Go generics CRUD repository pattern for a clean Go data layer

Why CRUD repositories get messy in Go

CRUD repositories start out simple. You write GetUser, then ListUsers, then the same for Orders, then Invoices. A few entities later, the data layer turns into a pile of near-copies where tiny differences are easy to miss.

What repeats most often isn’t the SQL itself. It’s the surrounding flow: running the query, scanning rows, handling “not found”, mapping database errors, applying pagination defaults, and converting inputs to the right types.

The usual hotspots are familiar: duplicated Scan code, repeated context.Context and transaction patterns, boilerplate LIMIT/OFFSET handling (sometimes with total counts), the same “0 rows means not found” check, and copy-pasted INSERT ... RETURNING id variations.

Once the repetition hurts enough, many teams reach for reflection. It promises “write it once” CRUD: take any struct and fill it from columns at runtime. The cost shows up later. Reflection-heavy code is harder to read, IDE support gets worse, and failures move from compile time to runtime. Small changes, like renaming a field or adding a nullable column, become surprises that only show up in tests or production.

Type-safe reuse means sharing the CRUD flow without giving up the everyday comforts of Go: clear signatures, compiler-checked types, and autocomplete that actually helps. With generics, you can reuse operations like Get[T] and List[T] while still requiring each entity to provide the parts that can’t be guessed, like how to scan a row into T.

This pattern is deliberately about the data access layer. It keeps SQL and mapping consistent and boring. It doesn’t try to model your domain, enforce business rules, or replace service-level logic.

Design goals (and what this won’t try to solve)

A good repository pattern makes everyday database access predictable. You should be able to read a repository and quickly see what it does, what SQL it runs, and what errors it can return.

The goals are simple:

• Type safety end to end (IDs, entities, and results aren’t any)
• Constraints that explain intent without type gymnastics
• Less boilerplate without hiding important behavior
• Consistent behavior across List/Get/Create/Update/Delete

The non-goals matter just as much. This isn’t an ORM. It shouldn’t guess field mappings, auto-join tables, or silently change queries. “Magic mapping” pushes you right back into reflection, tags, and edge cases.

Assume a normal SQL workflow: explicit SQL (or a thin query builder), clear transaction boundaries, and errors you can reason about. When something fails, the error should tell you “not found”, “conflict/constraint violation”, or “DB unavailable”, not a vague “repository error”.

The key decision is what becomes generic versus what stays per entity.

• Generic: the flow (run query, scan, return typed values, translate common errors).
• Per entity: the meaning (table names, selected columns, joins, and SQL strings).

Trying to force all entities into one universal filter system usually makes the code harder to read than writing two clear queries.

Choosing the entity and ID constraints

Most CRUD code repeats because every table has the same basic moves, but every entity has its own fields. With generics, the trick is to share a small shape and keep everything else free.

Start by deciding what the repository truly must know about an entity. For many teams, the only universal piece is the ID. Timestamps can be useful, but they aren’t universal, and forcing them into every type often makes the model feel fake.

Pick an ID type you can live with

Your ID type should match how you identify rows in the database. Some projects use int64, others use UUID strings. If you want one approach that works across services, make the ID generic. If your whole codebase uses one ID type, keeping it fixed can shorten signatures.

A good default constraint for IDs is comparable, since you’ll compare IDs, use them as map keys, and pass them around.

type ID interface {
	comparable
}

type Entity[IDT ID] interface {
	GetID() IDT
	SetID(IDT)
}

Keep entity constraints minimal

Avoid requiring fields via struct embedding or type-set tricks like ~struct{...}. They look powerful, but they couple your domain types to your repository pattern.

Instead, require only what the shared CRUD flow needs:

• Get and set the ID (so Create can return it, and Update/Delete can target it)

If you later add features like soft deletes or optimistic locking, add small opt-in interfaces (for example, GetVersion/SetVersion) and use them only where needed. Small interfaces tend to age well.

A generic repository interface that stays readable

A repository interface should describe what your app needs, not what the database happens to do. If the interface feels like SQL, it leaks details everywhere.

Keep the method set small and predictable. Put context.Context first, then the primary input (ID or data), then optional knobs bundled into a struct.

type Repository[T any, ID comparable, CreateIn any, UpdateIn any, ListQ any] interface {
	Get(ctx context.Context, id ID) (T, error)
	List(ctx context.Context, q ListQ) ([]T, error)
	Create(ctx context.Context, in CreateIn) (T, error)
	Update(ctx context.Context, id ID, in UpdateIn) (T, error)
	Delete(ctx context.Context, id ID) error
}

For List, avoid forcing a universal filter type. Filters are where entities differ most. A practical approach is per-entity query types plus a small shared pagination shape you can embed.

type Page struct {
	Limit  int
	Offset int
}

Error handling is where repositories often get noisy. Decide upfront which errors callers are allowed to branch on. A simple set usually works:

• ErrNotFound when an ID doesn’t exist
• ErrConflict for unique violations or version clashes
• ErrValidation when input is invalid (only if the repo validates)

Everything else can be a wrapped low-level error (DB/network). With that contract, service code can handle “not found” or “conflict” without caring whether storage is PostgreSQL today or something else later.

How to avoid reflection while still reusing the flow

Reflection usually sneaks in when you want one piece of code to “fill any struct”. That hides errors until runtime and makes the rules unclear.

A cleaner approach is to reuse only the boring parts: execute queries, loop rows, check affected counts, and wrap errors consistently. Keep mapping to and from structs explicit.

Split responsibilities: SQL, mapping, shared flow

A practical split looks like this:

• Per entity: keep the SQL strings and parameter order in one place
• Per entity: write small mapping functions that scan rows into the concrete struct
• Generic: provide the shared flow that executes a query and calls the mapper

That way, generics reduce repetition without hiding what the database is doing.

Here’s a tiny abstraction that lets you pass either *sql.DB or *sql.Tx without the rest of the code caring:

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

What generics should (and shouldn’t) do

The generic layer shouldn’t try to “understand” your struct. Instead, it should accept explicit functions you provide, such as:

• a binder that turns inputs into query arguments
• a scanner that reads columns into an entity

For example, a Customer repository can store SQL as constants (selectByID, insert, update) and implement scanCustomer(rows) once. A generic List can handle the loop, context, and error wrapping, while scanCustomer keeps the mapping type-safe and obvious.

If you add a column, you update the SQL and the scanner. The compiler helps you find what broke.

Step by step: implementing the pattern

The goal is one reusable flow for List/Get/Create/Update/Delete while keeping each repository honest about its SQL and row mapping.

1) Define the core types

Start with the fewest constraints you can. Pick an ID type that works for your codebase and a repository interface that stays predictable.

type ID interface{ ~int64 | ~string }

type Repo[E any, K ID] interface {
	Get(ctx context.Context, id K) (E, error)
	List(ctx context.Context, limit, offset int) ([]E, error)
	Create(ctx context.Context, e *E) error
	Update(ctx context.Context, e *E) error
	Delete(ctx context.Context, id K) error
}

2) Add an executor for DB and transactions

Don’t tie generic code directly to *sql.DB or *sql.Tx. Depend on a small executor interface that matches what you call (QueryContext, ExecContext, QueryRowContext). Then services can pass a DB or a transaction without changing repository code.

3) Build a generic base with shared flow

Create a baseRepo[E,K] that stores the executor and a few function fields. The base handles the boring parts: calling the query, mapping “not found”, checking affected rows, and returning consistent errors.

4) Implement the entity-specific pieces

Each entity repository provides what can’t be generic:

• SQL for list/get/create/update/delete
• a scan(row) function that converts a row into E
• a bind(...) function that returns query args

5) Wire concrete repos and use them from services

Build NewCustomerRepo(exec Executor) *CustomerRepo that embeds or wraps baseRepo. Your service layer depends on the Repo[E,K] interface and decides when to start a transaction; the repository just uses the executor it was given.

Handling List/Get/Create/Update/Delete without surprises

A generic repository only helps if every method behaves the same way everywhere. Most pain comes from small inconsistencies: one repo orders by created_at, another by id; one returns nil, nil for missing rows, another returns an error.

List: pagination and ordering that doesn’t shift

Pick one pagination style and apply it consistently. Offset pagination (limit/offset) is simple and works well for admin screens. Cursor pagination is better for endless scrolling, but it needs a stable sort key.

Whatever you choose, make ordering explicit and stable. Ordering by a unique column (often the primary key) prevents items from jumping between pages when new rows appear.

Get: a clear “not found” signal

Get(ctx, id) should return a typed entity and a clear missing-record signal, usually a shared sentinel error like ErrNotFound. Avoid returning a zero-value entity with a nil error. Callers can’t tell “missing” from “empty fields”.

Get into this habit early: the type is for data, the error is for state.

Before you implement methods, make a few decisions and keep them consistent:

• Create: do you accept an input type (no ID, no timestamps) or a full entity? Many teams prefer Create(ctx, in CreateX) to prevent callers from setting server-owned fields.
• Update: is it a full replace or a patch? If it’s a patch, don’t use plain structs where zero values are ambiguous. Use pointers, nullable types, or an explicit field mask.
• Delete: hard delete or soft delete? If it’s soft delete, decide whether Get hides deleted rows by default.

Also decide what write methods return. Low-surprise options are returning the updated entity (after DB defaults) or returning only the ID plus ErrNotFound when nothing was changed.

Testing strategy for generic and entity-specific parts

This approach only pays off if it’s easy to trust. Split tests along the same line as the code: test shared helpers once, then test each entity’s SQL and scanning separately.

Treat shared pieces as small pure functions whenever you can, like pagination validation, mapping sort keys to allowed columns, or building WHERE fragments. These can be covered with fast unit tests.

For list queries, table-driven tests work well because edge cases are the whole problem. Cover things like empty filters, unknown sort keys, limit 0, limit over max, negative offset, and “next page” boundaries where you fetch one extra row.

Keep per-entity tests focused on what’s truly entity-specific: the SQL you expect to run and how rows scan into the entity type. Use a SQL mock or a lightweight test database and make sure scan logic handles nulls, optional columns, and type conversions.

If your pattern supports transactions, test commit/rollback behavior with a tiny fake executor that records calls and simulates errors:

• Begin returns a tx-scoped executor
• on error, rollback is called exactly once
• on success, commit is called exactly once
• if commit fails, the error is returned unchanged

You can also add small “contract tests” that every repository must pass: create then get returns the same data, update changes the intended fields, delete makes get return not found, and list returns stable ordering under the same inputs.

Common mistakes and traps

Generics make it tempting to build one repository to rule them all. Data access is full of small differences, and those differences matter.

A few traps show up often:

• Over-generalizing until every method takes a giant bag of options (joins, search, permissions, soft deletes, caching). At that point, you’ve built a second ORM.
• Constraints that are too clever. If readers need to decode type sets to understand what an entity must implement, the abstraction costs more than it saves.
• Treating input types as the DB model. When Create and Update take the same struct you scan from rows, DB details leak into handlers and tests, and schema changes ripple through the app.
• Silent behavior in List: unstable sorting, inconsistent defaults, or paging rules that vary by entity.
• Not-found handling that forces callers to parse error strings instead of using errors.Is.

A concrete example: ListCustomers returns customers in a different order each time because the repository doesn’t set an ORDER BY. Pagination then duplicates or skips records between requests. Make ordering explicit (even if it’s just by primary key) and keep defaults consistent.

Quick checklist before you adopt this

Before you roll a generic repository into every package, make sure it will remove repetition without hiding important database behavior.

Start with consistency. If one repo takes context.Context and another doesn’t, or one returns (T, error) while another returns (*T, error), the pain shows up everywhere: services, tests, and mocks.

Make sure each entity still has one obvious home for its SQL. Generics should reuse the flow (scan, validate, map errors), not scatter queries across string fragments.

A quick set of checks that prevents most surprises:

• One signature convention for List/Get/Create/Update/Delete
• One predictable not-found rule used by every repo
• Stable list ordering that’s documented and tested
• A clean way to run the same code on *sql.DB and *sql.Tx (via an executor interface)
• A clear boundary between generic code and entity rules (validation and business checks stay outside the generic layer)

If you’re building internal tools quickly in AppMaster and later exporting or extending the generated Go code, these checks help keep the data layer predictable and easy to test.

A realistic example: building a Customer repository

Here’s a small Customer repository shape that stays type-safe without getting clever.

Start with a stored model. Keep the ID strongly typed so you can’t mix it with other IDs by accident:

type CustomerID int64

type Customer struct {
	ID     CustomerID
	Name   string
	Status string // "active", "blocked", "trial"...
}

Now split “what the API accepts” from “what you store”. This is where Create and Update should differ.

type CreateCustomerInput struct {
	Name   string
	Status string
}

type UpdateCustomerInput struct {
	Name   *string
	Status *string
}

Your generic base can handle the shared flow (execute SQL, scan, map errors), while the Customer repo owns the Customer-specific SQL and mapping. From the service layer’s point of view, the interface stays clean:

type CustomerRepo interface {
	Create(ctx context.Context, in CreateCustomerInput) (Customer, error)
	Update(ctx context.Context, id CustomerID, in UpdateCustomerInput) (Customer, error)
	Get(ctx context.Context, id CustomerID) (Customer, error)
	Delete(ctx context.Context, id CustomerID) error
	List(ctx context.Context, q CustomerListQuery) ([]Customer, int, error)
}

For List, treat filters and pagination as a first-class request object. It keeps call sites readable and makes it harder to forget limits.

type CustomerListQuery struct {
	Status *string // filter
	Search *string // name contains
	Limit  int
	Offset int
}

From there, the pattern scales well: copy the structure for the next entity, keep inputs separate from stored models, and keep scanning explicit so changes stay obvious and compiler-friendly.