SQLite Truncate Table: DELETE FROM, Reset IDs and VACUUM

There is no TRUNCATE in SQLite. Both TRUNCATE TABLE audit_log; and TRUNCATE audit_log; stop at the parser, so nothing is deleted:

Parse error near line 1: near "TRUNCATE": syntax error
  TRUNCATE TABLE audit_log;
  ^--- error here

The examples below use the 1bench demo database: users, audit_log (with a user_id foreign key to users), migrations and feature_rollout, all with id INTEGER PRIMARY KEY AUTOINCREMENT. Every statement was run on SQLite 3.43.2 and 3.54.1 with the same results; only the CLI's error prefix differs, as noted where it shows up.

Delete all rows with DELETE FROM

A DELETE with no WHERE clause empties the table and keeps its columns, indexes and triggers.

DELETE FROM audit_log;

SQLite runs this as a truncate behind the scenes. Instead of visiting each row, it clears the table's pages in one step, which EXPLAIN DELETE FROM audit_log; shows as a single Clear opcode per table and index. The row count is still reported correctly: the CLI's .changes on prints changes: 200 for the demo table.

The fast path is skipped, and rows are deleted one at a time, when any of these is true:

The result is the same either way. Only the speed changes, which you notice on tables with millions of rows.

Delete all rows and reset the id

DELETE does not reset an AUTOINCREMENT counter. SQLite keeps the highest id it has handed out in the sqlite_sequence table, so after emptying audit_log the next insert still gets id 201.

Example: empty the table, then remove its counter so numbering starts again at 1.

DELETE FROM audit_log;
DELETE FROM sqlite_sequence WHERE name = 'audit_log';

INSERT INTO audit_log (action, entity_type) VALUES ('login', 'user');
SELECT id FROM audit_log;  -- 1

To start from a number of your choice, update the row instead. The next insert gets seq + 1.

UPDATE sqlite_sequence SET seq = 1000 WHERE name = 'audit_log';

DELETE FROM sqlite_sequence; resets the counter of every table at once. The table itself only exists once some table has used AUTOINCREMENT, and querying it before that fails with "no such table: sqlite_sequence".

Tables without AUTOINCREMENT

A plain INTEGER PRIMARY KEY has no counter to reset. SQLite picks the largest id in the table plus one, so an empty table starts again at 1 by itself, and the id of a deleted last row is handed out again.

Example: the id 3 comes back after its row is deleted, and the table restarts at 1 once it is empty.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('a'), ('b'), ('c');

DELETE FROM notes WHERE id = 3;
INSERT INTO notes (body) VALUES ('d');   -- gets id 3 again

DELETE FROM notes;
INSERT INTO notes (body) VALUES ('e');   -- gets id 1

That reuse is the reason AUTOINCREMENT exists. Use it when an old id must never point at a new row, for example when ids appear in URLs or logs.

Foreign keys and PRAGMA foreign_keys

Foreign keys are off by default in the sqlite3 CLI and in most drivers (PRAGMA foreign_keys; returns 0), and the setting lasts only for the current connection. With them off, emptying a parent table leaves orphaned child rows behind and no error is raised.

Switch them on first, and the child table's ON DELETE rule decides what happens:

PRAGMA foreign_keys = ON;
DELETE FROM users;

In the demo database audit_log.user_id is ON DELETE SET NULL, so the delete succeeds and all 200 log rows lose their user. A child table with no ON DELETE action blocks it instead, and no rows are removed:

Error near line 2: FOREIGN KEY constraint failed

SQLite 3.43 prints the same message as "Runtime error near line 2: FOREIGN KEY constraint failed (19)". PRAGMA foreign_key_check; lists any orphaned rows left over from a delete you ran with foreign keys off. Note that PRAGMA foreign_keys does nothing inside an open transaction, so set it before BEGIN.

Shrink the file with VACUUM

Deleting rows does not make the database file smaller. The freed pages go on a free list for SQLite to reuse. In testing, a demo file grown to 10.6 MB stayed at 10.6 MB after DELETE FROM audit_log, with 2,573 of its 2,586 pages free.

PRAGMA freelist_count;   -- pages a VACUUM would give back
VACUUM;

VACUUM rebuilds the file, here from 10.6 MB down to 53 KB. It needs exclusive access and up to twice the file's size in free disk space while it runs. VACUUM INTO writes the compacted copy to a new file and leaves the original alone, which also works as a quick backup.

VACUUM INTO 'demo-compact.sqlite';

To have SQLite shrink the file after every delete, turn on auto-vacuum. On an existing database the setting only takes effect after one full VACUUM.

PRAGMA auto_vacuum = FULL;
VACUUM;

Drop and recreate the table

Dropping the table removes its rows, its indexes, its triggers and its sqlite_sequence row in one statement, so the counter starts at 1 when you recreate it. Copy the CREATE statements first with .schema feature_rollout in the CLI, because they are gone afterwards.

DROP TABLE feature_rollout;

CREATE TABLE feature_rollout (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  feature_key TEXT NOT NULL,
  enabled INTEGER DEFAULT 0,
  rollout_percentage REAL DEFAULT 0 CHECK (rollout_percentage BETWEEN 0 AND 100),
  target_countries TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  CHECK (enabled IN (0, 1))
);

The feature_rollout_set_updated_at trigger is dropped with the table and has to be created again too. DELETE FROM is the safer choice unless you also want to change the table's definition.

Empty every table

SQLite has no single command to truncate all tables. Generate one DELETE per table from sqlite_schema, skipping SQLite's own tables:

SELECT 'DELETE FROM "' || name || '";'
FROM sqlite_schema
WHERE type = 'table' AND name NOT LIKE 'sqlite_%';

Example: save the output to a file, then run it in one transaction with foreign keys off, so the order of the tables does not matter, and finish with a reset of every counter and a VACUUM.

PRAGMA foreign_keys = OFF;
BEGIN;
DELETE FROM "users";
DELETE FROM "app_settings";
DELETE FROM "migrations";
DELETE FROM "audit_log";
DELETE FROM "feature_rollout";
DELETE FROM sqlite_sequence;
COMMIT;
PRAGMA foreign_keys = ON;
VACUUM;

If you would rather start from an empty file, copy just the schema with sqlite3 demo.sqlite ".schema --nosys" | sqlite3 empty.sqlite. Without --nosys the copy stops on "object name reserved for internal use: sqlite_sequence".

From Python

Python's built-in sqlite3 module runs the same SQL. Pass the table name as a parameter in the sqlite_sequence query, and keep VACUUM outside the with block, since it cannot run inside a transaction.

import sqlite3

con = sqlite3.connect("demo.sqlite")
con.execute("PRAGMA foreign_keys = ON")

with con:
    con.execute("DELETE FROM audit_log")
    con.execute("DELETE FROM sqlite_sequence WHERE name = ?", ("audit_log",))

con.execute("VACUUM")
con.close()

TRUNCATE in SQLite PRAGMAs

TRUNCATE does appear in SQLite, but only for journal files. PRAGMA wal_checkpoint(TRUNCATE) copies the write-ahead log into the database and cuts the -wal file to zero bytes (2.7 MB down to 0 in testing). PRAGMA journal_mode = TRUNCATE makes SQLite empty the rollback journal after each transaction instead of deleting it.

PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA journal_mode = TRUNCATE;

Neither one removes any rows from your tables.