How to check table schema

Run .schema orders in the sqlite3 shell for the declared SQL, then PRAGMA table_info(orders); for the same columns as rows a script can read. The first prints the CREATE TABLE statement with its indexes. The second prints cid, name, type, notnull, dflt_value and pk, one row per column.

Why check this

Read the schema when a migration reaches staging and before you sign the release off. The failure it catches is a column the application believes is NOT NULL while the deployed table allows nulls, so the null arrives in the month end report instead of at insert time.

A second reason applies to SQLite in particular. The declared type of a column is not a rule about what may be stored there, so reading the schema tells you the intent and reading the data tells you the fact. Step 5 shows those two answers disagreeing on a single table.

All output below comes from SQLite 3.50.6. The way to list tables and columns differs on every engine: PostgreSQL has \d in psql and an information_schema, MySQL has SHOW CREATE TABLE. Neither of those is installed here, so this page does not describe their output.

Prerequisites

• The sqlite3 shell. See the SQLite CLI documentation and the pragma reference.
• A fixture to reproduce every block on this page. Save it as shop.sql and build the database with sqlite3 shop.db < shop.sql.

CREATE TABLE customers (
  id      INTEGER PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE,
  created TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL,
  currency    VARCHAR(3) NOT NULL DEFAULT 'EUR',
  status      TEXT NOT NULL DEFAULT 'new',
  paid_at     DATETIME,
  note        STRING
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status, paid_at);

INSERT INTO customers(id, email, created) VALUES (1, 'a@example.com', '2026-09-12 07:00:00');
INSERT INTO orders(id, customer_id, total_cents, note) VALUES (1, 1, 1999, 'paid');
INSERT INTO orders(id, customer_id, total_cents, note) VALUES (2, 1, '2500', '007');
INSERT INTO orders(id, customer_id, total_cents, note) VALUES (3, 1, 'free', 'x');

• The datatype reference for the affinity rules behind step 5.
• The output below came from the sqlite3 3.50.6 shell that ships in the Android SDK platform-tools, which is the build on this machine. Every result in the steps was reproduced through Node 22's node:sqlite, linked against SQLite 3.51.3. The two builds agree on all five, and disagree on one setting that the third pitfall records.

Steps

1. 1

   Step 1.

   Print the declared SQL for one table.

   sqlite3 shop.db ".schema orders"
   

   CREATE TABLE orders (
   id          INTEGER PRIMARY KEY,
   customer_id INTEGER NOT NULL REFERENCES customers(id),
   total_cents INTEGER NOT NULL,
   currency    VARCHAR(3) NOT NULL DEFAULT 'EUR',
   status      TEXT NOT NULL DEFAULT 'new',
   paid_at     DATETIME,
   note        STRING
   );
   CREATE INDEX idx_orders_customer ON orders(customer_id);
   CREATE INDEX idx_orders_status ON orders(status, paid_at);

   This is the statement stored in sqlite_master, reprinted verbatim, including the two indexes that name the table.

2. 2

   Step 2.

   Read the same columns as rows, which is the form a script can compare.

   sqlite3 -header -column shop.db "PRAGMA table_info(orders);"
   

   cid  name         type        notnull  dflt_value  pk
   ---  -----------  ----------  -------  ----------  --
   0    id           INTEGER     0                    1
   1    customer_id  INTEGER     1                    0
   2    total_cents  INTEGER     1                    0
   3    currency     VARCHAR(3)  1        'EUR'       0
   4    status       TEXT        1        'new'       0
   5    paid_at      DATETIME    0                    0
   6    note         STRING      0                    0

   notnull is 1 where the column was declared NOT NULL. dflt_value holds the default as written, quotes included. There is no column here for indexes and none for foreign keys.

3. 3

   Step 3.

   List the foreign keys, which step 2 did not show.

   sqlite3 -header -column shop.db "PRAGMA foreign_key_list(orders);"
   

   id  seq  table      from         to  on_update  on_delete  match
   --  ---  ---------  -----------  --  ---------  ---------  -----
   0   0    customers  customer_id  id  NO ACTION  NO ACTION  NONE

   NO ACTION on both sides means the reference declares nothing about cascades. Deleting customer 1 will not delete its orders.

4. 4

   Step 4.

   List the indexes on customers, where the declaration created one without naming it.

   sqlite3 -header -column shop.db "PRAGMA index_list(customers);"
   

   seq  name                          unique  origin  partial
   ---  ----------------------------  ------  ------  -------
   0    sqlite_autoindex_customers_1  1       u       0

   origin is u, meaning the index exists because a UNIQUE constraint asked for it, not because anyone wrote CREATE INDEX. .schema customers shows the word UNIQUE and never shows this index, because sqlite_master stores no SQL for it.

5. 5

   Step 5.

   Compare the declared type with the type actually stored.

   sqlite3 -header -column shop.db "SELECT id, total_cents, typeof(total_cents) AS t_total, note, typeof(note) AS t_note FROM orders;"
   

   id  total_cents  t_total  note  t_note
   --  -----------  -------  ----  -------
   1   1999         integer  paid  text
   2   2500         integer  7     integer
   3   free         text     x     text

   Row 2 was inserted with the string '2500' and came back as an integer, because INTEGER affinity converted it. Row 3 was inserted with 'free' into that same INTEGER NOT NULL column and stayed text, because affinity converts when it can and stores what it was given when it cannot. The note column was declared STRING, a type name SQLite does not recognise, so it took NUMERIC affinity and turned the order reference '007' into the integer 7.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | notnull 0 on a column the code treats as required | The database accepts a null there | Add the constraint in a migration, after checking existing rows for nulls | | dflt_value empty on a column with a default in the ORM | The default lives in application code only | Any insert from the shell, a script or a fixture loader will skip it | | origin u in index_list | The index came from a UNIQUE constraint | Nothing. Dropping it means dropping the constraint | | typeof() disagrees with the declared type | Affinity changed the value, or refused to | Validate in the application. The column type will not do it | | PRAGMA table_info returns nothing | The table name is wrong or in another schema | Run .tables and check the spelling and the attached database |

Common mistakes

What to check next

• How to check if a table has an index: step 4 lists the indexes, this reads what each one covers.
• How to check foreign key: the declaration in step 3 is not the same thing as enforcement.
• How to check database collation: the other column property that the declared type hides.
• How to compare database schemas: the same pragmas run against two databases and diffed.
• How to check database schema version: what the schema should look like, according to the migration history.

FAQ

How to check database schema, not one table?

Run sqlite3 shop.db .schema with no argument for every table and index in the file, or .tables for the names alone. Both read sqlite_master. For a machine-readable form, select from pragma_table_info joined against sqlite_master.

How to check table schema in SQL rather than a shell command?

SELECT sql FROM sqlite_master WHERE name='orders'; returns the same text that .schema prints, and SELECT * FROM pragma_table_info('orders'); returns step 2 as an ordinary result set. Both work from any client, not only the CLI.

Does the declared type stop the wrong value being stored?

Not in SQLite. Step 5 stores the text free in a column declared INTEGER NOT NULL. A CHECK constraint or STRICT on the table declaration enforces types. This answer is specific to SQLite and was not verified on another engine here.

Which of these reads is missing from the others?

.schema omits the indexes SQLite created for UNIQUE constraints. PRAGMA table_info omits indexes and foreign keys entirely. Reading only one of the three leaves part of the table undescribed, which is why the steps run all three.

Why does dflt_value show quotes around the value?

The pragma returns the default expression as it was parsed, not the evaluated result. 'EUR' is the SQL literal, and datetime('now') appears as a function call. Comparing a pragma default with a plain string will mismatch on the quotes.

Verified