How to safely back up or copy a live SQLite database

Posted by Kyle Hankinson August 18, 2026

---

The obvious way to back up an SQLite database is to copy the file. cp app.db backup.db, duplicate it in Finder, done. Most of the time the copy even opens fine, which is exactly what makes the habit dangerous: the two ways it fails are both silent.

The first failure is the torn copy. A database being written while you copy it can land in your backup half-updated, a state that never existed and that SQLite's crash recovery cannot repair, because from the copy's point of view there was no crash. The official How To Corrupt An SQLite Database File page lists backing up or restoring while a transaction is active among the reliable ways to manufacture corruption.

The second failure is quieter and easier to demonstrate, so let's do that.

Watching cp lose 500 rows

Here is a small experiment run on SQLite 3.50.6. Build a database in WAL mode (the write-ahead-logging journal mode available since SQLite 3.7.0, and a common default in frameworks and apps because of its concurrency benefits), give it 1,000 rows, and arrange for the most recent 500 to still be in the write-ahead log rather than the main file. On disk it looks like this:

$ ls -la app.db*
-rw-------  45056  app.db
-rw-------  45352  app.db-wal

That -wal file is not scratch space. It contains committed transactions that have not yet been checkpointed into the main database file. Now take the "backup" the way most people do:

$ cp app.db backup_cp.db
$ sqlite3 backup_cp.db "SELECT count(*) FROM notes;"
500

The source database reports 1,000 rows. The copy reports 500. No error, no warning: PRAGMA integrity_check on the copy even says ok, because structurally it is a perfectly valid database. It is simply the database as it stood at the last checkpoint, missing every commit that lived in the WAL. A backup made this way can quietly trail the real data by hours.

The two right ways

SQLite ships two tools that produce a consistent copy of a live database, and both captured all 1,000 rows in the same experiment.

The first is the .backup command in the sqlite3 shell:

$ sqlite3 app.db ".backup backup_api.db"
$ sqlite3 backup_api.db "SELECT count(*) FROM notes;"
1000

.backup drives the Online Backup API, which copies the database page by page through SQLite itself, WAL content included. It is safe to run against a database that other connections are using, and if another connection writes to the source mid-copy, the backup restarts so the result is always a consistent snapshot. The same API is callable from application code, which is how you schedule backups from inside an app.

The second is VACUUM INTO, available since SQLite 3.27.0 (2019):

VACUUM INTO 'backup_vacuum.db';

This writes a transactionally consistent snapshot of the database into a brand new file, and rebuilds it while doing so: tables and indexes are written out packed, and free pages are dropped rather than copied. The VACUUM documentation notes the output is a snapshot taken as of the statement's own transaction, so a live source is fine here too.

The rebuild is not a small detail. Take a table of 50,000 log rows, delete 45,000 of them (SQLite keeps the freed pages in the file for reuse rather than shrinking it), and back the database up both ways:

Copy method	Rows	File size
Source file	5,000	3,866,624
.backup	5,000	3,866,624
VACUUM INTO	5,000	397,312

.backup is byte-faithful and reproduces the dead space; VACUUM INTO produced a copy roughly a tenth the size holding exactly the same rows. So the choice comes down to intent: .backup for routine, incremental-friendly backups where you want the file as it is, VACUUM INTO when you are archiving a snapshot, shipping a database to someone, or want the compaction anyway.

Whichever you use, verify the result. Open the copy and run:

PRAGMA integrity_check;

Both copies above return ok, and a quick SELECT count(*) against your important tables confirms the copy is current, which is the check that catches the WAL trap since, as we saw, a stale copy passes integrity_check happily.

If you must copy files

Sometimes a file-level copy is the only tool available, for example pulling a database out of an iOS simulator or an app's container. The rules that make it safe:

• Only copy while no process has the database open. A closed database has no transaction in flight and (after a clean close of the last connection) no leftover WAL content.
• If companion files exist, copy the whole family together: app.db, app.db-wal, and app.db-shm. Copying the main file and the WAL together preserved all 1,000 rows in the experiment above; copying the main file alone lost 500.
• Never delete a -wal or -journal file to tidy up. The WAL holds committed data; the journal holds what is needed to roll back an interrupted write. Deleting either can corrupt the database or throw away commits.

The same torn-copy logic applies to anything that reads files behind your back. A live database sitting in a Dropbox or iCloud Drive folder gets synced file by file at arbitrary moments, and Time Machine walking the disk mid-write has the same problem. Keep live databases out of synced folders, or write snapshots into them with VACUUM INTO instead of syncing the working file.

This is also the clean workflow when you want to poke around inside an app's database: snapshot it with .backup or VACUUM INTO and open the copy in SQLPro for SQLite to browse, query, and export, with no risk of the app and your inspection stepping on each other. And if WAL mode is new to you, it is worth knowing for more than backup semantics: it is also one of the larger wins in how to improve SQLite insert performance.

One last habit worth stealing from the ops world: a backup you have never restored is a hope, not a backup. Once in a while, open a real backup file, run integrity_check, and count rows in the tables you care about. On SQLite that whole drill takes under a minute, which removes any excuse to skip it.

---

Tags: SQLite

SQL NULL traps: = NULL, NOT IN, and sort order

Posted by Kyle Hankinson August 7, 2026

---

A query that returns zero rows with zero errors is harder to debug than one that fails loudly, and NULL is behind more of those silent empties than everything else combined. The root cause is always the same: SQL comparisons involving NULL do not evaluate to true or false but to a third value, UNKNOWN, and a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped without comment.

That one rule produces three distinct traps. All of the MySQL, PostgreSQL, and SQLite behavior below was run against MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6; the SQL Server behavior is cited from Microsoft's documentation, linked where used.

Two tables are enough to demonstrate everything:

CREATE TABLE customers (id int, name varchar(20));
INSERT INTO customers VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

CREATE TABLE orders (customer_id int);
INSERT INTO orders VALUES (1), (NULL);   -- one order has no customer

Trap 1: WHERE col = NULL matches nothing

The natural way to find the orphaned order reads fine and returns nothing:

SELECT count(*) FROM orders WHERE customer_id = NULL;   -- 0
SELECT count(*) FROM orders WHERE customer_id IS NULL;  -- 1

Identical results on MySQL, PostgreSQL, and SQLite: the = version finds zero rows even though a NULL row is sitting right there. NULL means "unknown", and asking whether an unknown value equals another unknown value can only be answered "unknown", so the comparison never becomes TRUE for any row. SQL Server follows the same logic under its default settings; Microsoft's NULL and UNKNOWN page states that comparisons between two null values, or between a null value and any other value, return unknown, and directs you to IS NULL / IS NOT NULL.

When you genuinely want NULL-tolerant equality, where NULL equals NULL and nothing else, every engine has an operator for it, they just disagree on the spelling:

Engine	NULL-safe equality	Status
MySQL 8.4	a <=> b	verified: NULL <=> NULL returns 1, 1 <=> NULL returns 0
PostgreSQL 16	a IS NOT DISTINCT FROM b	verified: returns true for two NULLs, false for 1 vs NULL
SQLite	a IS b	verified; 3.50.6 also accepts IS NOT DISTINCT FROM
SQL Server 2022+	a IS NOT DISTINCT FROM b	per the IS NOT DISTINCT FROM docs; not available before SQL Server 2022

MySQL's comparison operators documentation notes that <=> is equivalent to the standard IS NOT DISTINCT FROM, so all four spellings mean the same thing.

Trap 2: one NULL turns NOT IN into an empty result

Now the trap with real teeth. Find the customers who have no orders:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

You would expect bob and carol. On MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6 alike, this returns zero rows. Not a wrong list, an empty one, and the same three-valued logic explains it in SQL Server as well.

The subquery produces the list (1, NULL), and id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second comparison is UNKNOWN for every row in the table, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. One stray NULL in the subquery quietly vetoes the entire result. This is the nastiest variety of NULL bug because the query works perfectly in development and then returns nothing in production the day the first NULL shows up in that column.

Two fixes, both verified to return bob and carol on all three engines. The direct one is to keep NULLs out of the list:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL);

The better one is to stop using NOT IN for this job entirely:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

NOT EXISTS asks "is there a matching row?" instead of comparing values, so NULLs in the subquery cannot poison it. It states the anti-join intent directly (the same family of shapes covered in SQL joins explained), and it is safe to use as a habit even on columns that are NOT NULL today, because columns have a way of becoming nullable later.

Trap 3: every engine sorts NULLs somewhere different

The first two traps behave identically everywhere. The third is where the engines part ways. Sort three values, one of them NULL:

CREATE TABLE s (v int);
INSERT INTO s VALUES (2), (NULL), (1);
SELECT v FROM s ORDER BY v ASC;

Engine	ASC order	DESC order	NULLS FIRST/LAST syntax
MySQL 8.4	NULL, 1, 2	2, 1, NULL	not supported
PostgreSQL 16	1, 2, NULL	NULL, 2, 1	supported
SQLite 3.50.6	NULL, 1, 2	2, 1, NULL	supported since 3.30 (2019)
SQL Server	NULL first (docs)	NULL last (docs)	not supported

MySQL and SQLite treat NULL as smaller than every value, so it leads an ascending sort. PostgreSQL treats NULL as larger, so it trails. SQL Server sides with MySQL: the ORDER BY clause documentation states that NULL values are treated as the lowest possible values. The practical consequence: port a "latest items first, blanks at the bottom" query from MySQL to Postgres and the blanks migrate from bottom to top with no error and no warning.

Where the standard syntax exists, pinning the position is trivial. Verified on PostgreSQL 16 and SQLite 3.50.6:

SELECT v FROM s ORDER BY v ASC NULLS LAST;   -- 1, 2, NULL

On MySQL 8.4 that syntax is a hard error (ERROR 1064), but a boolean sort key does the same job, verified to return 1, 2, NULL:

SELECT v FROM s ORDER BY (v IS NULL), v;

v IS NULL is 0 for values and 1 for NULLs, so NULLs sink to the end. SQL Server lacks the syntax too; the equivalent trick there is ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, using the conditional ordering pattern shown in the same ORDER BY documentation.

Chasing this class of bug across engines is considerably less painful when you can run the identical script against MySQL, PostgreSQL, SQL Server, and SQLite connections in one place and compare the grids, which is precisely the sort of side-by-side work SQLPro Studio exists for.

The habits that make all three traps a non-issue: write IS NULL rather than = NULL always, reach for NOT EXISTS rather than NOT IN under a subquery, and never let a query's correctness depend on where the engine happens to put NULLs in a sort. Declare it with NULLS LAST or a boolean sort key, and the query means the same thing everywhere.

---

Tags: MySQL PostgreSQL Microsoft SQL Server SQLite

Fixing "database is locked" in SQLite

Posted by Kyle Hankinson July 21, 2026

---

Your app writes to SQLite and gets this back:

sqlite3.OperationalError: database is locked

From the CLI, the same failure looks like:

Error: stepping, database is locked (5)

The 5 is the part worth reading. It is SQLITE_BUSY, and it means one specific thing: a different connection holds a lock that your statement needs. SQLite locks at the level of the whole database file, not rows or tables, so one writer anywhere blocks every other writer everywhere. Nothing is corrupt and nothing has been lost. Another connection simply has the file at the moment yours wants it.

Its lesser-known sibling SQLITE_LOCKED (code 6) is a conflict inside a single connection, such as dropping a table that one of your own open statements is still reading. The two are separate problems with separate fixes; the result codes reference has the full catalogue. If you see code 5, look outward at other connections.

Reproduce it on purpose

The error is much less mysterious once you can trigger it at will. Open two terminals on the same file. In the first, start a write transaction and leave it hanging:

-- terminal A
sqlite> CREATE TABLE t(x INTEGER);
sqlite> BEGIN IMMEDIATE;
sqlite> INSERT INTO t VALUES (1);
-- no COMMIT yet

In the second, try to write:

-- terminal B
sqlite> INSERT INTO t VALUES (2);
Error: stepping, database is locked (5)

Terminal B fails instantly, and the instantly is itself a clue. Out of the box, SQLite does not wait for a contended lock at all:

sqlite> PRAGMA busy_timeout;
0

Verified on SQLite 3.50.6: the default busy timeout is zero milliseconds. Any contention, however brief, surfaces as an immediate error.

Find out who holds the lock

Before reaching for fixes, identify the writer. The usual suspects, roughly in order of likelihood:

• An open transaction in your own code. A BEGIN whose COMMIT lives on a code path that an exception skipped is the classic version.
• A second connection inside the same app. Connection pools, background workers, and ORMs that quietly open extra handles all count as "another connection" even though it is all your process.
• Another program with the file open: a sqlite3 session in a forgotten terminal tab, a backup or sync agent, or a database browser. GUI clients are connections too. If you keep the file open in SQLPro for SQLite while your app writes to it, the app and the browser are two clients of one database, exactly like terminals A and B above.
• A crashed process that died mid-write, leaving a hot -journal file behind.

On macOS and Linux, lsof names every process holding the file. Run against the two-terminal repro:

$ lsof app.db
COMMAND   PID          USER   FD   TYPE DEVICE SIZE/OFF      NODE NAME
sqlite3 68988 kylehankinson    3u   REG   1,14    12288 360183043 app.db

PID 68988 is terminal A. In a real incident this is the fastest way to discover the forgotten CLI session or the second app instance you did not know was running.

Set a busy timeout

Since the default is to fail immediately, the single highest-value change is to tell SQLite to wait:

PRAGMA busy_timeout = 5000;

With that set, a blocked statement retries until the lock frees or five seconds elapse. Rerunning the blocked INSERT from terminal B while terminal A still held its transaction, the statement returned after 5.3 seconds of waiting instead of failing at once. If the other writer finishes inside the window, your statement proceeds and no error is ever seen; per the busy_timeout documentation, the handler sleeps repeatedly until the accumulated wait reaches the limit.

Two details matter. The setting is per connection, so it belongs in the code that opens connections, not in a one-off console session. And it is a treatment rather than a cure: it absorbs short contention, but a transaction held open for minutes will still time out everyone else.

Keep write transactions short, and start them honestly

Since one writer blocks all others, the length of your write transactions sets the amount of contention everyone else experiences. Batch your work, commit promptly, and never hold a transaction across user think-time or network calls.

When a transaction is going to write, declare that up front with BEGIN IMMEDIATE rather than a plain BEGIN. It claims the write lock at BEGIN, so contention surfaces at the very first statement, where retrying is trivial, instead of halfway through a batch of work. Short transactions are also one of the bigger wins covered in how to improve SQLite insert performance.

Enable WAL mode

In the default rollback-journal mode, readers and writers get in each other's way in a manner that surprises most people: a reader merely holding a read transaction open will block a writer's COMMIT. Reproduced on 3.50.6:

-- terminal A (journal_mode = delete, the default)
sqlite> BEGIN;
sqlite> SELECT count(*) FROM t;
-- read transaction stays open

-- terminal B
sqlite> BEGIN IMMEDIATE; INSERT INTO t VALUES (9); COMMIT;
Error: stepping, database is locked (5)

Switch the database to write-ahead logging and the same sequence just works:

sqlite> PRAGMA journal_mode = WAL;
wal

With WAL enabled, the writer in terminal B committed while terminal A's read transaction stayed open, and a reader querying during an uncommitted write got a clean answer from the last committed state. Readers no longer block the writer and the writer no longer blocks readers, which eliminates the largest class of SQLITE_BUSY errors in mixed read/write workloads.

WAL mode has been available since SQLite 3.7.0 (2010) and is a persistent property of the database file: set it once and every future connection gets it. Two things to know before flipping the switch. The database gains -wal and -shm companion files, which is normal. And WAL still allows only one writer at a time, so two concurrent writers can still collide; keep the busy timeout even after enabling it.

What not to do

Do not delete the -journal or -wal files to "unlock" a database. Those files are not clutter. The journal holds the information needed to roll back an interrupted transaction, and the WAL file holds committed transactions that have not yet been merged into the main file. Deleting either one can corrupt the database or silently discard committed data. If a stale lock genuinely outlived its process, which is rare on a local disk, the fix is to make sure no process has the file open (check lsof again) and then open it normally; SQLite recovers hot journals by itself.

And resist wrapping every write in an unbounded retry loop. Retries paper over the symptom while the leaked connection or forgotten transaction underneath keeps growing. Five seconds of busy timeout plus a diagnosis of who actually held the lock beats an infinite loop every time.

---

Tags: SQLite

How to Improve SQLite INSERT Performance

Posted by Kyle Hankinson December 5, 2024

---

SQLite is fast for reads, but naive inserts can be surprisingly slow. A few configuration changes can improve insert performance by 50x or more.

The Problem

By default, each individual INSERT in SQLite is wrapped in its own transaction. Each transaction forces a disk sync — a slow operation. Inserting 10,000 rows with 10,000 separate INSERTs means 10,000 disk syncs.

1. Wrap Inserts in a Transaction

The single biggest improvement:

BEGIN TRANSACTION;
INSERT INTO logs (message, level) VALUES ('Starting', 'INFO');
INSERT INTO logs (message, level) VALUES ('Processing', 'DEBUG');
-- ... thousands more inserts
INSERT INTO logs (message, level) VALUES ('Done', 'INFO');
COMMIT;

This changes 10,000 disk syncs to just one. In benchmarks, this alone can improve insert speed from ~85 inserts/second to ~50,000 inserts/second.

2. Use WAL Mode

Write-Ahead Logging mode allows concurrent reads during writes and batches disk writes more efficiently:

PRAGMA journal_mode = WAL;

WAL mode is persistent — you only need to set it once per database file. Benefits:

• Readers do not block writers
• Writers do not block readers
• Better write performance due to sequential I/O

3. Reduce Synchronous Level

PRAGMA synchronous = NORMAL;

Value	Safety	Speed
FULL (default)	Maximum durability	Slowest
NORMAL	Safe with WAL mode	Fast
OFF	Risk of corruption on crash	Fastest

NORMAL is a good balance — with WAL mode, it is safe against application crashes (but not power loss during a write).

4. Use Prepared Statements

Parsing SQL is expensive. Prepare the statement once, then bind and execute repeatedly:

# Python example
cursor = conn.cursor()
stmt = "INSERT INTO logs (message, level) VALUES (?, ?)"

conn.execute("BEGIN")
for msg, level in data:
    cursor.execute(stmt, (msg, level))
conn.execute("COMMIT")

// Swift example
let stmt = try db.prepare("INSERT INTO logs (message, level) VALUES (?, ?)")
try db.transaction {
    for (msg, level) in data {
        try stmt.run(msg, level)
    }
}

5. Increase Cache Size

The default page cache is 2MB. For large imports, increase it:

PRAGMA cache_size = -20000;  -- 20MB (negative = kilobytes)

More cache means fewer disk reads during the import.

6. Use Memory-Mapped I/O

PRAGMA mmap_size = 268435456;  -- 256MB

Memory mapping lets the OS handle caching more efficiently for large databases.

7. Multi-Row INSERT

SQLite 3.7.11+ supports multi-row VALUES:

INSERT INTO logs (message, level) VALUES
    ('Starting', 'INFO'),
    ('Processing', 'DEBUG'),
    ('Done', 'INFO');

This reduces parsing overhead compared to individual INSERT statements.

Benchmark Summary

Inserting 100,000 rows (typical results):

Configuration	Time	Rows/sec
Default (no transaction)	~20 min	~85
With transaction	~1.5 sec	~65,000
Transaction + WAL	~0.8 sec	~125,000
Transaction + WAL + prepared	~0.5 sec	~200,000
All optimizations	~0.3 sec	~300,000+

Complete Setup

For maximum insert performance, run these PRAGMAs at connection time:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -20000;
PRAGMA mmap_size = 268435456;
PRAGMA temp_store = MEMORY;

Then wrap all inserts in explicit transactions using prepared statements.

Using SQLPro Studio

opens SQLite database files directly — just open any `.db`, `.sqlite`, or `.sqlite3` file. You can run PRAGMA commands and test insert performance in the query editor, and import data from CSV or JSON files using the built-in import feature.

---

Tags: SQLite

How to Copy or Clone a Table in SQL

Posted by Kyle Hankinson October 24, 2024

---

Cloning a table is useful for backups, testing, and schema experiments. Every database can do it, but what gets copied varies.

MySQL

Structure + Data

CREATE TABLE users_copy AS SELECT * FROM users;

Warning: This copies data and column definitions but NOT indexes, primary keys, auto-increment, or foreign keys.

Structure Only (No Data)

CREATE TABLE users_copy LIKE users;

LIKE copies the full structure including indexes and auto-increment. To then copy data:

INSERT INTO users_copy SELECT * FROM users;

Partial Copy

CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

PostgreSQL

Structure + Data

CREATE TABLE users_copy AS SELECT * FROM users;

Like MySQL, this does not copy indexes, constraints, or defaults.

Structure with Constraints

CREATE TABLE users_copy (LIKE users INCLUDING ALL);

INCLUDING ALL copies defaults, constraints, indexes, comments, and identity columns. You can be selective:

CREATE TABLE users_copy (LIKE users INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

Then copy data separately:

INSERT INTO users_copy SELECT * FROM users;

SQL Server

Structure + Data

SELECT * INTO users_copy FROM users;

SELECT INTO creates the new table automatically. It copies column definitions and data but not indexes, constraints, or triggers.

Structure Only

SELECT * INTO users_copy FROM users WHERE 1 = 0;

The WHERE 1 = 0 ensures no rows are copied.

With Specific Columns or Filters

SELECT id, name, email INTO active_users
FROM users WHERE status = 'active';

Oracle

-- Structure + Data
CREATE TABLE users_copy AS SELECT * FROM users;

-- Structure Only
CREATE TABLE users_copy AS SELECT * FROM users WHERE 1 = 0;

To get the full DDL including indexes and constraints:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'USERS') FROM DUAL;

Then edit the output to change the table name.

SQLite

-- Structure + Data
CREATE TABLE users_copy AS SELECT * FROM users;

-- Structure Only
CREATE TABLE users_copy AS SELECT * FROM users WHERE 0;

SQLite's CREATE TABLE AS does not copy primary keys or autoincrement.

What Gets Copied

Feature	CREATE AS SELECT	LIKE / Structure Copy
Column names & types	Yes	Yes
Data	Yes	No
Primary key	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Indexes	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Auto-increment	No	Yes (MySQL LIKE, PG INCLUDING ALL)
Foreign keys	No	No (must add manually)
Triggers	No	No
Constraints	No	Yes (PG INCLUDING ALL)

Using SQLPro Studio

In , you can duplicate a table directly from the sidebar. Right-click a table and select "Duplicate Table" to create a copy with the structure and optionally the data — no SQL required.

---

Tags: MySQL PostgreSQL Microsoft SQL Server SQLite

How to Check if a Table Exists in SQL

How to Reset Auto-Increment and Identity Columns in SQL

How to Limit Query Results and Paginate in SQL

How to Insert Multiple Rows in a Single SQL Statement

How to Add a Column to an Existing Table in SQL

Export from to CSV, JSON or XML