How to verify row counts after migration

Count each table before the migration and after it, then compare the numbers with one query: SELECT count(*) FROM orders;. Over a seeded table of 50000 orders both sides returned 50000 and the delta was 0, while 2941 of those rows had silently lost their status value.

Why check this

Row counts are the first gate after a migration runs on staging, before anyone opens the application. They catch the loud failures: a WHERE clause in the copy step that filtered rows out, a join that multiplied them, a batch loop that stopped at the last successful chunk and exited 0.

The failure it prevents is the one nobody notices for a week. A migration that moves 50000 orders into a new table and lands 49000 of them leaves the application working, the reports slightly wrong, and no error anywhere.

The same run shows the limit of the check. A count compares cardinality, nothing else. Steps 4 and 5 below take the same 50000 rows and find the damage the count could not see.

Prerequisites

• The sqlite3 3.50.6 shell, which on this machine is the Android platform-tools build and the only SQLite client on PATH. It produced every output below, and each was reproduced through node:sqlite, SQLite 3.51.3. The counting queries are plain SQL; reading the table list out of sqlite_master is SQLite specific, and the SQLite documentation gives the equivalent for other engines.
• A copy of the database from before the migration. A count you did not take beforehand cannot be reconstructed afterwards.
• The fixture used below, 50000 rows with a skewed status distribution:

CREATE TABLE orders (
  id             INTEGER PRIMARY KEY,
  customer_email TEXT    NOT NULL,
  status         TEXT    NOT NULL,
  amount_cents   INTEGER NOT NULL,
  created_at     TEXT    NOT NULL
);
INSERT INTO orders (id, customer_email, status, amount_cents, created_at)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT i,
       'user' || (i % 900) || '@example.com',
       CASE WHEN i % 17 = 0 THEN 'refunded'
            WHEN i % 7  = 0 THEN 'cancelled'
            WHEN i % 3  = 0 THEN 'shipped'
            WHEN i % 2  = 0 THEN 'pending'
            ELSE 'paid' END,
       (i * 37) % 90000 + 500,
       date('2026-01-01', '+' || (i % 240) || ' days')
FROM n;

• The migration under test copies orders into orders_v2, renaming the status values through a CASE that has no branch for refunded. That omission is the defect the steps hunt.

Steps

1. 1

   Step 1.

   Count the table you are about to migrate, on the copy taken before the run.

   sqlite3 shop-before.db "SELECT count(*) FROM orders;"
   

   50000

   Write the number down. This is the only figure in the procedure that cannot be recovered later.

2. 2

   Step 2.

   Count every table in one command, rather than the one table you remembered. The first sqlite3 call writes a SELECT per table, the pipe runs them.

   sqlite3 shop.db "SELECT 'SELECT ''' || name || ''' AS tbl, count(*) AS rows FROM \"' || name || '\";' FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;" | sqlite3 -noheader shop.db
   

   orders|50000
   orders_v2|50000

   The same command against shop-before.db prints one line, orders|50000. Run it on both files and diff the two outputs, so a table the migration forgot to fill shows up as a line, not as an absence.

3. 3

   Step 3.

   Turn the comparison into a single verdict. ATTACH opens the pre-migration copy beside the live database, so one query reads both.

   sqlite3 -header -column shop.db "ATTACH 'shop-before.db' AS src; SELECT (SELECT count(*) FROM src.orders) AS src_rows, (SELECT count(*) FROM main.orders_v2) AS dst_rows, (SELECT count(*) FROM src.orders) - (SELECT count(*) FROM main.orders_v2) AS delta;"
   

   src_rows  dst_rows  delta
   --------  --------  -----
   50000     50000     0

   A delta of 0. Every row arrived. Stopping here is the mistake this page exists to prevent.

4. 4

   Step 4.

   Count the column as well as the row. count(*) counts rows; count(column) counts rows where that column is not NULL, and the gap between them is free.

   sqlite3 -header -column shop.db "SELECT count(*) AS all_rows, count(state) AS with_state FROM orders_v2;"
   

   all_rows  with_state
   --------  ----------
   50000     47059

   2941 rows carry no state at all. The delta in step 3 was still 0, because those rows are present. They are empty in the column the migration was written to fill.

5. 5

   Step 5.

   Count by the value the migration rewrote, on both sides, in one query. FULL OUTER JOIN keeps a value that exists on one side only, which is the whole point.

   sqlite3 -header -column shop.db "ATTACH 'shop-before.db' AS src; SELECT s.status AS src_status, s.n AS src_rows, d.state AS dst_state, d.n AS dst_rows FROM (SELECT status, count(*) n FROM src.orders GROUP BY status) s FULL OUTER JOIN (SELECT state, count(*) n FROM main.orders_v2 GROUP BY state) d ON d.state = upper(s.status) ORDER BY s.status;"
   

   src_status  src_rows  dst_state  dst_rows
   ----------  --------  ---------  --------
                                  2941
   cancelled   6722      CANCELLED  6722
   paid        13445     PAID       13445
   pending     13446     PENDING    13446
   refunded    2941
   shipped     13446     SHIPPED    13446

   Two rows tell the story. refunded has 2941 source rows and no target row. An unnamed target group has 2941 rows and no source. The CASE in the migration has no branch for refunded, so those rows landed as NULL.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | delta is 0 | Cardinality matches | Keep going. Run steps 4 and 5 before you sign anything off. | | delta is positive | Rows were dropped | Find the filter. A WHERE or an inner join in the copy step is the usual cause. | | delta is negative | Rows were multiplied | A join on a non-unique key. Confirm with How to check duplicate rows in sql. | | count(*) above count(col) | Rows arrived, a column did not | Read the group counts in step 5 to name the values that went missing. | | A group present on one side only | A value mapping is incomplete | Fix the mapping, rerun the migration, repeat step 5 until both columns are filled. | | Counts move between two runs | The source is still taking writes | Take the counts inside one transaction, or freeze writes first. |

Common mistakes

What to check next

• How to check data integrity after migration: the checksum that catches what a matching count misses, and how to read a mismatch.
• How to test database migration: running the up and down legs, where the count comparison belongs.
• How to check duplicate rows in sql: the negative delta case, when a join multiplied rows instead of copying them.
• How to verify a database backup: count and checksum again after a restore, because a restore command exiting 0 proves nothing.

FAQ

How do I check a row count in SQL?

SELECT count(*) FROM table_name; on any engine. Add WHERE to count a subset. Avoid count(1) versus count(*) arguments; they are identical in SQLite, and count(column) is the one that behaves differently because it skips NULL.

How do I count every table at once?

Generate one SELECT per table from the catalogue and run the result, as in step 2. SQLite reads sqlite_master. On other engines the catalogue view is information_schema.tables, and the generation step is the same.

Are the row counts in the system catalogue good enough?

No. Catalogue row counts are planner estimates refreshed by ANALYZE and they drift. Use them to plan a query, never to verify a migration.

The counts match but the application is wrong. What now?

Count grouped by the columns the migration touched, as in step 5, then compare a checksum of the ordered rows. A matching count with wrong content is the normal shape of a mapping defect.

Does this work on PostgreSQL or MySQL?

The counting queries do. The commands here ran against SQLite 3.50.6, which is the engine available on the machine that verified them, so the shell syntax and the catalogue table are SQLite forms.

Verified