SQLite Formatter

Quoting and parameters in SQLite

SQLite is permissive about quoting because it tries to run SQL written for other engines. The SQLite dialect accepts the same set:

• Identifiers can be wrapped in double quotes, backticks or square brackets, so "order", [customer name] and a backtick-quoted group column all format as names.
• Strings use single quotes, and a quote inside is doubled: 'it''s'. Backslash is not an escape character.
• Comments are -- and /* */. A MySQL-style # comment is an error.
• Blob literals such as x'DEADBEEF' are one token.

Bound parameters come in five spellings, and all of them survive formatting: ?, numbered ?2, :since, @region and $channel. This is where the dialect choice shows. Formatted as MySQL, ?2 is split into ? 2; under PostgreSQL, @region turns into @ region. Both would break the binding in your driver, so keep SQLite selected for code from Python’s sqlite3 module, Android Room, better-sqlite3 or Go’s database/sql.

UPSERT, RETURNING and table options

INSERT … ON CONFLICT(sku) DO UPDATE SET … puts the conflict target on the line after VALUES, with the SET list and any trailing WHERE indented beneath it; excluded.qty is left as a plain column reference. RETURNING, available since SQLite 3.35, becomes a clause of its own. INSERT OR REPLACE and REPLACE INTO are recognised as insert forms.

In CREATE TABLE, each column goes on its own line with INTEGER PRIMARY KEY AUTOINCREMENT, CHECK (…), REFERENCES … ON DELETE CASCADE and default expressions kept together. The table options STRICT and WITHOUT ROWID stay after the closing parenthesis. Pasting the output of the shell’s .schema command is a quick way to get a readable schema.

PRAGMA, triggers and other statements

PRAGMA foreign_keys = ON; and PRAGMA table_info(orders); each stay on one line, as do VACUUM, ANALYZE, REINDEX and ATTACH DATABASE 'archive.db' AS archive. Set Blank lines between statements to 0 to keep a block of pragmas compact at the top of a script.

Triggers are only partly understood. In CREATE TRIGGER … AFTER UPDATE ON orders FOR EACH ROW BEGIN … END, the statements in the body are formatted, but the UPDATE event moves to the next line. Window frames have a similar wrinkle: in ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW the AND is treated like a boolean operator and starts a new line. Both results are still valid SQLite.

Things to watch

A column called key is upper-cased to KEY, which SQLite does not care about but a reviewer might. Use Keyword case: As written or quote the name if you want it left alone. Commands that start with a dot, such as .mode csv or .headers on, belong to the sqlite3 shell and must be removed first. The formatter works on text only: it never opens a database file, and the text you paste is processed in the browser without being uploaded. For converting a spreadsheet into INSERT statements, try CSV to SQL.

Examples

insert into inventory (sku, qty) values ('SKU-1001', 5) on conflict(sku) do update set qty = qty + excluded.qty, updated_at = datetime('now') where excluded.qty > 0;

INSERT INTO
  inventory (sku, qty)
VALUES
  ('SKU-1001', 5)
ON CONFLICT (sku) DO UPDATE
SET
  qty = qty + excluded.qty,
  updated_at = datetime('now')
WHERE
  excluded.qty > 0;

create table if not exists orders (id integer primary key autoincrement, customer_id integer not null references customers(id) on delete cascade, total real not null check (total >= 0), status text not null default 'pending', created_at text not null default (datetime('now'))) strict;

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  total REAL NOT NULL CHECK (total >= 0),
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) strict;

select strftime('%Y-%m', o.created_at) as month, count(*) as orders, round(sum(o.total), 2) as revenue, o.payload ->> '$.channel' as channel from orders o where o.created_at >= :since and o.customer_id = ?2 group by month, channel order by month;

select
  strftime('%Y-%m', o.created_at) as month,
  count(*) as orders,
  round(sum(o.total), 2) as revenue,
  o.payload ->> '$.channel' as channel
from
  orders o
where
  o.created_at >= :since
  and o.customer_id = ?2
group by
  month,
  channel
order by
  month;

pragma foreign_keys = on;
pragma journal_mode = wal;
insert into customers (email, name) values ('ana@example.com', 'Ana') returning id, created_at;

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = wal;
INSERT INTO
  customers (email, name)
VALUES
  ('ana@example.com', 'Ana')
RETURNING
  id,
  created_at;

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/mysql
• sql formatter/postgresql
• sql validator
• sql minifier
• CSV to SQL
• JSON to SQL
• sql style guide