Web Developer Travis McCracken on Using SQLite for Local Testing

Problem: local tests drift from production reality

Travis McCracken’s post about backend development with Rust and Go points at a familiar problem in API teams: local testing is either too slow to run often or too fake to catch meaningful failures. Teams start with mocks because mocks are fast. Then the first production incident arrives from a database constraint, transaction boundary, timeout, lock, migration mismatch, or serialization bug that the mocks never modeled. Anyone who has operated distributed systems has seen this pattern. The test suite was green because the test suite was not testing the system’s actual contracts.

SQLite is attractive in that gap because it gives developers a real SQL engine with almost no setup. A file-backed database can be created, migrated, populated, tested, and deleted in seconds. For local API tests, that changes the feedback loop. Instead of waiting for a Docker network, a shared development database, or a cloud sandbox, a developer can run tests from a clean state on every commit. That matters because the cheapest failures are the ones found before services are composed into a larger system.

The catch is that SQLite is not PostgreSQL, MySQL, MongoDB, or a managed cloud database. Treating it as a production equivalent is how teams create a second class of false confidence. The useful framing is narrower and stronger: SQLite is excellent for testing data access contracts, API behavior, migration shape, idempotency, and transaction expectations when the application layer owns the abstraction clearly. It is a bad substitute for production load testing, distributed transaction behavior, replica lag, storage engine edge cases, or cloud IAM behavior.

McCracken’s broader Rust and Go angle fits this neatly. Rust gives backend teams strong type boundaries and memory safety for components where correctness and throughput matter. Go gives teams simple concurrency, deployment ergonomics, and a standard library that makes API services straightforward to ship. SQLite can sit under either language as a local test dependency, but the real value comes from how the service boundary is designed.

Solution approach: make SQLite test the contract, not the vendor

A pragmatic local test setup starts with a question that sounds boring but saves outages: what behavior must the API guarantee regardless of the backing database? For a REST API, that may include status codes, validation errors, pagination ordering, uniqueness constraints, idempotency keys, optimistic concurrency checks, and retry semantics. For a gRPC or internal service API, it may include request deadlines, typed error mapping, and whether repeated commands are safe.

SQLite is useful when those behaviors are expressed through a small data access layer rather than scattered SQL across handlers. In Rust, that might be a repository trait implemented by a SQLite adapter for local tests and a PostgreSQL adapter in production. In Go, it might be an interface around CreateOrder, GetUserByEmail, or MarkJobComplete, with tests running the same HTTP handlers against a real SQLite database. The point is not to hide every database capability. The point is to make the API tests assert business behavior while the lower level database integration tests cover production-specific SQL.

A local test might create a database file or in-memory database, apply migrations, insert fixtures, run the API call, then assert both the response and resulting database state. That gives the test enough realism to catch missing constraints, broken transaction handling, and state transition bugs. It also keeps the cost low enough that developers actually run it. A test suite that requires a long cloud setup often becomes a CI-only ritual, and CI-only tests are late feedback.

For API design, this pushes teams toward cleaner boundaries. Handlers should parse and validate requests, call application services, and translate domain errors into protocol responses. Application services should coordinate transactions and consistency rules. Database adapters should own SQL details. Once that separation exists, SQLite tests can exercise meaningful behavior without needing to reproduce every production dependency. The architecture becomes easier to reason about because each layer has a smaller failure surface.

Consistency is where this gets interesting. SQLite supports real transactions, isolation behavior, foreign keys, indexes, and write-ahead logging. That is enough to test many single-node invariants. For example, an API that creates a user and a default workspace should commit both records or neither. A job queue endpoint should not mark work complete if the worker fails halfway through a transaction. A payment intent endpoint should reject duplicate idempotency keys. Those are not distributed systems tests, but they are exactly the class of consistency bugs that show up as bad customer state.

The same design also helps with Rust and Go service choices. A Rust service using an async framework such as Axum can model request data tightly and return typed errors. A Go service can keep handlers small and rely on table-driven tests around HTTP behavior. In both cases, SQLite makes the database part of the test cheap enough to be normal. That is the operational win.

Scalability implications: fast local tests change engineering behavior

Scalability is not only about how many requests the production cluster handles. It is also about how many changes the engineering system can absorb without breaking. Slow tests reduce batch size. Developers wait longer, run less locally, and merge larger changes. Larger changes make failures harder to isolate. That is a scaling problem in the delivery pipeline, and it has the same shape as backpressure in a service mesh: when feedback gets slow, queues grow and failure recovery gets worse.

SQLite helps because local tests can run with little coordination. There is no shared database to corrupt, no test namespace collision, and no network dependency to flap. Each test can own its database file. Parallel test workers can run isolated schemas. CI can shard tests without provisioning one database per shard. Those details sound mechanical until a team loses hours to flaky integration environments. Then the appeal becomes obvious.

For API teams, this often supports a tiered test strategy. Unit tests cover pure logic. SQLite-backed API tests cover request to persistence behavior. Production database integration tests cover dialect-specific SQL, migrations, indexes, query plans, and driver behavior. End-to-end tests cover service composition. Load tests cover saturation, queuing, connection pools, and hot partitions. SQLite does not remove the need for the other layers. It makes one important layer fast and deterministic.

That distinction matters for distributed systems. SQLite will not model replica lag, leader election, cross-region writes, causal consistency, read-your-writes behavior across replicas, or the way a managed database throttles under noisy neighbors. If a production API depends on those properties, the team needs tests and staging exercises that use the real system. Local SQLite tests should catch contract regressions before those expensive tests run. They should not pretend the network does not exist.

API patterns that benefit most

The best fit is CRUD plus workflow APIs with clear state transitions. User registration, project creation, inventory reservation, token issuance, webhook processing, and job scheduling all benefit from real persistence in local tests. These APIs usually fail at the boundaries: duplicate requests, missing constraints, partial writes, stale versions, and unexpected retry order. SQLite lets those failures appear without a full production stack.

Idempotency is a good example. A payment or webhook endpoint may accept a unique idempotency key and return the same result for repeated requests. A mock can assert that a function was called once, but it rarely captures the database-level race. A SQLite test can create the unique index, run two requests, and check that the second request observes the stored result instead of creating a duplicate. That test does not prove the production database handles every concurrent edge case, but it forces the application contract into code.

Optimistic concurrency is another useful case. An API may require clients to send a version field when updating a document. The update should succeed only if the stored version still matches. In SQL, that often becomes an UPDATE ... WHERE id = ? AND version = ? statement followed by a row count check. SQLite can test that behavior locally. The broader distributed systems lesson is that consistency should be explicit at the API boundary. Hidden last-write-wins behavior usually becomes an incident report later.

Pagination and ordering also deserve attention. APIs that return lists need stable ordering, cursor semantics, and predictable behavior when new records arrive. SQLite tests can enforce that the API never depends on accidental row order. A service that only passes tests when rows come back in insertion order is already depending on undefined behavior. That kind of bug tends to surface under load, after an index change, or during a database upgrade.

Trade-offs: the sharp edges are real

SQLite’s strengths come from being embedded and simple to run. Those same strengths define the limits. It has different SQL dialect behavior from common production databases. Types, constraints, JSON support, time handling, locking, and migration semantics can differ. If the production system uses PostgreSQL features such as advisory locks, advanced indexes, row-level security, LISTEN/NOTIFY, or strict JSONB operators, SQLite tests can only cover the abstraction above those features unless the team writes careful compatibility layers.

Concurrency also needs discipline. SQLite can support many local testing needs, and WAL mode improves read and write behavior, but it is still not a distributed database. A production service with many instances, connection pooling, read replicas, and multi-region routing has failure modes SQLite will never show. That does not make SQLite a bad test tool. It means the team has to label the test honestly.

There is also a schema drift risk. If SQLite migrations are separate from production migrations, the two schemas will diverge. When that happens, local tests become decorative. A better approach is to generate test schemas from the same migration source when possible, or keep a narrow compatibility schema that is reviewed whenever production migrations change. Schema drift should be treated like API drift because it is API drift, just lower in the stack.

Managed databases such as MongoDB Atlas solve a different problem. They provide hosted operations, scaling controls, backups, monitoring, and cloud integration. That is valuable in production and staging, but it is heavier than what most developers need for every local test run. A good engineering setup can use both: SQLite for fast contract tests, and the managed production database for integration, migration, and performance validation.

The same trade-off applies to Rust and Go. Rust can prevent classes of memory and concurrency bugs before runtime, but it has a steeper learning curve and can slow early iteration when the team is still shaping the domain. Go makes service code easy to read and deploy, but it depends more on runtime tests and code review discipline for certain correctness guarantees. SQLite local testing does not choose between them. It supports both by making persistence behavior visible early.

What changes for teams

The practical change is cultural as much as technical. Teams stop treating local tests as a shallow smoke check and start treating them as the first consistency gate. A handler test should not only assert 201 Created. It should assert what was committed, what was rejected, what can be retried, and what state remains after failure. That is how APIs become dependable under pressure.

For a small backend project, the setup can be modest: a migration runner, a helper that creates a fresh SQLite database per test, and a set of API tests around the core workflows. For a larger service, the pattern can grow into a test matrix where SQLite-backed tests run on every change, production database integration tests run in CI, and load or failure-injection tests run before releases. The layers should complement each other instead of competing for one perfect test environment.

McCracken’s focus on efficient backend systems is directionally right, but the strongest lesson is not that Rust, Go, or SQLite are universally better choices. The stronger lesson is that backend reliability comes from matching tools to failure modes. Use Rust where memory safety and tight control matter. Use Go where straightforward services and operational simplicity matter. Use SQLite where local persistence tests need to be fast, isolated, and real enough to catch contract bugs.

A distributed systems engineer learns to distrust any test environment that hides the failure being discussed. SQLite is useful precisely when the failure is local state correctness, transaction shape, API contract drift, or migration behavior. It is insufficient when the failure depends on multiple nodes, network partitions, replica delay, or managed service limits. The value is in knowing the difference and encoding that difference into the test strategy.