A forum comment found a bug that 100% coverage did not
A reader asked how one create-table form reconciles SQL Server's identity column with PostgreSQL's sequences. It did not, and on SQLite it failed silently rather than loudly. What the fix took, and why a fully covered test suite had nothing to say about any of it.
A Russian technology site ran a piece about LibreDB Studio, and a reader in the comments did not believe the engine count. One UI for sixteen engines, they asked, so is there really a written interface for each kind of database, for editing tables and columns?
The answer is that there is one UI and no per-engine interface: an abstract provider class with thirteen required methods, one file per type id, and a UI that branches on what a provider publishes about itself rather than on the engine’s name.
That is why MongoDB’s tree says Collection and document, Redis says Key Pattern and key, and the Cassandra editor is labelled CQL with no JOIN, no subquery and no OFFSET.
The create-table form appears where a provider declares supportsCreateTable, and where a provider does not declare it the button is simply absent rather than present and broken.
Then they pushed on exactly the right spot.
An autoincrementing primary key in MS SQL is literally one checkbox, while in PostgreSQL they built a pile of silly sequences for the same thing. So the interface does not differ radically, but it differs very concretely. How did you solve that?
We had not solved it.
The form declared a dbType prop, the workspace passed the active connection’s type into it, and the component never destructured it.
The prop was dead, so one form emitted one dialect for all eight engines, and that dialect was PostgreSQL’s: the default column was id SERIAL PRIMARY KEY and the type list offered JSONB.
The part that made it a bug rather than an inconvenience
On SQL Server, Oracle and Trino, SERIAL does not parse.
The form previews its SQL above the button, so a user saw a statement fail and knew why.
That is bad, and it is honest.
SQLite is neither.
SQLite accepts any word as a type name, so CREATE TABLE t (id SERIAL PRIMARY KEY) succeeds, SERIAL lands on NUMERIC affinity, and only a column typed exactly INTEGER PRIMARY KEY is the rowid alias that auto-numbers.
SQLite’s legacy rule then lets a non-INTEGER primary key hold NULLs.
Measured on 3.53: two inserts that omit the key both store id = NULL, with no error anywhere.
So the engine where the form looked like it worked was the engine where it quietly handed people a broken primary key. libSQL, which embeds SQLite, behaves identically. A reader’s question about a checkbox turned out to be a silent data-integrity defect on two of the eight engines.
One control, eight spellings
The fix keeps the control singular. The type list still carries one entry, Auto-Increment, and a per-engine descriptor holds what that entry compiles to. What the engines disagree about is not only the spelling but the shape of the clause, which is why the whole column clause is stored per dialect rather than assembled from parts.
| Engine | What the form emits |
|---|---|
| PostgreSQL | id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY |
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
| Oracle | id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY |
| SQLite, libSQL | id INTEGER PRIMARY KEY AUTOINCREMENT |
| DuckDB | CREATE SEQUENCE IF NOT EXISTS t_id_seq; then id INTEGER PRIMARY KEY DEFAULT nextval('t_id_seq') |
| Trino | no auto-increment offered at all |
Four of those rows carry a fact that only shows up when you run it.
MySQL’s AUTO_INCREMENT precedes PRIMARY KEY; SQLite’s AUTOINCREMENT has to follow it, and id INTEGER AUTOINCREMENT PRIMARY KEY is a syntax error while id BIGINT PRIMARY KEY AUTOINCREMENT is refused because AUTOINCREMENT is only allowed on an INTEGER primary key.
PostgreSQL moved too, and in the direction the reader was needling us about: PostgreSQL’s own documentation calls SERIAL historical and points at identity columns, so the form writes an identity column now.
DuckDB has no keyword of any kind, and GENERATED ... AS IDENTITY there parses and then fails at execution with “Constraint not implemented”, so this is a missing feature rather than a missing word; a sequence is the only route, which makes DuckDB the one engine where the form emits two statements, and they need IF NOT EXISTS because a DuckDB sequence outlives the table that defaults from it.
Trino declares no primary key for any table in any catalog, and its column grammar is a name, a type and an optional NOT NULL, so PRIMARY KEY and UNIQUE are parse errors there and the form hides those two checkboxes instead of offering controls the engine cannot compile.
That last row is the rule the whole change follows.
Where an engine cannot express what a control means, the control is not offered.
It is the same rule as supportsCreateTable itself, which is a published fact rather than a guess.
What our tests had to say about all of this
Nothing. The component had thirty-four passing tests and 100% line coverage, which is a hard gate in that repository, and the defect was live in every release.
The reason is worth more than the bug. Coverage measures which lines executed, not which shapes the application actually produces. Every one of those tests mounted the form with the engine already known, and the application never does that: the workspace renders the form unconditionally, so its first render always arrives before there is an active connection.
That gap survived the first fix and was caught in review.
The corrected form derived its initial column once, at mount, from a prop that is undefined on the first render, so the per-engine default never reached a first open at all.
Two consequences, both measured in a running browser rather than argued: Trino’s type dropdown rendered empty, and after a create on Trino the SQLite form emitted id INTEGER NOT NULL, no key and no auto-increment.
That table got created, and the next insert without a value failed.
The class of defect the change set out to close had arrived through a different door.
The second round fixed it by deriving the columns from the connected engine on every change, and added five tests that all fail against the previous commit. Three of them mount with no engine and hand one over afterwards, because that is the only shape the application produces.
What is still not fixed
Verifying this against live engines found two more defects, and both are ours rather than the contributor’s.
Trino executes no unqualified statement at all, SELECT included, because a Trino connection pins a catalog and never a schema, so the coordinator refuses anything that does not name catalog, schema and table in full.
The create-table form’s output cannot run there whatever it spells.
That is issue #653.
An empty database offers no Create Table button anywhere, because the explorer’s empty-schema panel renders a message and no toolbar. The one case where a form is most useful is the one case it cannot be reached from. That is issue #654, and it is a good first issue.
The reader’s second criticism stands as well.
There is no ALTER COLUMN form for any engine, and no create-column form either; the DDL form in the product is the one this post is about.
Schema changes come out as SQL from a diff of two schemas, with per-dialect branching and a refusal that names its reason where an engine cannot express the change, and a human applies them.
Their example, dropping a foreign key, renaming a column and changing its type, is a chain that one ALTER cannot do and that we do not sequence for you.
Inline row editing covers seven engines and finds the key column by name, id or *_id, rather than from primary-key metadata, so a composite key or a differently named one is not editable in the grid.
The reason all of this is thin, without dressing it up: the project’s goal was to make database access governable and accountable on your own server, so the work went into the editor, access control and the audit trail, and the DDL forms got the least attention of anything in the product.
The chain
A piece gets published. A reader who has not run the product reads one sentence of it, does not believe the claim, and asks a question specific enough to be answerable. The question becomes an issue with a repro and an acceptance list. Somebody who had never touched the project claims it, measures the dead prop and derives the affected engine set from the capability flags rather than trusting the list in the issue, asks the one question that had a real fork in it, and closes it in two rounds of review, correcting the maintainer’s wording along the way.
The comment was worth more than the test suite, and the test suite is not bad. That is not a slogan about open development; it is an argument for publishing the limits, because a reader can only aim at a claim they can see. Every engine on this site carries a line saying what it does not do, and the security page publishes its own known gaps. This is what that is for.
The change is PR #651. The issue is #648. Both were measured on PostgreSQL 18, MySQL 26.7, SQL Server 2022, Oracle 23, SQLite 3.53, libSQL, DuckDB 1.5.5 and Trino 476.