Should I use SQLite for tests when production runs PostgreSQL?

No, run tests of code that sends SQL on PostgreSQL when production runs PostgreSQL. SQLite sorts NULL values first in ascending order where PostgreSQL sorts them last, so an ordered query can pass on SQLite and return another order in production.

Is using HSQLDB for tests a good idea?

HSQLDB suits tests only when production also runs HSQLDB. HSQLDB's PostgreSQL and MySQL syntax modes are off by default, and HSQLDB sorts nulls before other values by default, which PostgreSQL does not.

Is a test with an in-memory database an integration test?

A test that runs your SQL on an in-memory database is an integration test, because the code and a database engine run together. A test on H2 or SQLite cannot find a difference between that engine and the engine production runs.

Should I test with an in-memory database?

Answer

Yes, test code that runs SQL, on the database engine production runs: use an in-memory database only when production runs the same engine, such as SQLite in a mobile app, and otherwise start PostgreSQL or MySQL in a container.

Verdict on the code under testYes

Why

Blast radiususers
Change frequencyregularly
Detectabilityeventually
Reversibilitywith-effort
Test costmoderate

Yes, test code that runs SQL on the engine production runs; an in-memory database qualifies only when it is that engine. The typical case is a PostgreSQL or MySQL backend whose query tests run on SQLite, H2 or HSQLDB in memory. Blast radius is users and Change frequency is regularly, because customers see the rows queries return and most features change a query. Detectability is eventually, because SQL that the in-memory engine runs differently from production returns plausible rows without an error. Reversibility is with-effort, because code acts on those rows until a hotfix, and Test cost is moderate, about an hour per test against a local database, so rule R11 gives Test.

When the decision changes
When	Decision	Why
The test checks a rule above the repository, such as when a lead counts as stale	Test the rule with an in-memory fake repository and no database	Test cost falls to trivial, because the rule takes and returns plain values, and Detectability stays eventually, which keeps Test
The code calls only save and findById, entities map columns by convention, and CI starts the app on the production engine with schema validation	Do not write separate database tests; test the code that calls those methods	Detectability moves to immediately and Reversibility to trivial, because a missing column fails startup and a convention leaves no column name to swap
Several companies share one database, and every query must add the tenant ID	Test mandatory on the production engine, with rows of two tenants in each test	Blast radius rises to safety-or-legal and Detectability to never, because a missing tenant filter leaks records without an error
A query selects the subscriptions that a nightly job charges	Test mandatory on the production engine, including a NULL last charge date	Blast radius rises to money and Reversibility to costly, because a double charge needs refunds
Production runs BigQuery, which has no local edition	Test it differently: a scheduled job compares the query's results with a reference query and alerts on a difference	Test cost rises to heavy, because only a cloud dataset runs BigQuery SQL, while Detectability stays eventually and Reversibility with-effort

What breaks if you don't test

A green in-memory suite proves only that the SQL works on the in-memory engine. SQLite puts NULL values first in an ascending sort, and so does HSQLDB by default, while PostgreSQL sorts nulls last. A list ordered by a nullable date passes on SQLite and comes back in another order in production, with no error, and the rows at the end stop getting attention.

What you lose if you over-test

Running the suite on both engines means every query that uses a PostgreSQL feature, such as the JSONB operator @>, needs a skip or a second version for the in-memory engine. Moving tests of plain logic onto the real engine makes them wait for a database they never query: a PostgreSQL container takes about 3 seconds to start with the image cached, in my measurements.

How to test

Start the production engine and version in a container with Testcontainers, schema built by the real migrations.
Write one integration test per hand-written query, with rows it must return, rows it must leave out, and a NULL in each nullable column it sorts or compares.
Test rules above the repository against an in-memory fake repository, with no database.
In Django, point the test settings at PostgreSQL; the runner uses an in-memory database only for SQLite. For .NET, Microsoft's testing guide covers the in-memory provider's limits.
When the app ships SQLite, use in-memory SQLite, as Room's testing guide shows.

Procedure and references

When the answer changes

The code calls only framework methods, and CI starts the app on the production engine.
A query decides which tenant's rows come back or what a customer is charged.
The production database has no local edition, such as BigQuery.

Real incident + Code example

The call list that buried new leads

On a Django CRM I worked on, tests ran on in-memory SQLite and production ran PostgreSQL. The sales call list ordered open leads by last_called_at, which was NULL for leads nobody had called. The test asserted that a new lead came first, and on SQLite it did. In production new leads sorted after 400 open leads, below the part of the list reps reached. Three weeks later marketing asked why a campaign's sign-ups had no calls. We set the null order with Django's nulls_first and moved the tests to PostgreSQL:

from django.db.models import F
from django.test import TestCase
from django.utils import timezone

from leads.models import Lead


def call_list():
    return Lead.objects.filter(status="open").order_by(
        F("last_called_at").asc(nulls_first=True), "id"
    )


class CallListTests(TestCase):  # DATABASES in test settings: PostgreSQL 16
    def test_a_lead_nobody_called_comes_first(self):
        called = Lead.objects.create(status="open", last_called_at=timezone.now())
        new = Lead.objects.create(status="open", last_called_at=None)

        self.assertEqual(list(call_list()), [new, called])

Sources

FAQ

Should I use SQLite for tests when production runs PostgreSQL?: No, run tests of code that sends SQL on PostgreSQL when production runs PostgreSQL. SQLite sorts NULL values first in ascending order where PostgreSQL sorts them last, so an ordered query can pass on SQLite and return another order in production.
Is using HSQLDB for tests a good idea?: HSQLDB suits tests only when production also runs HSQLDB. HSQLDB's PostgreSQL and MySQL syntax modes are off by default, and HSQLDB sorts nulls before other values by default, which PostgreSQL does not.
Is a test with an in-memory database an integration test?: A test that runs your SQL on an in-memory database is an integration test, because the code and a database engine run together. A test on H2 or SQLite cannot find a difference between that engine and the engine production runs.