Move From database sql to sqlc One Query at a Time

You have a service with a few hundred queries written against database/sql. You like the idea of sqlc, SQL files turned into typed Go with errors caught at build time, and you do not have a month to rewrite the data layer. You do not need one. sqlc can be adopted one query at a time, in the same package, over the same connection pool, with the old and new code running side by side. This article shows the path on Go 1.25.6 with sqlc v1.30.0 against PostgreSQL 18.6: put a seam in front of the queries, pin the behaviour with a contract test, move two queries, and leave the third where it is.

It belongs to the database cornerstone. The tool itself is introduced in sqlc from install to type safe queries, and the interface technique comes from small store interfaces.

The plan

A migration like this works because every step leaves the program working and tested.

1. Introduce a seam. Callers depend on a Store interface, not on the SQL.
2. Pin the behaviour. A contract test that every Store implementation must pass, including its errors.
3. Add sqlc beside the old code, generating into the same package, using the same *sql.DB.
4. Move one query, run the contract test, commit.
5. Repeat, in any order, for as long as it pays.
6. Delete the old implementation when nothing uses it.

You can stop at any step. A service with half its queries on sqlc is in a fine state, and the half that is not has a test.

The starting point

package main

import (
	"context"
	"database/sql"
	"errors"
)

var ErrNotFound = errors.New("not found")

type Account struct {
	ID    int64
	Email string
	Plan  string
}

// Store is the seam: callers depend on it, and each method can move from handwritten SQL to sqlc on its own.
type Store interface {
	ByEmail(ctx context.Context, email string) (Account, error)
	SetPlan(ctx context.Context, id int64, plan string) error
	Create(ctx context.Context, email string) (Account, error)
}

// Handwritten is the code as it was before the migration.
type Handwritten struct{ DB *sql.DB }

func (s Handwritten) ByEmail(ctx context.Context, email string) (Account, error) {
	var a Account
	err := s.DB.QueryRowContext(ctx, "select id, email, plan from accounts657 where email = $1", email).Scan(&a.ID, &a.Email, &a.Plan)
	if errors.Is(err, sql.ErrNoRows) {
		return Account{}, ErrNotFound
	}
	return a, err
}

func (s Handwritten) SetPlan(ctx context.Context, id int64, plan string) error {
	res, err := s.DB.ExecContext(ctx, "update accounts657 set plan = $2 where id = $1", id, plan)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s Handwritten) Create(ctx context.Context, email string) (Account, error) {
	a := Account{Email: email, Plan: "free"}
	err := s.DB.QueryRowContext(ctx, "insert into accounts657 (email) values ($1) returning id", email).Scan(&a.ID)
	return a, err
}

// Mixed has moved the two queries that exist in query.sql to sqlc and left Create alone.
type Mixed struct {
	DB *sql.DB
	Q  *Queries
}

func (s Mixed) ByEmail(ctx context.Context, email string) (Account, error) {
	row, err := s.Q.AccountByEmail(ctx, email)
	if errors.Is(err, sql.ErrNoRows) {
		return Account{}, ErrNotFound
	}
	return Account{ID: row.ID, Email: row.Email, Plan: row.Plan}, err
}

func (s Mixed) SetPlan(ctx context.Context, id int64, plan string) error {
	n, err := s.Q.SetPlan(ctx, SetPlanParams{ID: id, Plan: plan})
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s Mixed) Create(ctx context.Context, email string) (Account, error) {
	return Handwritten{s.DB}.Create(ctx, email)
}

Store has three methods. Handwritten is the code as it was: database/sql calls, with sql.ErrNoRows mapped to ErrNotFound and zero rows affected on an update mapped to the same error. Those two mappings are part of the contract, since callers rely on errors.Is(err, ErrNotFound). That is why the seam is worth building first: the behaviours that make a data layer correct are the error cases, and they are the easiest to lose when rewriting.

sqlc beside the old code

Two files describe the schema and the queries sqlc will generate for:

create table accounts657 (
    id    bigserial primary key,
    email text not null unique,
    plan  text not null default 'free'
);

-- name: AccountByEmail :one
select id, email, plan from accounts657 where email = $1;

-- name: SetPlan :execrows
update accounts657 set plan = $2 where id = $1;

AccountByEmail returns one row; SetPlan is marked :execrows, which makes the generated function return the number of rows affected, the information the handwritten version used for ErrNotFound. The configuration chooses where the code goes and which package it uses:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: database/sql

Three settings make a gradual move possible. package: main and out: . put the generated files, db.go, models.go and query.sql.go, in the same package as the existing code. sql_package: database/sql makes the generated Queries take a *sql.DB or *sql.Tx through its DBTX interface, so it shares the pool, the transactions and the instrumentation you already have; the other option, pgx/v5, would need a pgx pool and is a later, separate decision (see sqlc with pgx v5). And schema.sql is the schema sqlc checks queries against, which can point at your migrations folder instead of a copy.

The Mixed type in store.go is the middle state. It holds the *sql.DB and a *Queries. Its ByEmail calls Q.AccountByEmail and maps the error exactly as before. Its SetPlan calls Q.SetPlan, which returns the affected row count, and maps zero to ErrNotFound. Its Create delegates to the handwritten version, because that query is not in query.sql yet. Callers cannot tell the difference.

The contract test

package main

import (
	"context"
	"database/sql"
	"errors"
	"testing"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func open(t *testing.T) *sql.DB {
	t.Helper()
	db, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { db.Close() })
	for _, q := range []string{"drop table if exists accounts657", "create table accounts657 (id bigserial primary key, email text not null unique, plan text not null default 'free')"} {
		if _, err := db.Exec(q); err != nil {
			t.Fatal(err)
		}
	}
	return db
}

// contract is the behaviour both implementations must share; it is what lets one query move at a time.
func contract(t *testing.T, s Store) {
	ctx := context.Background()
	made, err := s.Create(ctx, "[email protected]")
	if err != nil {
		t.Fatal(err)
	}
	got, err := s.ByEmail(ctx, "[email protected]")
	if err != nil || got != made {
		t.Fatalf("ByEmail: got %+v, %v; want %+v", got, err, made)
	}
	if _, err := s.ByEmail(ctx, "[email protected]"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("missing email: got %v, want ErrNotFound", err)
	}
	if err := s.SetPlan(ctx, made.ID, "pro"); err != nil {
		t.Fatal(err)
	}
	if got, _ := s.ByEmail(ctx, "[email protected]"); got.Plan != "pro" {
		t.Fatalf("plan after SetPlan: %q", got.Plan)
	}
	if err := s.SetPlan(ctx, made.ID+1000, "pro"); !errors.Is(err, ErrNotFound) {
		t.Fatalf("SetPlan on a missing id: got %v, want ErrNotFound", err)
	}
}

func TestHandwritten(t *testing.T) { db := open(t); contract(t, Handwritten{db}) }

func TestMixedWithSqlc(t *testing.T) {
	db := open(t)
	contract(t, Mixed{DB: db, Q: New(db)})
}

contract runs the same checks against any Store: create an account, read it back and compare the whole struct, look up an email that does not exist and expect ErrNotFound, change the plan and read it back, and change the plan of an id that does not exist and expect ErrNotFound again. Two tests call it, one with Handwritten and one with Mixed. The script regenerates the sqlc code and runs them:

set -e
"$HOME/go/bin/sqlc" generate
go test -trimpath -v -count=1 .

=== RUN   TestHandwritten
--- PASS: TestHandwritten (0.03s)
=== RUN   TestMixedWithSqlc
--- PASS: TestMixedWithSqlc (0.02s)
PASS
ok  	example.test/lab	0.337s

Both pass: TestHandwritten and TestMixedWithSqlc. That is the property that makes the migration safe. The test does not know or care how a method is implemented; it pins what callers see. Add a case to contract whenever you discover a behaviour that matters, such as how a unique violation is reported or what an empty result looks like, and every implementation inherits it.

What changes when a query moves

For each query, the work is the same:

1. Copy the SQL into query.sql with a -- name: comment and the right command: :one, :many, :exec or :execrows.
2. Run sqlc generate. If the SQL is wrong for the schema, this is where you find out, as an error from the generator and not as a run time failure.
3. Replace the body of the handwritten method with a call to the generated one, keeping the error mapping.
4. Run the contract test.

The differences to watch:

• Row types. sqlc generates a struct per query result (AccountByEmailRow) or reuses the table's model if the columns match. Map to your domain type at the boundary, as Mixed.ByEmail does, so the generated types do not leak into callers and a column change touches one function.
• Nullable columns become sql.NullString and friends in the generated types. A column that was string in your struct and is nullable in the schema will show up here. The overrides setting maps them to your preferred types; see sqlc overrides.
• Argument order and names. Positional parameters become fields of a ...Params struct, named from the columns. That makes calls with several arguments harder to get wrong than the positional Exec.
• Dynamic SQL does not fit. A query with optional filters assembled in Go has no static form for sqlc. Leave it handwritten, or express it with sqlc.narg and coalesce as in sqlc dynamic queries. Do not force it.
• Transactions keep working. Queries has a WithTx(tx) method; the pattern is in sqlc transactions.
• Errors are the same. sqlc does not wrap driver errors: sql.ErrNoRows still comes from a :one query, and driver errors pass through unchanged, so your existing mapping stays.

Order of work

Start with the queries that benefit most: the ones with many columns, where a typo in a handwritten Scan is likely, and the ones that changed recently. Leave dynamic queries and rarely touched ones for last, and possibly forever. Move write queries after read queries, since reads are safe to compare. Do a handful per pull request so the reviews stay small, and keep each commit green. The generated files are committed to the repository, so reviewers can read them and CI does not need sqlc, with one check: a CI step that runs sqlc generate and fails if the working tree changes, which catches a changed query whose code was not regenerated. Add sqlc vet, covered in sqlc vet and linting, when the file grows.

When not to migrate

If the data layer is small and stable, the migration is churn: the point of sqlc is fewer mistakes in code you keep changing. If most queries are dynamic, you will move few of them. And if the team is not comfortable with a code generation step in the build, resolve that first: the tool is well behaved, and the workflow still has to be agreed. Done carefully, the migration is boring in the best way, a series of small commits, none of which changes what the service does. The configuration is documented at docs.sqlc.dev, and the database/sql package generation in its guide for PostgreSQL.