SQLite Formatter

SQLite SQL turns up embedded in other things: a string constant in application code, a migration in a mobile app, the output of .schema from the command-line shell. It is usually written compactly because it was never meant to be read on its own, and it is usually the moment you need to read it that something has gone wrong.

With the SQLite dialect selected, the formatter handles the syntax the engine permits and others do not — columns declared with no type at all, PRAGMA statements sitting between ordinary DDL, ON CONFLICT upsert clauses with their excluded. pseudo-table, and AUTOINCREMENT on an integer primary key.

SQLite quirks the formatter has to accommodate

• Columns with no type. SQLite uses dynamic typing with type affinity: the declared type is a hint about preferred storage, not a constraint, and it may be omitted entirely. The tag column in the sample has no type, which is perfectly legal and which a stricter dialect parser would reject.
• Type affinity names. There are five affinities — TEXT, NUMERIC, INTEGER, REAL and BLOB — and any declared type maps onto one of them by name matching. A column declared VARCHAR(255) gets TEXT affinity and happily stores an integer. The formatter prints what you wrote; it does not normalise type names, because the exact spelling is what the affinity rules read.
• AUTOINCREMENT. Legal only on an INTEGER PRIMARY KEY, which is itself an alias for the internal rowid. It is also usually unnecessary: without it, SQLite already assigns increasing rowids, and adding it costs an extra sqlite_sequence table plus a guarantee against reuse you may not need.
• PRAGMA. Configuration statements such as PRAGMA foreign_keys = ON are formatted as standalone statements between DDL, which is exactly how they appear in real migration scripts.
• ON CONFLICT upserts. The conflict target, the DO UPDATE SET assignments, the excluded. references to the row that failed to insert, and the optional trailing WHERE are each broken out onto their own lines.
• STRICT tables. The modern opt-in to real type enforcement is preserved on the tail of a CREATE TABLE. Note that it is incompatible with the typeless column above — a STRICT table requires every column to declare one of the five permitted types — which is a rule about SQLite, not about formatting.

ALTER TABLE is narrow, so schemas get rewritten

SQLite supports only a small slice of ALTER TABLE: rename a table, add a column, rename a column, and drop a column. There is no changing a column type, no adding a constraint to an existing table, no reordering.

The standard workaround — create a new table with the shape you want, copy the rows across, drop the original, rename the replacement — is why SQLite migrations tend to contain whole CREATE TABLE statements rather than terse one-line alters. Those long DDL statements are the main thing people bring to this formatter, and a clause-per-column layout makes it much easier to diff the new table against the old.