Queries That Pass Their Tests on SQLite and Break on PostgreSQL in Production

This case study uses a demo project in BareSQL’s code repository: a made-up booking app with a schema of two tables and eleven queries, written the way an app tested on SQLite tends to write them. Its test suite stays fast the usual way. Each test builds the schema in an in-memory SQLite database, runs the app’s queries and throws the database away.

Production runs on PostgreSQL. Every query passes every test, and the question is how many of them do the same thing in production. The outputs below are what BareSQL printed.

Since the app only ever meets two engines, lint gets told so:

baresql lint --engines sqlite,postgres schema.sql queries.sql

What Lint Finds Without Running Anything

queries.sql:4:1: error: INSERT OR IGNORE works on SQLite only; PostgreSQL, MySQL and MariaDB refuse it. [insert-ignore]
    INSERT OR IGNORE
    Insert from a SELECT that checks first: INSERT INTO t (id) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1). When two sessions insert the same row at once, the unique key still stops the second one, with an error instead of a skip.
queries.sql:11:31: error: PostgreSQL has no = between a boolean and a number. [boolean-as-number]
    is_admin = 1
    Compare and insert TRUE and FALSE (active = TRUE, or simply WHERE active). All four engines accept them.
queries.sql:20:23: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
    b.starts_at
    Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
queries.sql:24:12: error: DATE(x, modifier) works on SQLite only; PostgreSQL, MySQL and MariaDB refuse it. [function-not-everywhere]
    date(starts_at, '+' || nights || ' days')
    No form works on all four engines; d + INTERVAL '1' DAY on PostgreSQL, MySQL and MariaDB.
queries.sql:27:8: error: STRFTIME() works on SQLite only; PostgreSQL, MySQL and MariaDB refuse it. [function-not-everywhere]
    strftime('%Y-%m', starts_at)
    No form works on all four engines; TO_CHAR() on PostgreSQL and DATE_FORMAT() on MySQL and MariaDB.
queries.sql:34:62: error: PostgreSQL refuses the text '240.00' for the INTEGER column price_cents; MySQL and MariaDB round it to a whole number, and SQLite keeps a fraction as it is. [type-not-enforced]
    '240.00'
    Run the tests on the production engine as well, or check values in the application before they reach the database.

6 problems: 6 errors. 3 notes not shown (differences that depend on the data); --all shows them.

Each of the 6 problems is an error, a statement PostgreSQL refuses outright: INSERT OR IGNORE for sign-ups, is_admin = 1 for the admin check, a GROUP BY that leaves a column out, SQLite’s own date() and strftime(), and a price that arrives as the text '240.00' for an integer column, which SQLite quietly turns into the number 240. On PostgreSQL each of them fails the first time it runs, so they’re quick to find, though for this app the first time would be in production.

What Only Shows Up With Data

Lint also says that 3 notes are hidden. Notes are differences that depend on the data, so lint can’t know whether they matter here. baresql compare can, because it runs the queries:

baresql compare --sqlite --postgres postgres://dev@localhost/scratch schema.sql queries.sql

Of the differences it reports, 2 raise no error on either engine:

queries.sql:8  SELECT id, email FROM users WHERE email LIKE 'bob%'
  1 row on SQLite, 0 on PostgreSQL
  SQLite: 2, '[email protected]'
  PostgreSQL: no rows
  lint: like-case

queries.sql:17  SELECT b.id, u.name FROM bookings b JOIN users u ON u.id = b.user_id ORDER BY u.name
  the same rows in a different order (NULL sorts differently, or rows that tie in ORDER BY came in another order)
  SQLite: 4 rows
      4, NULL
      1, 'Ann Lee'
      2, 'Ann Lee'
      3, 'Bob Stone'
  PostgreSQL: 4 rows
      1, 'Ann Lee'
      2, 'Ann Lee'
      3, 'Bob Stone'
      4, NULL
  lint: null-order

The admin search box finds Bob on SQLite, where LIKE ignores letter case for A to Z, and finds nobody on PostgreSQL, where it doesn’t. The front desk’s guest list puts the guest without a name first on SQLite and last on PostgreSQL. Neither would fail a test that checks the query runs, and both change what a person sees. The fixes are short: LOWER(email) LIKE 'bob%', and ORDER BY u.name IS NULL, u.name wherever the position of NULL matters.

In all, compare ran 15 statements on both engines: 7 answered alike and 8 differ. The 6 refusals among them are the errors lint found, with the same codes.

Running the Tests on Both

The fix that lasts is to run the tests on PostgreSQL as well, at least in CI. Until that’s in place, BareSQL gives the list before the change: every query that would break, with the reason and what to write instead. Lint can also sit in CI on its own, since it needs no database:

- name: Check the SQL
  run: baresql lint --engines sqlite,postgres db/schema.sql db/queries/*.sql

The rule posts explain each difference with the answers every engine gave, including four of the ones found here: LIKE and letter case, where NULL sorts, booleans and numbers and values that don’t fit the column.