[EPIC] DDL & DML Operations

[hilmanfjrsy]

Dynamic SQL API for DDL & DML Operations

Develop an API that allows clients to perform dynamic DDL (Data Definition Language) and DML (Data Manipulation Language) operations using JSON-based payloads. This API will support creating, altering, and dropping tables (DDL) as well as inserting, selecting, updating, and deleting records (DML). Additionally, implement a metadata management system to store table and column metadata separately.

1. Metadata Tables

• metadata_tables (Stores table-related information)
  • id UUID PRIMARY KEY
  • table_name VARCHAR(255) UNIQUE NOT NULL
  • created_at TIMESTAMP
  • updated_at TIMESTAMP
• metadata_columns (Stores column-related information.)
  • id UUID PRIMARY KEY,
  • table_id UUID REFERENCES metadata_tables(id) ON DELETE CASCADE,
  • column_name VARCHAR NOT NULL,
  • data_type VARCHAR NOT NULL,
  • is_primary BOOLEAN DEFAULT FALSE,
  • is_nullable BOOLEAN DEFAULT TRUE,
  • is_unique BOOLEAN DEFAULT FALSE,
  • created_at TIMESTAMP
  • updated_at TIMESTAMP

1. DDL Operations

• Design API endpoint /migrate
• Validate input (check table and column names).
• Insert/update/delete table metadata into metadata_tables.
• Insert column metadata into metadata_columns.
• Generate CREATE, ALTER, DROP TABLE SQL and execute.
• Example payload operations:
  • The operations can send multiple payloads and will execute sequentially.

{
  "operations": [
    {
      "operation": "Create",
      "resource": "Table",
      "migration": {
        "name": "test",
        "primaryKey": "UUID"
      }
    },
    {
      "operation": "Create",
      "resource": "Column",
      "migration": {
        "name": "name",
        "table": "test",
        "column": {
          "type": "text",
          "definition": {
            "textType": "text",
            "default": null,
            "unique": false,
            "nullable": true
          }
        }
      }
    },
    {
      "operation": "Create",
      "resource": "Column",
      "migration": {
        "name": "description",
        "table": "test",
        "column": {
          "type": "text",
          "definition": {
            "textType": "text",
            "default": null,
            "unique": false,
            "nullable": true
          }
        }
      }
    },
    {
      "operation": "Create",
      "resource": "Column",
      "migration": {
        "name": "created_at",
        "table": "test",
        "column": {
          "type": "datetime",
          "definition": {
            "default": "now()",
            "nullable": true
          }
        }
      }
    },
    {
      "operation": "Create",
      "resource": "Column",
      "migration": {
        "name": "updated_at",
        "table": "test",
        "column": {
          "type": "datetime",
          "definition": {
            "default": "now()",
            "nullable": true
          }
        }
      }
    },
    {
      "operation": "Alter",
      "resource": "Column",
      "migration": {
        "from": "active",
        "to": "active",
        "table": "users",
        "description": "",
        "column": {
          "definition": {
            "unique": false,
            "default": true,
            "nullable": false
          }
        }
      }
    },
    {
      "operation": "Drop",
      "resource": "Column",
      "migration": {
        "table": "users",
        "column": "active"
      }
    }
  ]
}

1. Schema table

Provide an endpoint to display the schema, including available tables and columns, from the metadata table and column.
[GET] /schemas

1. DML Operation

• Design API endpoint /execute
• Validate the input.
• Select/insert/update/delete operations

Example payload:

Basic:

{
  "operations": [
    {
      "operation": "Select",
      "instruction": {
        "name": "data",
        "orderBy": {
          "created_at": "ASC"
        },
        "condition": {},
        "limit": 26,
        "offset": 0,
        "params": {},
        "table": "users"
      }
    },
    {
      "operation": "Select",
      "instruction": {
        "name": "data",
        "orderBy": {
          "created_at": "ASC"
        },
        "condition": {
          "$or": [
            {
              "$or": [
                {
                  "email": {
                    "$eq": "{{name}}"
                  },
                  "external_id": {
                    "$eq": "user1"
                  }
                }
              ]
            }
          ]
        },
        "limit": 26,
        "offset": 0,
        "params": {
          "name": "admin@admin.com"
        },
        "table": "users"
      }
    },
    {
      "operation": "Insert",
      "instruction": {
        "table": "users",
        "name": "data",
        "data": {
          "external_id": "user1"
        }
      }
    },
    {
      "operation": "Update",
      "instruction": {
        "table": "users",
        "name": "data",
        "condition": {
          "$and": [
            {
              "id": {
                "$eq": "3e054215-991b-4f37-b98e-2e891a09eec4"
              }
            }
          ]
        },
        "set": {
          "external_id": "admin1"
        }
      }
    },
    {
      "operation": "Delete",
      "instruction": {
        "view": "ok",
        "name": "data",
        "condition": {
          "$and": [
            {
              "id": {
                "$eq": "3c81493b-86b5-41cf-afc8-ce6cabe2594b"
              }
            }
          ]
        },
        "params": {}
      }
    }
  ]
}