SQLite ER Diagram Guide

SQLite powers mobile apps, embedded devices and small web products, and its DDL has habits the other databases do not. For diagramming, three facts cover almost everything.

1. The rowid alias

INTEGER PRIMARY KEY is not just a primary key — it is an alias for SQLite's internal rowid, which makes it auto-incrementing in behavior. A diagram should mark it PK and auto-increment, even though the words AUTOINCREMENT appear in only a minority of schemas (that keyword forces a stricter, slower generation and is rarely needed).

The exception is a table declared WITHOUT ROWID. Such a table has no hidden rowid at all: its PRIMARY KEY becomes the real storage key, rows live in key order, and nothing auto-increments. They suit junction tables keyed by (order_id, product_id) and other lookup data, saving one index and one indirection. In the diagram they look like a composite primary key with no auto badge; if you assume every INTEGER PRIMARY KEY increases, WITHOUT ROWID is the signal that it does not.

2. Foreign keys live inline

SQLite does not support ADD CONSTRAINT. Every foreign key is written inside the CREATE TABLE as a column-level REFERENCES clause — which is convenient for parsers: one statement holds the whole truth about a table.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL
);

Two more facts about SQLite foreign keys matter. First, enforcement is off by default: PRAGMA foreign_keys = ON has to run on every connection, or references are recorded but never checked. The DDL is identical either way, so the diagram shows the relationship whether or not the app enforces it. Second, SQLite does not validate references at CREATE TABLE time — a table can reference one that does not exist yet. Import order becomes irrelevant, and a typo in a REFERENCES clause goes unnoticed until a constraint fails at runtime.

When you do not have the schema as text, ask the database for it. In the sqlite3 shell, .schema prints every CREATE TABLE. Programmatically, PRAGMA table_info(x) — or table_xinfo, which also lists hidden and generated columns — and PRAGMA foreign_key_list(x) return the same information in structured form. PRAGMA foreign_key_check scans the data for rows that violate the declared references; run it before trusting the diagram of an old file.

3. Types are suggestions

SQLite's type affinity means a column can be declared almost anything and still store anything. In practice, schemas declare TEXT, INTEGER, REAL, BLOB and NUMERIC — diagrams map uuid-like TEXT columns and JSON stored as TEXT with the type shown as-is, which is the honest representation.

The affinity rules are short enough to memorize: any type containing INT becomes INTEGER; TEXT, CHAR or CLOB becomes TEXT; BLOB or an empty type means no affinity; REAL, FLOA or DOUB becomes REAL; everything else, including NUMERIC and DECIMAL, becomes NUMERIC. So VARCHAR(255) is TEXT, a BOOLEAN column is NUMERIC and stores 0 and 1, DATETIME is usually TEXT in ISO format. A diagram that preserves the declared type instead of the affinity is more useful — VARCHAR(255) tells a reader something that 'text' does not.

Migrations and schema changes

ALTER TABLE is the most limited of the family, and it shapes how SQLite schemas evolve. Adding a column works. Renaming a column arrived in 3.25 and dropping one only in 3.35, so older files cannot do either. There is no ADD CONSTRAINT and no ALTER COLUMN: any structural change means creating a new table, copying the data with INSERT INTO ... SELECT, dropping the old table and renaming. The SQLite documentation describes the full procedure as a twelve-step recipe — foreign keys, indexes and triggers all have to be dropped and recreated in the right order.

Tooling fills the gap. Flyway, Liquibase, Alembic, knex, drizzle-kit and Prisma Migrate all support SQLite and generate the table-rebuild boilerplate for you. Their migration folders are also a good source for diagrams: apply the migrations in order, then dump the resulting schema — the final CREATE TABLE statements describe reality better than any single migration.

PRAGMA foreign_keys=OFF;
BEGIN;
CREATE TABLE posts_new (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE
);
INSERT INTO posts_new (id, user_id, title) SELECT id, user_id, title FROM posts;
DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;
COMMIT;
PRAGMA foreign_keys=ON;

When SQLite schemas diverge from server databases

SQLite files often start as the schema of a small app and end up ported to PostgreSQL or MySQL as the product grows. Expect these divergences. There are no schemas or tablespaces — one namespace, no search_path. There are no ENUM types: a TEXT column plus a CHECK constraint plays that role. No arrays, no separate permission model, and JSON stored as plain TEXT with whatever validation the author added in triggers. AUTOINCREMENT in SQLite is a stronger guarantee than the rowid alias — it prevents rowid reuse after deletions — but it translates to a plain SERIAL or AUTO_INCREMENT on export, and it rarely matters.

Composite foreign keys deserve a second look. SQLite supports them fully and mobile codebases use them — a settings table keyed by (user_id, device_id) is a common shape — but ORM mappings for composite keys are fiddly. When porting, teams often replace them with a synthetic id plus a unique index, a change that alters the diagram's key badges before it alters any data.

Paste your schema into ERDGenerator with the SQLite dialect: INTEGER PRIMARY KEY becomes an auto-increment PK, inline REFERENCES become edges, and exporting emits the schema back as runnable SQLite DDL — or as PostgreSQL if you are planning a migration.

Common mistakes

• Assuming foreign keys are enforced because they are declared. Without PRAGMA foreign_keys=ON on the connection, SQLite stores the reference and never checks it.
• Marking every INTEGER PRIMARY KEY as auto-increment, then assuming WITHOUT ROWID tables follow the same rule. They do not — the composite key is the storage key.
• Expecting ALTER TABLE ... ADD CONSTRAINT to work. It does not exist; a constraint can only be added by rebuilding the table.
• Reading VARCHAR(255) and concluding SQLite has MySQL's type system. It has affinity — BOOLEAN and DATETIME are conventions, not types.
• Splitting a .sql file on semicolons. CREATE TRIGGER bodies contain semicolons and must be read as whole statements.
• Diagramming from application code. Migrations drift, and the .sqlite file is the only source that cannot lie.

Best practices

• Export the schema with .schema (or a dump minus the INSERT lines) rather than reconstructing it from code — the database is the source of truth.
• Name your constraints with CONSTRAINT fk_posts_user FOREIGN KEY ... instead of bare column-level REFERENCES; named constraints make diffs and re-imports clearer.
• Use WITHOUT ROWID deliberately for junction and lookup tables with composite keys, not everywhere: it helps small-row lookup tables and hurts large-row ones.
• Turn PRAGMA foreign_keys=ON at connection setup in every client, and keep PRAGMA foreign_key_check in your test suite so violations surface in CI, not in production data.
• If the file will eventually migrate to a server database, keep declared types honest — VARCHAR(255) exports better than a bare TEXT.

Frequently asked questions

Why does the diagram show a foreign key my app never enforced? Because the edge comes from the DDL, and SQLite records REFERENCES clauses regardless of the foreign_keys pragma. The diagram describes the declared model; whether running code enforced it is a data question, and PRAGMA foreign_key_check answers it.

Where is the AUTOINCREMENT keyword? Usually absent. INTEGER PRIMARY KEY is already a rowid alias and increases on its own; the explicit keyword only adds a guarantee against rowid reuse after deletions, at a cost. The diagram marks the column auto-increment either way.

Can I move the schema to PostgreSQL? Mostly directly. Inline REFERENCES clauses and CHECK constraints carry over, INTEGER PRIMARY KEY maps to an identity column, and TEXT stays TEXT. What does not carry over: rowid assumptions, WITHOUT ROWID storage decisions and JSON validation implemented in triggers.

How do generated columns appear? SQLite has supported VIRTUAL and STORED generated columns since 3.31, with the same meaning as in MySQL. They render as regular columns flagged as generated — nothing can be inserted into them.