What a database mock verifies, which bugs only show up against real Postgres, why SQLite is a poor stand-in, and how to make real-database tests fast enough to run on every change, with a Go example that tests a unique index against an isolated test database.

Mocking the database is the default advice in most unit testing guides, and for a function that formats an invoice it does the job. In a backend, many of the bugs live in SQL instead.
A unique index on lower(email) or a migration that renamed a column never trips a mock, since the query never runs. This guide covers where mocks still help and how to make real-database tests fast.
Mock the database only for code whose behavior does not depend on the database, and test everything that relies on SQL, constraints, migrations or transactions against the same Postgres version you run in production. The table compares the usual setups.
A mock checks that your code sent something that looks like the right query and then did the right thing with a result you made up. With go-sqlmock, a test for a user insert looks like this:
db, mock, _ := sqlmock.New()
mock.ExpectQuery(`INSERT INTO users`).
WithArgs("ada@example.com").
WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))
u, err := createUser(ctx, db, "ada@example.com")
// passes as long as the SQL starts with INSERT INTO users
The test passes even if the table has no email column, if the unique index is missing from the migration, or if a second insert of Ada@example.com would succeed in production because the index is case-sensitive. Martin Fowler's essay on mocks and stubs calls this behavior verification, where the test checks the calls made rather than the resulting state, and for a database the resulting state is the thing you care about.
Constraint violations are the most common, since the code path that turns Postgres error 23505 (unique_violation in the error code appendix) into a 409 response only runs when Postgres raises it. Migrations are next, because a mock knows nothing about the schema, so a migration that drops a column the code still reads goes unnoticed until deploy.
SQL itself is the third group: a typo in a column name, a GROUP BY that Postgres rejects, an ON CONFLICT clause that targets a constraint which does not exist. Transactions and locking round it out, since SELECT ... FOR UPDATE, isolation levels and deadlocks only behave like production when a real database is doing the locking.
It is faster to start, but it tests a different database. SQLite uses flexible typing, so it will store a string in an integer column without complaint, and its SQL dialect differs from Postgres in date functions, JSON operators and the index types it supports. A test suite that passes on SQLite tells you the logic is right for SQLite, and teams that try it usually end up adding a second, Postgres-only suite for the queries that matter.
Most of the cost is setup, not queries, so run the migrations once and clone the result. Postgres can create a database from a template with CREATE DATABASE test_42 TEMPLATE app_template, which copies the already migrated database instead of replaying every migration, as described in the template databases docs. For a typical application schema that makes a fresh database per test cheap compared to running the migrations again.
For a throwaway test cluster you can also turn durability off. The fsync setting can be disabled when losing the data on a crash does not matter, which is exactly the case for tests, and putting the data directory on a RAM disk removes most of the remaining disk wait.
Rolling back a transaction at the end of each test is the other common trick, and it is fast, but it breaks down as soon as the code under test opens its own transactions or commits, and it hides bugs that only appear after a commit, such as deferred constraints.
Mocks fit at the edges of your system, where the real dependency is slow, costs money or is out of your control. Payment providers, email senders and other teams' APIs are good candidates, as are failure cases that are hard to produce on demand, such as a connection drop halfway through a request. Pure logic that never touches the database does not need a database either, and splitting it out into functions that take plain values keeps those tests fast without any mocking at all.
Encore brings infrastructure from code, so the Postgres database a test needs is the one the service already declares, and encore test starts it locally, applies the migrations and runs your normal Go tests against it. Per the testing docs, the test databases run in a separate cluster configured to skip fsync and use an in-memory filesystem, which keeps integration tests close to unit-test speed.

Run in your terminal to get started locally.
The migration adds the case-insensitive unique index that a mock would never see:
-- users/migrations/1_create_users.up.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Ada@example.com and ada@example.com are the same person.
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email));
The service declares the database and maps a unique violation to an AlreadyExists error, which Encore returns as HTTP 409:
// users/users.go
package users
import (
"context"
"strings"
"encore.dev/beta/errs"
"encore.dev/storage/sqldb"
"encore.dev/storage/sqldb/sqlerr"
)
// Encore starts this Postgres database locally and in tests, and
// provisions it in your cloud when you deploy.
var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{
Migrations: "./migrations",
})
type RegisterParams struct {
Email string `json:"email"`
}
type User struct {
ID int64 `json:"id"`
Email string `json:"email"`
}
//encore:api public method=POST path=/users
func Register(ctx context.Context, p *RegisterParams) (*User, error) {
return createUser(ctx, db, p.Email)
}
// createUser takes the database as an argument so a test can pass
// its own isolated copy.
func createUser(ctx context.Context, db *sqldb.Database, email string) (*User, error) {
u := &User{Email: strings.TrimSpace(email)}
err := db.QueryRow(ctx,
`INSERT INTO users (email) VALUES ($1) RETURNING id`, u.Email,
).Scan(&u.ID)
if sqldb.ErrCode(err) == sqlerr.UniqueViolation {
return nil, &errs.Error{Code: errs.AlreadyExists, Message: "email already registered"}
}
if err != nil {
return nil, err
}
return u, nil
}
The test asks for its own database with et.NewTestDatabase, which Encore clones from a template that already has every migration applied and drops when the test ends:
// users/users_test.go
package users
import (
"context"
"testing"
"encore.dev/beta/errs"
"encore.dev/et"
)
func TestDuplicateEmailIsCaseInsensitive(t *testing.T) {
ctx := context.Background()
// A fresh, fully migrated database that only this test sees.
testDB, err := et.NewTestDatabase(ctx, "users")
if err != nil {
t.Fatal(err)
}
if _, err := createUser(ctx, testDB, "Ada@example.com"); err != nil {
t.Fatalf("first insert: %v", err)
}
_, err = createUser(ctx, testDB, "ada@example.com")
if errs.Code(err) != errs.AlreadyExists {
t.Fatalf("want AlreadyExists, got %v", err)
}
}
Run it with encore test ./..., which wraps go test and accepts the same flags. If someone removes lower() from the index, this test fails on the laptop instead of in production. Each test also shows up as a trace in the local development dashboard at localhost:9400, so a failing assertion comes with the exact queries that ran. For calls to other services or external APIs, et.MockEndpoint and et.MockService replace them per test, as covered in the mocking docs, which keeps mocks at the edges and the database real.
When you push, the same declaration becomes a managed Postgres database in your AWS or GCP account, such as Amazon RDS on AWS or Cloud SQL on GCP, with the same migrations applied.
A coding agent that adds a column, a migration and the code that uses it can run encore test in the same session and see a broken constraint locally, since the test database comes from the same declaration the agent edited.
Best fit: Backends on AWS or GCP that want integration tests against real Postgres on every change, with isolated databases per test and the same schema in tests and production.
No. Only for code that does not depend on what the database does, such as formatting, validation or pricing logic. Code that relies on SQL, constraints, migrations or transactions should be tested against the same Postgres version you run in production, because a mock returns whatever the test told it to and never runs the query.
No. SQLite uses flexible typing, has a different SQL dialect and lacks Postgres features such as JSONB operators and many index types, so tests can pass on SQLite and fail on Postgres. Run the tests against Postgres, ideally the same major version as production.
Give each test its own data, either with unique values per test on a shared database, a transaction per test that is rolled back at the end, or a fresh database per test cloned from a migrated template database, which Postgres creates quickly from the TEMPLATE option of CREATE DATABASE.
Declare the database with sqldb.NewDatabase and its migrations, write normal Go tests, and run them with encore test. For a test that needs a clean database, call et.NewTestDatabase(ctx, "users") to get an isolated, fully migrated copy that is dropped when the test ends.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.