sqlc in a Monorepo With Many Services and Shared Schemas

One repository, several services, one PostgreSQL: that arrangement is common, and it raises a question the single service tutorial never meets. Who owns which table? If every service reads one schema.sql, every service can call every query, and the compiler stops helping with the boundaries you meant to have. If every service keeps its own copy of the shared tables, the copies drift. sqlc's configuration can express a middle path, and this article builds it and checks what it does.

The setup is two services, accounts and billing, that share a customers table and each own one more. It runs on PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6. It extends the sqlc tutorial and the configuration article, and its neighbours are the repository pattern for how the generated code is wrapped, and multi-tenant database designs for a different kind of sharing. All of it sits in the database cornerstone.

The layout

sqlc.yaml
shared/schema.sql          the customers table, owned by nobody in particular
accounts/schema.sql        profiles
accounts/queries/*.sql
billing/schema.sql         invoices
billing/queries/*.sql
accountsdb/                generated
billingdb/                 generated

Each service owns a folder with its schema additions and its queries. The shared folder holds tables that more than one service needs to know about. Generated code goes into one output directory per service, named so that an import path says whose queries it is.

The configuration is the part that makes it work:

version: "2"
sql:
  - name: accounts
    engine: postgresql
    schema: ["shared/schema.sql", "accounts/schema.sql"]
    queries: accounts/queries
    gen:
      go:
        package: accountsdb
        out: accountsdb
        sql_package: pgx/v5
  - name: billing
    engine: postgresql
    schema: ["shared/schema.sql", "billing/schema.sql"]
    queries: billing/queries
    gen:
      go:
        package: billingdb
        out: billingdb
        sql_package: pgx/v5

Each entry has its own name, schema, queries and gen block, which makes each an independent run of the generator. The key line is schema: ["shared/schema.sql", "accounts/schema.sql"]: a list, read in order. sqlc builds the catalogue for that entry from exactly those files and nothing else. So accountsdb is generated from customers and profiles, and billingdb from customers and invoices.

The shared table:

create table customers (
    id    bigserial primary key,
    email text not null unique
);

and each service's own:

create table profiles (
    customer_id bigint primary key references customers (id),
    display     text not null
);

create table invoices (
    id          bigserial primary key,
    customer_id bigint not null references customers (id),
    cents       int not null
);

Notice what the billing file does: invoices has a foreign key to customers (id). sqlc needs to know customers exists to accept that, and it does, because shared/schema.sql is in billing's list. The accounts service does not list the billing schema, so it cannot see invoices, and a query in the accounts folder that mentions it fails at generate time with "relation does not exist". That is the boundary. It is enforced by the compiler instead of by a convention in a wiki.

The queries

Accounts adds a customer, sets a profile and reads one back with a join:

-- name: AddCustomer :one
insert into customers (email) values ($1) returning *;

-- name: SetProfile :exec
insert into profiles (customer_id, display) values ($1, $2)
on conflict (customer_id) do update set display = excluded.display;

-- name: ProfileFor :one
select c.email, p.display from customers c join profiles p on p.customer_id = c.id where c.id = $1;

Billing records invoices and totals what a customer owes:

-- name: Invoice :one
insert into invoices (customer_id, cents) values ($1, $2) returning *;

-- name: Owed :one
select c.email, coalesce(sum(i.cents), 0)::bigint as cents
from customers c left join invoices i on i.customer_id = c.id where c.id = $1 group by c.id;

Both folders are directories of .sql files, so a service can split its queries by topic without touching the configuration.

What came out

The lab loads one combined schema into PostgreSQL (the services share one database here), runs sqlc generate, lists the generated files, and runs a program that uses both packages:

create table customers (
    id    bigserial primary key,
    email text not null unique
);
create table profiles (
    customer_id bigint primary key references customers (id),
    display     text not null
);
create table invoices (
    id          bigserial primary key,
    customer_id bigint not null references customers (id),
    cents       int not null
);

package main

import (
	"context"
	"fmt"
	"log"

	"example.test/lab/accountsdb"
	"example.test/lab/billingdb"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/sqlc0699?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()

	acc, bill := accountsdb.New(pool), billingdb.New(pool)
	c, err := acc.AddCustomer(ctx, "[email protected]")
	must(err)
	must(acc.SetProfile(ctx, accountsdb.SetProfileParams{CustomerID: c.ID, Display: "Ada L."}))
	_, err = bill.Invoice(ctx, billingdb.InvoiceParams{CustomerID: c.ID, Cents: 1250})
	must(err)
	_, err = bill.Invoice(ctx, billingdb.InvoiceParams{CustomerID: c.ID, Cents: 500})
	must(err)

	p, err := acc.ProfileFor(ctx, c.ID)
	must(err)
	o, err := bill.Owed(ctx, c.ID)
	must(err)
	fmt.Printf("1. accountsdb sees: %+v\n", p)
	fmt.Printf("2. billingdb sees:  %+v\n", o)
	fmt.Printf("3. the customers table is generated into both packages: %T and %T\n", accountsdb.Customer{}, billingdb.Customer{})
	fmt.Printf("4. billingdb has no Profile type; accountsdb has no Invoice type: %v\n", noCross())
}

package main

import (
	"os"
	"strings"
)

// noCross reports whether each generated package leaves out the other service's tables.
func noCross() bool {
	read := func(p string) string { b, _ := os.ReadFile(p); return string(b) }
	return !strings.Contains(read("billingdb/models.go"), "type Profile ") &&
		!strings.Contains(read("accountsdb/models.go"), "type Invoice ")
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0699
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go mod tidy -e >/dev/null 2>&1
find . -name '*.go' -path './*db/*' | sort
go run .

./accountsdb/db.go
./accountsdb/models.go
./accountsdb/profiles.sql.go
./billingdb/db.go
./billingdb/invoices.sql.go
./billingdb/models.go
1. accountsdb sees: {Email:[email protected] Display:Ada L.}
2. billingdb sees:  {Email:[email protected] Cents:1750}
3. the customers table is generated into both packages: accountsdb.Customer and billingdb.Customer
4. billingdb has no Profile type; accountsdb has no Invoice type: true

The listing shows two packages with three files each: db.go, models.go and one file per query source (profiles.sql.go, invoices.sql.go). The file name follows the .sql file the queries came from, so splitting queries across files splits the generated code the same way.

Both services read the same row. The accounts service created the customer, set the display name Ada L. and read back {Email:[email protected] Display:Ada L.}. The billing service wrote two invoices, 1250 and 500, and read back {Email:[email protected] Cents:1750}. They share a database and a table, and each sees its own slice of the schema.

The shared table was generated twice. The third output line printed accountsdb.Customer and billingdb.Customer: two distinct Go types with identical fields. That is the cost of this design and the thing to understand before adopting it. A function that takes an accountsdb.Customer cannot accept a billingdb.Customer, even though they describe the same row, because they are different named types in different packages.

Each package leaves out the other's tables. The last line checked the generated models.go files: billingdb has no Profile struct and accountsdb has no Invoice struct, and it printed true. The boundary exists in the generated code, not only in the config.

One practical wrinkle. The program imports the generated packages, which did not exist when the Go module was set up. Until go mod tidy runs after generation, go run stops with a missing go.sum entry for a module that pgxpool needs. The lab's script runs go mod tidy between sqlc generate and go run. In a real repository the dependencies are already in go.sum, so you meet this only the first time, but a CI job that starts from a clean checkout and generates first should run it.

Where shared tables should live

There are three ways to handle a table that two services need. Each trades duplication against coupling, and which one is right depends on how much the two services should know about each other.

1. Duplicate the type, share the schema file (what the lab did). Each service gets its own Customer. The services stay independent: changing a column regenerates both, and a service that does not use a column does not care. The cost is the duplicate types and, if the two services must exchange a customer, a conversion at the boundary. For services that talk to each other over an API this is not a cost, since the API has its own types anyway.

2. Generate the shared tables once, into a platform package. Add a third entry with only shared/schema.sql and its own queries (GetCustomer, AddCustomer), generated into a platformdb package that both services import. There is one Customer type. The cost is coupling: the services now depend on a shared package, and a change to a shared query affects both. That is the right trade when the shared table is truly shared state, such as users, tenants or reference data, and the wrong one when the "sharing" is only that two services happened to join to the same table.

3. Do not share tables. Each service owns its tables and reads another service's data through that service's API or a replicated copy. This is the strongest boundary and a different architecture, not a sqlc setting: the monorepo question becomes a service design question, and the read replica article and the sharding article cover related shapes. If you reach for this, each service gets its own database and its own sqlc.yaml, and the shared folder disappears.

A heuristic: if you find yourself writing conversion functions between two generated Customer types in every handler, the table is shared state and option 2 fits. If the services barely touch each other's rows, option 1 stays simple.

The migrations question

The lab used schema files. A real system has migrations, and the question is whose folder they live in. Three patterns, in increasing order of coordination:

• One migrations folder for the whole database, owned by a platform team or by convention. sqlc entries point at it, as in the migrations article, and every service sees every table. This is the simplest and gives up the table boundaries above, unless each entry lists only some files, which is awkward when files mix tables.
• A migrations folder per service plus a shared one, applied in a fixed order (shared first). The lists in schema mirror that order, and each service's catalogue is built from the shared files followed by its own. This is the layout the lab's schema lists describe, and it keeps ownership clear. The ordering rule has to be written down, since a service's migration may depend on a shared table.
• Separate databases per service, each with its own folder and its own sqlc entry. No sharing to manage, at the cost of the cross service joins you can no longer write.

Whichever you pick, the rule from the migrations article holds: never edit a migration that has been applied, and commit the generated code with the migration that caused it.

Repository structure and Go modules

Two layouts work for the Go side.

One module at the root. The generated packages are ordinary packages (example.com/repo/accountsdb), one go.mod, one set of dependencies. It is the least work, and it means every service shares dependency versions. For a monorepo where the services deploy together, that is a feature.

One module per service, joined with a go.work file for local development. Each service has its own sqlc.yaml, its own generated code and its own pgx version. It gives you independent upgrades and costs a configuration file per service and a CI job that runs for each. The single config in the lab turns into several, each with one entry. A shared package then becomes a module of its own that services import by version.

In both, put generated code in a directory that a reader recognises (internal/db under each service is a common choice) and mark it as generated in your code review tool, so diffs in it fold away.

CI for many services

A monorepo CI job should run only the checks a change can affect. A change under accounts/ needs the accounts entry regenerated and diffed; a change under shared/ needs both.

• Run sqlc diff and sqlc vet at the root. They check every entry in the file, which is fast, and one command covers all services. The vet and lint article shows the exit codes.
• Scope by path when the repository grows. Trigger the job on changes to sqlc.yaml, shared/ and the service folder, and skip it otherwise.
• Test each service against its own schema. The testing article builds a schema per test, and each service can run the same helper with its own schema files.
• Fail on a shared change that breaks a service. Because a shared schema change regenerates every package that lists it, the compiler flags every call site that needs to change. Run the build for all services on a change to shared/, so the breakage shows in the pull request that caused it.

A change that crosses services

The test of a layout is what happens when the shared table changes. Suppose a migration adds a nullable phone column to customers. With the layout above, the next sqlc generate rewrites both accountsdb/models.go and billingdb/models.go: each Customer struct gains a Phone field, and every query that returns customers.* in either package gains it too. Queries that list their columns are untouched. The review for that pull request shows two generated diffs and no hand written code, which is the signal that nothing else needed to change.

Now suppose the migration instead renames email to address. Generation fails in both services at once, at every query that names email, with the file and line. Nobody has to remember which services read the column. That is the case where listing the shared schema in each entry earns its keep: the failure is in the pull request, not in whichever service deploys last.

Contrast the duplicated schema approach, with a hand copied customers table in each service's folder. The same rename succeeds in one copy and is forgotten in the other, and the second service finds out in production.

Pitfalls

• Entries that list a schema file twice or in the wrong order. A foreign key to a table defined later fails with "relation does not exist". List shared files first.
• Two entries writing to the same out directory. The files overwrite each other, and one service's models silently replace the other's. Give each entry its own directory and package name.
• Package names that collide in imports. db in every service forces import aliases. Use names that say the owner (accountsdb).
• Assuming the compiler enforces runtime isolation. The services share a database user in the lab, so a service can still run any SQL by hand. Separate roles with grants per schema are what enforce it at the database. sqlc enforces which queries your code can call, which is a useful but different guarantee.
• Forgetting go mod tidy after the first generate in a clean checkout.

Checklist

• One config, one entry per service, with schema listing the shared files first and the service's own after.
• A distinct out directory and package name per entry.
• Decide, once, how shared tables are generated: duplicated per service, or once into a platform package.
• Keep migrations in a fixed order with shared ones first, and commit generated code with the migration.
• Run sqlc diff, sqlc vet and a build of every service on any change to shared/.
• Use database roles when you need isolation that the compiler cannot give.

The multiple entry configuration is described in sqlc's configuration reference, and the module layouts in the Go documentation for workspaces and multi-module repositories.