Create-table form emits PostgreSQL DDL for every engine (dbType prop is unused)

[cevheri]

What is wrong

CreateTableModal is the only DDL form in the product, and it emits one dialect for every engine.

• The component declares a dbType prop (src/components/CreateTableModal.tsx), Studio.tsx passes the active connection's type into it, and the component never reads it. The prop is dead.
• The default first column is id SERIAL PRIMARY KEY, and the type list offers JSONB. Both are PostgreSQL spellings.
• The same text is generated for every engine that publishes supportsCreateTable: postgres, mysql, mssql, oracle, sqlite, duckdb, libsql, trino. On SQL Server, Oracle and Trino a SERIAL column or a JSONB type does not parse, so pressing Create runs a statement that can only fail.

The modal previews the SQL before the button, so this is visible rather than silent, but the form cannot produce a working auto-increment primary key outside PostgreSQL and MySQL.

Repro

1. Connect to SQL Server (or Oracle, or Trino).
2. Open the create-table form, keep the default id column, name the table, press Create.
3. The generated CREATE TABLE t (id SERIAL PRIMARY KEY) fails in the engine.

What it should do

Read dbType and emit that engine's spelling for the two things the form actually offers:

• auto-increment primary key: GENERATED BY DEFAULT AS IDENTITY (postgres, oracle), IDENTITY(1,1) (mssql), AUTO_INCREMENT (mysql), INTEGER PRIMARY KEY AUTOINCREMENT (sqlite, libsql), a sequence default (duckdb), and no auto-increment offer at all for trino.
• the type list: drop JSONB where it does not exist and offer the engine's own JSON/text types.

Where an engine cannot express what the checkbox means, the form should not offer the checkbox, the same way supportsCreateTable itself is a published fact rather than a guess. Follow the refusal pattern already used in src/lib/schema-diff/migration-generator.ts, which branches per dialect and returns a reason instead of an invalid statement.

Notes

Reported by a reader on opennet.ru (https://opennet.ru/66216-libredb), who asked how the form reconciles SQL Server's one-checkbox identity with PostgreSQL's sequences. It does not.

Tests are required: tests/components/CreateTableModal.test.tsx exists, and the repo gate is 100% line coverage. One case per engine id asserting the generated SQL is the shape this issue lists.

Help is welcome on this one, and questions in the issue are fine before you start.

[7487]

Claiming this one per CONTRIBUTING's "claim before you start".

Confirmed the dead prop: CreateTableModalProps declares dbType?: string (line 26) but the component destructures only { isOpen, onClose, onTableCreated } (line 42), so the value Studio.tsx passes is discarded. The eight ids that reach the form are the 17 in DatabaseType minus the nine that publish supportsCreateTable: false, which matches the list in the issue.

Plan: give generateSQL a per-dialect descriptor for the two things the form offers — the auto-increment primary key and the type list — and drop the auto-increment option entirely for trino, following the refusal pattern in migration-generator.ts. Tests per engine id in tests/components/CreateTableModal.test.tsx.

One question before I finalize the duckdb branch: a sequence default needs a CREATE SEQUENCE alongside the CREATE TABLE, and onTableCreated hands a single string to executeQuery. Would you rather the form emit both statements in one submission, or decline the auto-increment checkbox on duckdb the way it will for trino? I will default to emitting both statements if the splitter handles it, and say so in the PR.

[cevheri]

Claiming this one per CONTRIBUTING's "claim before you start".

Confirmed the dead prop: CreateTableModalProps declares dbType?: string (line 26) but the component destructures only { isOpen, onClose, onTableCreated } (line 42), so the value Studio.tsx passes is discarded. The eight ids that reach the form are the 17 in DatabaseType minus the nine that publish supportsCreateTable: false, which matches the list in the issue.

Plan: give generateSQL a per-dialect descriptor for the two things the form offers — the auto-increment primary key and the type list — and drop the auto-increment option entirely for trino, following the refusal pattern in migration-generator.ts. Tests per engine id in tests/components/CreateTableModal.test.tsx.

One question before I finalize the duckdb branch: a sequence default needs a CREATE SEQUENCE alongside the CREATE TABLE, and onTableCreated hands a single string to executeQuery. Would you rather the form emit both statements in one submission, or decline the auto-increment checkbox on duckdb the way it will for trino? I will default to emitting both statements if the splitter handles it, and say so in the PR.

Assigned. Measuring the thing before starting on it, and coming back with a specific question instead of a guess, is the right way to pick up an issue this size. Your reading of the dead prop and the eight-id set both check out.

On the duckdb question: emit both statements. onTableCreated does hand a single string to executeQuery, but use-query-execution.ts reads that string with isMultiStatement under the connection's own grammar and routes a multi-statement buffer to /api/db/multi-query, which splits and runs them in order. duckdb is in the grammar table, so the split is correct there. One condition: docs/providers/duckdb.md records no sequence or identity fact, so measure the engine and add what you measured to that doc in the same PR rather than inheriting PostgreSQL's spelling.

One correction to the issue text, which is my error and not yours: there is no auto-increment checkbox. The three checkboxes are Primary, Nullable and Unique. Auto-increment is an entry in the type dropdown, and the default column's type is a hardcoded "SERIAL" in two places, including the reset in handleCreate. So the two things the form offers collapse into one surface. Declining auto-increment for trino means leaving that entry out of trino's type list and giving trino a different default column type, not hiding a checkbox. All three sites need to come from the dialect descriptor.

[cevheri]

Assigned. Measuring the thing before starting on it, and coming back with a specific question instead of a guess, is the right way to pick up an issue this size. Your reading of the dead prop and the eight-id set both check out.

On the duckdb question: emit both statements. onTableCreated does hand a single string to executeQuery, but use-query-execution.ts reads that string with isMultiStatement under the connection's own grammar and routes a multi-statement buffer to /api/db/multi-query, which splits and runs them in order. duckdb is in the grammar table, so the split is correct there. One condition: docs/providers/duckdb.md records no sequence or identity fact, so measure the engine and add what you measured to that doc in the same PR rather than inheriting PostgreSQL's spelling.

One correction to the issue text, which is my error and not yours: there is no auto-increment checkbox. The three checkboxes are Primary, Nullable and Unique. Auto-increment is an entry in the type dropdown, and the default column's type is a hardcoded "SERIAL" in two places, including the reset in handleCreate. So the two things the form offers collapse into one surface. Declining auto-increment for trino means leaving that entry out of trino's type list and giving trino a different default column type, not hiding a checkbox. All three sites need to come from the dialect descriptor.