Migrate From GORM to sqlc Gradually Without a Big Rewrite

Rewriting a data layer in one go is how projects stall. Every query changes at once, the risk is the sum of all of them, and the branch lives for weeks while the rest of the code moves. The alternative is the strangler approach: put a seam in front of the old implementation, build the new one behind the same seam, and move one operation at a time with both implementations live. This article does that for GORM to sqlc on PostgreSQL 18.6, and shows the two mechanisms that make it safe: a shared connection pool and a shared transaction.

It assumes you know both tools. The tutorials are GORM and sqlc, and the reasons to move (or not) are in sqlc versus GORM and Ent. For other migrations in the cluster, see from lib/pq to pgx and the Ent migration.

Before you start

Check that the reason to migrate is real. Common good reasons: queries are mostly hand tuned SQL anyway, performance work keeps leading around the ORM, the team wants SQL reviewed as SQL, or compile time checking of queries would have caught production bugs. Poor reasons: a new tool is interesting, or one query was slow (fix that query, in GORM or with Raw).

If you go ahead, migrate by operation, in order of value: hot paths and the queries with the most complex SQL first, plain CRUD last or never. A service that ends with sqlc for reports and GORM for simple tables is a fine result.

Step 1: a seam

The migration needs an interface that does not mention either library. In the lab it is UserRepo with three methods, and a domain User struct. Anything that calls the store depends on the interface. If your code calls db.Where(...) from handlers today, this is the real first step, and it is worth doing even if you never migrate: extract the queries into methods first, with tests, while the implementation is still GORM.

The interface speaks in domain types and domain errors (ErrNotFound), so neither GORM's ErrRecordNotFound nor sql.ErrNoRows escapes. Both implementations translate their own errors at the boundary, which is the part that most often differs between them.

Step 2: sqlc on the same pool

The schema the queries are checked against is the migrated schema, and the queries are ordinary named SQL:

create table users (
  id    bigserial primary key,
  email text not null unique,
  name  text not null
);
create table orders (
  id         bigserial primary key,
  user_id    bigint not null references users(id),
  total_cents int not null
);

-- name: UserByEmail :one
select id, email, name from users where email = $1;

-- name: CreateUser :one
insert into users (email, name) values ($1, $2) returning id;

-- name: CountOrders :one
select count(*) from orders where user_id = $1;

The configuration is the part that makes the gradual approach work:

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

sql_package: database/sql generates code that takes a DBTX, an interface with ExecContext, QueryContext and QueryRowContext. The default pgx/v5 mode would want a pgxpool.Pool, a second pool with its own settings, and no way to join GORM's transactions. In database/sql mode, the generated Queries runs on the *sql.DB that GORM already holds, which db.DB() returns. The service keeps one pool, one set of connection limits and one place to read pool metrics (pool settings).

You give up pgx specific types (pgtype) and COPY support through the generated code in this mode. Keep that in mind for the final state: if you end up all in on sqlc, switching the generator to pgx mode later is a mechanical change.

Step 3: two implementations and one contract

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"os"

	"example.test/lab/sqlcdb"
	_ "github.com/jackc/pgx/v5/stdlib"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// User is the domain type. Neither GORM nor sqlc types leak past the repository.
type User struct {
	ID    int64
	Email string
	Name  string
}

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

// UserRepo is the seam the migration happens behind.
type UserRepo interface {
	ByEmail(ctx context.Context, email string) (User, error)
	Create(ctx context.Context, email, name string) (int64, error)
	CountOrders(ctx context.Context, userID int64) (int64, error)
}

type userRow struct {
	ID    int64
	Email string
	Name  string
}

func (userRow) TableName() string { return "users" }

type orderRow struct {
	ID         int64
	UserID     int64
	TotalCents int
}

func (orderRow) TableName() string { return "orders" }

// gormRepo is the existing implementation.
type gormRepo struct{ db *gorm.DB }

func (r gormRepo) ByEmail(ctx context.Context, email string) (User, error) {
	var row userRow
	err := r.db.WithContext(ctx).Where("email = ?", email).Take(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return User{}, ErrNotFound
	}
	return User(row), err
}

func (r gormRepo) Create(ctx context.Context, email, name string) (int64, error) {
	row := userRow{Email: email, Name: name}
	err := r.db.WithContext(ctx).Create(&row).Error
	return row.ID, err
}

func (r gormRepo) CountOrders(ctx context.Context, userID int64) (int64, error) {
	var n int64
	err := r.db.WithContext(ctx).Model(&orderRow{}).Where("user_id = ?", userID).Count(&n).Error
	return n, err
}

// sqlcRepo is the replacement, built over the same connection pool.
type sqlcRepo struct{ q *sqlcdb.Queries }

func (r sqlcRepo) ByEmail(ctx context.Context, email string) (User, error) {
	u, err := r.q.UserByEmail(ctx, email)
	if errors.Is(err, sql.ErrNoRows) {
		return User{}, ErrNotFound
	}
	return User{ID: u.ID, Email: u.Email, Name: u.Name}, err
}

func (r sqlcRepo) Create(ctx context.Context, email, name string) (int64, error) {
	return r.q.CreateUser(ctx, sqlcdb.CreateUserParams{Email: email, Name: name})
}

func (r sqlcRepo) CountOrders(ctx context.Context, userID int64) (int64, error) {
	return r.q.CountOrders(ctx, userID)
}

// contract is the one suite both implementations must pass.
func contract(name string, r UserRepo, prefix string) {
	ctx := context.Background()
	id, err := r.Create(ctx, prefix+"[email protected]", "Ada")
	fmt.Printf("%-5s create: id>0=%v err=%v\n", name, id > 0, err)
	u, err := r.ByEmail(ctx, prefix+"[email protected]")
	fmt.Printf("%-5s by email: %q err=%v\n", name, u.Name, err)
	_, err = r.ByEmail(ctx, "[email protected]")
	fmt.Printf("%-5s missing: notfound=%v\n", name, errors.Is(err, ErrNotFound))
	_, err = r.Create(ctx, prefix+"[email protected]", "Dup")
	fmt.Printf("%-5s duplicate rejected: %v\n", name, err != nil)
	n, err := r.CountOrders(ctx, u.ID)
	fmt.Printf("%-5s orders: %d err=%v\n", name, n, err)
}

func main() {
	log.SetFlags(0)
	ctx := context.Background()
	dsn := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Discard})
	if err != nil {
		log.Fatal(err)
	}
	admin.Exec("drop schema if exists l0734 cascade")
	admin.Exec("create schema l0734")
	db, err := gorm.Open(postgres.Open(dsn+" search_path=l0734"), &gorm.Config{Logger: logger.Discard})
	if err != nil {
		log.Fatal(err)
	}
	ddl, _ := os.ReadFile("schema.sql")
	if err := db.Exec(string(ddl)).Error; err != nil {
		log.Fatal(err)
	}

	// Step 1: sqlc over the pool GORM already owns. One pool, one set of credentials.
	sqlDB, _ := db.DB()
	q := sqlcdb.New(sqlDB)
	fmt.Println("== 1. the same contract against both implementations")
	contract("gorm", gormRepo{db}, "g-")
	contract("sqlc", sqlcRepo{q}, "s-")

	// Step 2: both see each other's writes, because it is one database.
	g, _ := gormRepo{db}.ByEmail(ctx, "[email protected]")
	s, _ := sqlcRepo{q}.ByEmail(ctx, "[email protected]")
	fmt.Println("== 2. cross reads:", g.Name, s.Name)

	// Step 3: one transaction that uses both. GORM's tx connection satisfies sqlc's DBTX interface.
	err = db.Transaction(func(tx *gorm.DB) error {
		row := userRow{Email: "[email protected]", Name: "Tx"}
		if err := tx.Create(&row).Error; err != nil {
			return err
		}
		seen, err := sqlcdb.New(tx.Statement.ConnPool).UserByEmail(ctx, "[email protected]")
		fmt.Println("== 3. sqlc sees the GORM write inside the transaction:", seen.Name, err)
		return errors.New("roll back")
	})
	_, after := sqlcRepo{q}.ByEmail(ctx, "[email protected]")
	fmt.Println("   after rollback:", err, "| visible:", !errors.Is(after, ErrNotFound))

	// Step 4: the cutover is a one line change where the repository is built.
	var repo UserRepo = gormRepo{db}
	if os.Getenv("USE_SQLC") == "1" {
		repo = sqlcRepo{q}
	}
	fmt.Printf("== 4. wired implementation: %T\n", repo)
}

gormRepo is the existing code behind the interface. sqlcRepo is the replacement. The contract function is the migration's definition of done: it takes any UserRepo, runs the same checks, and prints the result per implementation. The script generates the sqlc code, tidies the module and runs everything:

set -e
~/go/bin/sqlc generate
go mod tidy 2>&1 | tail -3
go run .
USE_SQLC=1 go run . | tail -1

== 1. the same contract against both implementations
gorm  create: id>0=true err=<nil>
gorm  by email: "Ada" err=<nil>
gorm  missing: notfound=true
gorm  duplicate rejected: true
gorm  orders: 0 err=<nil>
sqlc  create: id>0=true err=<nil>
sqlc  by email: "Ada" err=<nil>
sqlc  missing: notfound=true
sqlc  duplicate rejected: true
sqlc  orders: 0 err=<nil>
== 2. cross reads: Ada Ada
== 3. sqlc sees the GORM write inside the transaction: Tx <nil>
   after rollback: roll back | visible: false
== 4. wired implementation: main.gormRepo
== 4. wired implementation: main.sqlcRepo

The output for section 1 shows both repositories behaving the same:

• create returned an id greater than zero and a nil error for both.
• by email returned "Ada".
• missing returned ErrNotFound for both, which is the translation each implementation has to do for itself.
• duplicate rejected was true for both.
• orders returned 0 for both.

That is five behaviours, and they are the ones to write down before migrating a method: the happy path, the missing row, the constraint, the empty list and the zero. Teams skip the "missing" and "duplicate" cases, and those are where two implementations differ.

In a real project this is a Go test, not a printed table, run with a table of implementations: for name, repo := range repos { t.Run(name, func(t *testing.T) { contract(t, repo) }) }. When the method is migrated and the contract passes, delete the GORM version.

Step 4: both live on one database

Section 2 of the output, cross reads: Ada Ada, shows the property that makes gradual migration cheap: a user created through GORM was read back through sqlc, and the reverse. There is one database and no data copy, so each method can switch independently, and a rollback is switching back.

Step 5: share a transaction

The harder case is a unit of work that spans both. Suppose a method inside a GORM transaction still needs a query that is now in sqlc. If sqlc used a different connection, it would not see the transaction's uncommitted writes, and a rollback would not cover it.

Section 3 solves it. Inside db.Transaction, tx.Statement.ConnPool is the transaction's connection (a *sql.Tx), and it satisfies sqlc's DBTX. sqlcdb.New(tx.Statement.ConnPool) builds a Queries bound to the transaction. The output: sqlc sees the GORM write inside the transaction: Tx <nil>, then the closure returned an error and after rollback: roll back | visible: false. The row GORM inserted was visible to sqlc inside the transaction and gone from both after the rollback.

This is the feature that removes the fear from the migration. You can convert a method that runs in the middle of a transaction without touching the transaction code. Do it in a small helper so the cast lives in one place, and add a test that the helper sees uncommitted data, because that is the property you rely on.

Step 6: switch with a flag

Section 4 builds the repository from an environment variable: the default prints main.gormRepo, and USE_SQLC=1 prints main.sqlcRepo. A per method or per percentage flag is the same idea at a finer grain. A flag lets you deploy the new code path turned off, enable it for a canary or for staff, compare results, and turn it off if something is wrong, without a deploy.

For read paths you can go further and run both and compare: call GORM, call sqlc in the background, log mismatches, return the GORM answer. A week of silence is evidence. Do not do this for writes.

What tends to go wrong

• Different null handling. GORM scans NULL into zero values or pointers by the struct you chose. sqlc generates sql.NullString or pgtype.Text. Map explicitly in the repository and cover nullable columns in the contract.
• Time precision and zones. Both go through the same driver, but a hand written query may select now() or cast differently. Compare timestamps after truncation.
• Soft deleted rows. GORM adds deleted_at is null for you. A sqlc query does not. Every migrated query on a soft delete table needs the condition written, which is the most common regression in this migration. Put it in a view or a test.
• Hooks and default values. A BeforeCreate hook that sets a slug or a UUID stops running when the write moves to sqlc. Find them before migrating the write.
• Association writes. GORM's nested creates have no equivalent. Replace them with explicit statements in a transaction.
• Two sources of truth for the schema. GORM AutoMigrate and sqlc's schema file will drift. Move the schema to versioned migrations first (the migrations article).

An order of work

1. Extract an interface and move query code behind it. Add the contract tests against GORM.
2. Move schema ownership to SQL migrations.
3. Add sqlc in database/sql mode, with sqlc vet and diff in CI (sqlc vet).
4. Migrate read methods first, behind a flag, one method per pull request.
5. Migrate writes, starting with the ones with no hooks or associations.
6. When the last use of a model is gone, delete it. Leave GORM in place if some tables are fine as they are.

Checklist

• Define the seam and the contract before writing any sqlc.
• Use sql_package: database/sql to share the pool and the transaction.
• Test missing, duplicate and null cases for each method.
• Watch for soft delete conditions and hooks that disappear.
• Switch with a flag and keep rollback one setting away.

The tool documentation is in the sqlc docs and the underlying pattern in Martin Fowler's description of the strangler fig application.