Skip to content

How to recover deleted rows in SQLite

Restore the database from right before the delete into a new file, then copy the missing rows back into the live database.

Restore the moment before, then copy the rows back

Your app keeps running: copying rows back is an ordinary write.

// 1. Get your Litebackup server: litebackup.com/get-started
// 2. In your shell, with the token from Machines, Add machine in your console:
//      npm install @litebackup/sdk
//      export LITEBACKUP_TOKEN=YOUR_JOIN_TOKEN

import Database from "better-sqlite3";
import { backup, restore } from "@litebackup/sdk";

// 3. Back up, once: every change becomes a version you can restore
backup("/var/lib/app/main.sqlite", { prefix: "app/" });

// 4. After a mistake: restore the moment before it into a new file
await restore("app/main.sqlite", {
  to: "/tmp/before.sqlite",
  time: new Date("2026-11-16T09:29:00Z"),
});

// Copy the missing rows back into the live database
const db = new Database("/var/lib/app/main.sqlite");
db.exec(`
  ATTACH '/tmp/before.sqlite' AS before;
  INSERT INTO main.orders
  SELECT * FROM before.orders
  WHERE id NOT IN (SELECT id FROM main.orders);
  DETACH before;
`);

Find the moment before the delete

litebackup versions app/main.sqlite lists the versions your server keeps, with their times. Restore with a --time right before the delete, or a --version from that list.

Without continuous backup, use your newest copy from before the delete, from .backup, VACUUM INTO, or a snapshot. Writes between that copy and the delete are not in it.

Copy only the rows you lost

Narrow the WHERE to the rows that were deleted, for example:

WHERE id NOT IN (SELECT id FROM main.orders)
  AND customer_id = 42;

When other tables point at these rows, copy the rows they point at first, in the order your app inserts them.

Recover a dropped table

CREATE TABLE AS SELECT loses the table's constraints and indexes. Recreate the table from its original schema first, then copy the rows:

sudo -u app sh -c 'sqlite3 /tmp/before.sqlite ".schema orders" | sqlite3 /var/lib/app/main.sqlite'
sudo -u app sqlite3 /var/lib/app/main.sqlite \
  "ATTACH '/tmp/before.sqlite' AS before; INSERT INTO main.orders SELECT * FROM before.orders;"

Replace the whole database

If too much changed to copy rows by hand, replace the file with the restored one. Restore SQLite to a point in time has the steps.

Next time

  • Litebackup keeps every change as a version within seconds, so the moment before a mistake is always there. Get started.
  • How to back up SQLite compares the ways to back up.

Get started

Install your Litebackup server and back up a first database in minutes: get started, or see pricing.