Designing a Clean Persistence Layer in Go with Ports, Pools, and Migrations

Most enterprise applications are built around data. They receive requests, validate decisions, store records, search history, and produce reports. Because data is so central, the database can quietly become the real designer of the system. Table names leak into business code. Storage-specific rules shape service methods. A change in the database engine becomes a change in every layer.

A better persistence design treats the database as an important dependency, not as the owner of the business model. In Go, this works especially well with small interfaces, explicit wiring, and the standard database/sql package. The goal is not to ignore the database. The goal is to give it a clear job: persist and retrieve data while the domain keeps control of business meaning.

This post builds a practical persistence approach for a dispatch service. The service manages jobs assigned to vehicles and routes. The same principles apply to payments, licenses, customer profiles, bookings, or any system where data must survive beyond one request.

The Problem

A dispatch service has several actors and responsibilities:

• API handlers receive client requests.
• Business services decide whether a job can be created, updated, or listed.
• A repository saves and loads jobs.
• A database stores durable records.
• Migration tooling evolves the schema safely across environments.

The input is usually a command such as "create this dispatch job" or "list jobs in this state". The output is either a saved domain object, a list of records, or an error. The difficult part is not writing one insert statement. The difficult part is keeping the following concerns separate over years of change:

• Business rules, such as whether a vehicle may receive another job.
• Persistence rules, such as how rows are inserted or updated.
• Database operations, such as connection pooling and transactions.
• Schema evolution, such as adding columns or indexes safely.
• Performance decisions, such as whether the data model supports real query patterns.

The architecture should allow the service to use MySQL today without forcing every layer to know MySQL details.

The Architecture: Domain Inside, Database Outside

A clean persistence design follows the ports and adapters idea. A port is an interface that describes what the application needs. An adapter is an implementation that talks to a real external tool, such as MySQL.

HTTP request
  |
  v
HTTP adapter
  |
  v
Application service
  |
  v
Persistence port interface
  |
  v
MySQL repository adapter
  |
  v
Database

The direction matters. The domain and application service depend on a small interface. They do not depend on sql.DB, a driver, a table name, or a query string. The MySQL repository depends on the domain model and converts between rows and Go values.

That single boundary gives you several benefits:

• You can unit test services with an in-memory fake repository.
• You can replace MySQL with another store by changing the adapter.
• You can keep business rules readable instead of hiding them in SQL or triggers.
• You can keep database tuning in the infrastructure layer.

Define the Domain Model and Port

Start with a domain model that represents business meaning. This struct should not contain ORM tags, table names, or persistence-specific fields. It is a Go representation of the thing your system reasons about.

package domain

import (
    "context"
    "time"
)

type DispatchJob struct {
    ID          string
    VehicleCode string
    RouteCode   string
    StartsAt    time.Time
    State       string
}

type DispatchJobStore interface {
    Save(ctx context.Context, job DispatchJob) (created bool, err error)
    FindByID(ctx context.Context, id string) (DispatchJob, error)
    ListByState(ctx context.Context, state string) ([]DispatchJob, error)
}

The interface does not mention MySQL, SQL, transactions, tables, or indexes. It says what the application needs: save a job, load one job, and list jobs by state.

Notice the return value of Save. Returning created bool lets the caller distinguish a new record from an update. An HTTP adapter could use that distinction to return different response codes. A business service could use it to trigger different follow-up behavior. The important point is that the caller receives a domain-friendly result, not a database-specific detail.

Implement a MySQL Repository Adapter

The repository adapter is where database details belong. It receives a *sql.DB, executes statements, scans rows, and maps data back into domain structs.

Add the MySQL driver to the module:

go get github.com/go-sql-driver/mysql

Then create the adapter. The exact package layout can vary, but the dependency direction should stay the same: infrastructure imports the domain, not the other way around.

package mysqlrepo

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

    "example.com/dispatch/internal/domain"
)

type DispatchJobRepository struct {
    db *sql.DB
}

func NewDispatchJobRepository(db *sql.DB) *DispatchJobRepository {
    return &DispatchJobRepository{db: db}
}

func (r *DispatchJobRepository) FindByID(ctx context.Context, id string) (domain.DispatchJob, error) {
    const stmt = `
        SELECT id, vehicle_code, route_code, starts_at, state
        FROM dispatch_jobs
        WHERE id = ?`

    var job domain.DispatchJob
    err := r.db.QueryRowContext(ctx, stmt, id).Scan(
        &job.ID,
        &job.VehicleCode,
        &job.RouteCode,
        &job.StartsAt,
        &job.State,
    )
    if errors.Is(err, sql.ErrNoRows) {
        return domain.DispatchJob{}, err
    }
    if err != nil {
        return domain.DispatchJob{}, err
    }
    return job, nil
}

The repository does not decide whether the job is valid. It does not decide whether the vehicle may be assigned. It only translates between database rows and domain values.

Saving with an Upsert

For a simple save operation, a MySQL upsert can insert a row when the ID is new and update it when the ID already exists. This is a single data-changing statement, so the database treats it atomically.

func (r *DispatchJobRepository) Save(ctx context.Context, job domain.DispatchJob) (bool, error) {
    const stmt = `
        INSERT INTO dispatch_jobs (id, vehicle_code, route_code, starts_at, state)
        VALUES (?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            vehicle_code = VALUES(vehicle_code),
            route_code = VALUES(route_code),
            starts_at = VALUES(starts_at),
            state = VALUES(state)`

    result, err := r.db.ExecContext(
        ctx,
        stmt,
        job.ID,
        job.VehicleCode,
        job.RouteCode,
        job.StartsAt,
        job.State,
    )
    if err != nil {
        return false, err
    }

    affected, err := result.RowsAffected()
    if err != nil {
        return false, err
    }

    return affected == 1, nil
}

In MySQL, this style of statement reports one affected row for an insert and two for an update in the common case. The repository converts that behavior into a simple boolean. The rest of the application does not need to know how the database reports upserts.

Use Transactions Only When the Operation Needs Them

A transaction groups multiple data-changing operations so they succeed together or fail together. The acronym ACID describes the expected behavior: atomicity, consistency, isolation, and durability. In practice, a transaction protects you from half-applied changes.

Use a transaction when one business operation writes to multiple places. For example, creating a dispatch job and marking the vehicle as busy should not leave the system in a state where the job exists but the vehicle is still marked available.

A single upsert statement does not need an explicit transaction wrapper. Multiple inserts, updates, or deletes that form one business decision usually do.

func (r *DispatchJobRepository) CreateAndReserveVehicle(ctx context.Context, job domain.DispatchJob) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    const insertJob = `
        INSERT INTO dispatch_jobs (id, vehicle_code, route_code, starts_at, state)
        VALUES (?, ?, ?, ?, ?)`

    if _, err := tx.ExecContext(ctx, insertJob, job.ID, job.VehicleCode, job.RouteCode, job.StartsAt, job.State); err != nil {
        return err
    }

    const reserveVehicle = `
        UPDATE vehicles
        SET state = 'RESERVED'
        WHERE code = ?`

    if _, err := tx.ExecContext(ctx, reserveVehicle, job.VehicleCode); err != nil {
        return err
    }

    return tx.Commit()
}

The defer tx.Rollback() line is a safety net. If any operation returns early, the transaction is rolled back. If Commit succeeds, the deferred rollback has no practical effect. This pattern keeps error paths simple and makes failed writes predictable.

Choose the Database by Data Shape, Not Habit

A common mistake is choosing one familiar database for every problem. That is comfortable at first, but it leads to weak designs when the data has a different shape.

For many enterprise systems, a relational database is still the safest starting point for core business records because it gives stable schema, transactions, constraints, and expressive queries. That does not mean every piece of data belongs there. You might keep dispatch jobs in MySQL as the source of truth, store current vehicle positions in a key/value store, and index selected data somewhere else for search.

The rule is simple: choose based on how the data is written, read, related, and changed.

Keep Business Rules in Go, Use the Database for Invariants

Business logic is the set of rules that describes what the system means. Examples include:

• A vehicle cannot receive overlapping dispatch jobs.
• A vehicle may have a maximum number of active jobs per day.
• A completed job cannot move back to a draft state without a special workflow.

These rules are easier to test, review, and evolve when they live in Go application or domain code. Keeping them in Go also avoids tying the business model to one database dialect.

The database should still protect important invariants. An invariant is a rule that must stay true even if a bug reaches the database layer. Good database-level protections include:

• Primary keys for identity.
• Foreign keys for required relationships.
• Unique constraints for values that must not repeat.
• Indexes that support expected access patterns.

Use the application for business decisions. Use the database for integrity guarantees and efficient storage.

Wire Persistence in the Composition Root

The composition root is the place where concrete dependencies are created and connected. In a Go service, main.go is the natural place for this. It can load configuration, create the database pool, configure pooling rules, build the repository, and pass only the needed interface into the server.

package main

import (
    "database/sql"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql"

    "example.com/dispatch/internal/httpserver"
    "example.com/dispatch/internal/mysqlrepo"
)

func configurePool(db *sql.DB, maxOpen int, maxIdle int, maxLifetime time.Duration, maxIdleTime time.Duration) {
    db.SetMaxOpenConns(maxOpen)
    db.SetMaxIdleConns(maxIdle)
    db.SetConnMaxLifetime(maxLifetime)
    db.SetConnMaxIdleTime(maxIdleTime)
}

func main() {
    dsn := loadDatabaseDSN()

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    configurePool(db, 20, 10, 30*time.Minute, 5*time.Minute)

    jobStore := mysqlrepo.NewDispatchJobRepository(db)

    if err := httpserver.Run(loadServerSettings(), jobStore); err != nil {
        log.Fatal(err)
    }
}

The server should receive the port it needs, not the database credentials and not the driver setup.

package httpserver

import "example.com/dispatch/internal/domain"

func Run(settings ServerSettings, store domain.DispatchJobStore) error {
    // Register routes and handlers that call the store interface.
    return nil
}

This gives you clean dependency flow:

• main.go knows about infrastructure.
• The HTTP layer knows about the persistence port.
• The domain does not know about MySQL.
• Tests can pass a fake implementation of DispatchJobStore.

Configure sql.DB as a Pool, Not as One Connection

A frequent misunderstanding in Go is thinking that *sql.DB is one database connection. It is not. It is a concurrency-safe handle that manages a pool of connections. Calls such as Query, Exec, and Begin borrow a connection from the pool and return it after use.

This matters because Go services often run many goroutines. Without a controlled pool, the service can pressure the database with too many active sessions. A connection pool gives backpressure: when all allowed connections are busy, new database work waits instead of opening unlimited connections.

The main settings are:

• SetMaxOpenConns: caps total open connections. Too low can increase request latency. Too high can overload the database.
• SetMaxIdleConns: keeps a number of idle connections ready for traffic bursts. Too low can cause repeated reconnects.
• SetConnMaxIdleTime: closes connections that have been idle too long. This helps when firewalls or load balancers drop inactive connections.
• SetConnMaxLifetime: recycles long-lived connections. This is useful when databases sit behind proxies, when routing changes, or when long-lived sessions accumulate hidden state.

Pool values should be chosen with the database limit, expected request concurrency, and query duration in mind. They are not random tuning knobs. They are part of production stability.

Decide Carefully Between Direct SQL and ORMs

An ORM, or Object-Relational Mapper, maps database tables to Go structs and generates SQL for common operations. Examples in Go include GORM, Ent, and SQLBoiler. ORMs can help teams move faster when the model is simple and the team is not confident with SQL.

The tradeoff is that an ORM becomes part of the architecture. Its assumptions shape the way data is modeled, queried, and evolved.

A clean approach is to keep two separate models when using an ORM:

package domain

type DispatchJob struct {
    ID          string
    VehicleCode string
    RouteCode   string
    State       string
}

package mysqlrepo

type DispatchJobRecord struct {
    ID          string
    VehicleCode string
    RouteCode   string
    State       string
}

func toDomain(row DispatchJobRecord) domain.DispatchJob {
    return domain.DispatchJob{
        ID:          row.ID,
        VehicleCode: row.VehicleCode,
        RouteCode:   row.RouteCode,
        State:       row.State,
    }
}

The domain struct stays pure. The record struct belongs to the adapter. If you use ORM tags, table names, primary key hints, or index hints, put them on the adapter struct, not on the domain struct.

Use an ORM when:

• The data model is simple.
• Most operations are basic create, read, update, and delete actions.
• Development speed matters more than exact query control.
• The team benefits from a higher-level API.

Prefer direct SQL when:

• Query performance matters heavily.
• The system has advanced reporting or filtering.
• The team needs full control over joins, indexes, and execution plans.
• Generated queries become hard to understand under load.

A mixed approach is also valid. Use an ORM for ordinary CRUD paths and hand-written SQL for the few performance-critical reports or queries. The risk is synchronization: schema changes must update both the ORM model and the custom SQL.

Treat Migrations Like Application Code

A schema changes over time. New fields appear. Indexes are added. Tables split or merge. If database changes happen manually, development, staging, and production drift apart. The application may work on one environment and fail in another.

Schema migrations solve this by making database changes versioned, reviewed, repeatable, and automated.

Tools such as Liquibase and Flyway apply migration files in order and track which changes have already run. A migration should live in version control beside the application code.

Example first migration:

-- V001__create_dispatch_jobs.sql
CREATE TABLE dispatch_jobs (
    id VARCHAR(64) PRIMARY KEY,
    vehicle_code VARCHAR(64) NOT NULL,
    route_code VARCHAR(64) NOT NULL,
    starts_at TIMESTAMP NULL,
    state VARCHAR(32) NOT NULL DEFAULT 'WAITING'
);

Later, the business needs to know when a job finished. Do not edit the old file. Add a new migration.

-- V002__add_finished_at_to_dispatch_jobs.sql
ALTER TABLE dispatch_jobs
ADD COLUMN finished_at TIMESTAMP NULL;

If common lookups filter by vehicle and state, add a new migration for the access pattern:

-- V003__index_dispatch_jobs_vehicle_state.sql
CREATE INDEX idx_dispatch_jobs_vehicle_state
ON dispatch_jobs (vehicle_code, state);

Good migration habits:

• Use a migration tool for every schema change.
• Review migration files like Go code.
• Keep old migrations immutable.
• Add rollback scripts where the tool and release process support them.
• Run migrations automatically in the deployment pipeline.
• Use clear names such as V001__..., V002__..., and V003__....

Automatic schema updates from an ORM may seem convenient, but they are risky for enterprise systems. A failed or missing column change can stop a service at startup and make rollback harder. Controlled migration files give the team history, repeatability, and auditability.

Performance Starts with Evidence and Design

Database performance work should begin with evidence. Do not optimize a query just because it looks suspicious. The bottleneck may be network latency, connection pool starvation, missing backpressure, or a poor data model.

A practical performance workflow:

1. Identify where time is spent before changing anything.
2. Check whether the data model matches the access pattern.
3. Confirm whether indexes are used by the query plan.
4. Measure read and write impact after adding an index.
5. Revisit normalization when joins dominate hot paths.
6. Use denormalization only when the read pattern justifies the extra write complexity.

Indexes are not free. They can speed reads, but they also slow inserts and updates because the index must be maintained. Unique indexes can be excellent when a column truly must be unique, but they also add write cost. Multi-column indexes can help when queries consistently filter by several columns with AND; placing the highest-cardinality column first is often an important design choice.

Bitmap indexes can be powerful in the right database and workload, but they can hurt write-heavy systems. Treat them as a specialized tool, not a default choice.

The deeper lesson is that performance problems are often design problems. A perfectly normalized model can still be wrong for a high-volume access path. A less elegant schema can be the right production design if it matches how the system is actually used.

Testing Without a Database

A clean port lets you test business behavior without starting MySQL. The fake repository below stores jobs in memory. It is not a replacement for integration tests against a real database, but it is excellent for fast unit tests around application rules.

package dispatch_test

import (
    "context"
    "testing"

    "example.com/dispatch/internal/domain"
)

type fakeJobStore struct {
    jobs map[string]domain.DispatchJob
}

func newFakeJobStore() *fakeJobStore {
    return &fakeJobStore{jobs: make(map[string]domain.DispatchJob)}
}

func (f *fakeJobStore) Save(ctx context.Context, job domain.DispatchJob) (bool, error) {
    _, existed := f.jobs[job.ID]
    f.jobs[job.ID] = job
    return !existed, nil
}

func (f *fakeJobStore) FindByID(ctx context.Context, id string) (domain.DispatchJob, error) {
    return f.jobs[id], nil
}

func (f *fakeJobStore) ListByState(ctx context.Context, state string) ([]domain.DispatchJob, error) {
    result := make([]domain.DispatchJob, 0)
    for _, job := range f.jobs {
        if job.State == state {
            result = append(result, job)
        }
    }
    return result, nil
}

func TestListByStateUsesStoreContract(t *testing.T) {
    ctx := context.Background()
    store := newFakeJobStore()

    _, _ = store.Save(ctx, domain.DispatchJob{ID: "job-1", State: "WAITING"})
    _, _ = store.Save(ctx, domain.DispatchJob{ID: "job-2", State: "DONE"})
    _, _ = store.Save(ctx, domain.DispatchJob{ID: "job-3", State: "WAITING"})

    jobs, err := store.ListByState(ctx, "WAITING")
    if err != nil {
        t.Fatalf("list jobs: %v", err)
    }
    if len(jobs) != 2 {
        t.Fatalf("expected 2 waiting jobs, got %d", len(jobs))
    }
}

This test is fast because it does not cross a network boundary. It verifies the application-facing contract. You should still add integration tests for the MySQL adapter itself, because only a real database can verify SQL syntax, driver behavior, constraints, migrations, and transaction behavior.

Common Mistakes to Avoid

Letting table design become domain design

Tables are storage structures. Domain models are business structures. They often overlap, but they should not be treated as the same thing. Keep the database model behind a repository boundary.

Putting ORM tags on domain structs

Tags that describe persistence belong to infrastructure. Once the domain imports storage concerns, the core becomes harder to test and harder to replace.

Using transactions everywhere

Transactions are required for multi-step data changes that must succeed or fail as a group. Wrapping every single statement in an explicit transaction adds complexity without always adding value.

Ignoring the connection pool

A service with many goroutines can overload the database even when each query is fast. Configure the pool deliberately and treat it as a production safety mechanism.

Trusting generated queries blindly

ORM-generated SQL can be convenient, but it can also hide N+1 patterns, unnecessary joins, or missing indexes. When traffic grows, inspect what actually runs.

Editing old migration files

Old migrations are history. Changing them after they have run in another environment creates drift. Add a new migration instead.

Adding indexes without considering writes

Indexes improve selected reads, but they cost writes. Add them for real access patterns, not because they seem generally useful.

Implementation Checklist

Use this checklist when building or reviewing a Go persistence layer:

• The domain defines small persistence interfaces based on business needs.
• The repository adapter implements the interface and hides SQL details.
• Business rules live in Go services or domain code.
• The database enforces identity, relationships, uniqueness, and useful indexes.
• main.go wires the database, pool, repository, and server.
• *sql.DB is configured with open, idle, idle-time, and lifetime limits.
• Transactions are used for multi-statement business operations.
• ORM-specific structs are separate from domain structs.
• Migrations are versioned, reviewed, immutable, and automated.
• Query performance is investigated with evidence before changes are made.
• Indexes are added for known access patterns and reviewed for write cost.
• Tests can replace the real repository with a fake implementation.

Conclusion

A strong persistence layer is not just a collection of queries. It is an architectural boundary. Go makes that boundary clear with small interfaces, explicit dependency wiring, and the database/sql package.

Keep the domain focused on business language. Keep SQL, drivers, connection pools, ORM details, and migrations in the infrastructure layer. Choose databases by the shape of the data, not by habit. Use transactions when a business operation needs atomicity. Treat migrations as deployable code. Tune performance only after understanding the real bottleneck.

When the database serves the domain instead of designing it, the system stays easier to test, easier to change, and safer to operate under real enterprise load.