Skip to main content

Data Access

For developers with .NET (C#) experience, this guide explains how database/sql, sqlc, GORM, and ent map to EF Core and Dapper. In .NET, EF Core is often the standard choice. In Go, several schools coexist on top of the standard database/sql: writing SQL directly, sqlc (which generates code from SQL), and full-featured ORMs. The Go community tends to favor writing SQL explicitly and avoiding implicit behavior such as change tracking and lazy loading. This document lays out the thinking behind each approach and how to choose among them.

1. What This Document Covers​

  • database/sql and connection pooling
  • The SQL-first approach with sqlc
  • Criteria for choosing an ORM (GORM/ent)
  • Migration management and transactions

database/sql and connection pooling​

database/sql is a database-agnostic interface, roughly in the position of ADO.NET. To actually connect, you register a database-specific "driver" with a blank import (_).

  • The *sql.DB returned by sql.Open(driverName, dsn) is not a single connection but a connection pool. It is safe to share across goroutines, so create it once at application startup and reuse it.
  • sql.Open does not verify the connection. Check connectivity with db.PingContext(ctx).
  • The main APIs are as follows.
APIPurpose
db.QueryContextA SELECT that returns multiple rows. Returns *sql.Rows
db.QueryRowContextA SELECT that returns one row. Scan returns the error
db.ExecContextStatements that return no rows, such as INSERT / UPDATE / DELETE
db.BeginTxStarts a transaction
db.PrepareContextA prepared statement
  • Tune the pool with SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime, and SetConnMaxIdleTime.
  • Query placeholders differ by driver. PostgreSQL uses $1, $2; MySQL and SQLite use ?; SQL Server uses @p1 or @name.

The SQL-first approach with sqlc​

sqlc is a tool that generates type-safe Go code from the SQL you write. It is the opposite of an ORM: "SQL first, code second."

  • Write the schema (CREATE TABLE) and the queries (SELECT ...) in .sql files and run sqlc generate.
  • For each query, it generates structs, parameter types, and methods. If the SQL and schema don't match, generation fails, so type mismatches are caught before the build.
  • At run time, the generated code just calls database/sql (or pgx), with no reflection or run-time SQL generation.
  • It is weaker at dynamic query assembly (such as variable search conditions). For those spots, use database/sql directly or pair it with a query builder.

Criteria for choosing an ORM (GORM/ent)​

OptionCharacteristicsCompared with EF Core
database/sql directlyMost explicit. More boilerplateClose to ADO.NET
sqlxExtends database/sql. Simplifies Scan into structsClose to Dapper
sqlcGenerates type-safe code from SQLCloser to a typed Dapper than to EF Core
GORMA reflection-based ORM with AutoMigrate, relations, hooks, and moreClosest to EF Core
entDefines the schema in code and generates a type-safe API. Strong for graph-like dataGives type-safe queries similar to LINQ

Rules of thumb:

  • If you are comfortable writing SQL and want to manage exactly what each query does, sqlc is a strong candidate. Performance and behavior are easier to predict, and reviews are easier.
  • If you want to build CRUD-heavy features quickly and delegate relationship handling, consider GORM. You need to understand its implicit, reflection-driven behavior (default table names, soft deletes, zero-value updates, and so on) before using it.
  • If you value schema-driven design and type safety, ent is a candidate.
  • Whichever you choose, you will still write raw SQL for complex reporting queries and the like.

Migration management and transactions​

Nothing in database/sql generates migrations automatically from model differences the way EF Migrations does. You manage migrations with a separate tool.

ToolCharacteristics
golang-migrateApplies numbered up / down SQL files via a CLI or library
gooseMigrations written as SQL files or Go code, driven from a CLI
AtlasDeclarative migrations that compute diffs from a declared schema
  • The usual practice is to keep migrations as version-controlled SQL files and apply them from the deployment pipeline.
  • Start a transaction with db.BeginTx(ctx, nil) and finish it with tx.Commit() or tx.Rollback(). There is no implicit ambient transaction like TransactionScope; you pass tx around explicitly.

2. .NET Mapping​

.NET (C#)Go
ADO.NETdatabase/sql
Dappersqlx
EF CoreGORM / ent / sqlc (code generation)
EF Migrationsgolang-migrate / atlas / goose

Additional mappings that complement the table above:

.NET (C#)Go
DbConnection*sql.DB (pool) / *sql.Conn (single connection)
DbCommand.ExecuteNonQuerydb.ExecContext
DbCommand.ExecuteReaderdb.QueryContext (*sql.Rows)
DbDataReader.Read() / GetXxx()rows.Next() / rows.Scan(&...)
ExecuteScalardb.QueryRowContext(...).Scan(&v)
DbTransaction*sql.Tx
TransactionScopeNone (pass tx around explicitly)
DBNull / Nullable<T>sql.NullString and other sql.Null[T] types (Go 1.22 and later) / pointers
Max Pool Size in the connection stringdb.SetMaxOpenConns
CancellationTokencontext.Context (first argument of the ...Context methods)
DbContextNo direct equivalent (GORM's *gorm.DB and sqlc's *Queries come closest)
Change tracking (ChangeTracker)None (with GORM you call Save / Updates each time)
LINQ to Entitiesent's type-safe query API / GORM's method chaining / plain SQL with sqlc
Include (eager loading)GORM's Preload / ent's WithXxx / write a JOIN with sqlc
DbUpdateException (unique constraint violation, etc.)Check driver-specific error types (such as pgconn.PgError) with errors.As

3. Common Pitfalls for C# Developers​

sql.DB is a pool, not a connection​

A *sql.DB is not something to open and close per request. Create it once at startup and share it across the application. Call Close only at shutdown. Calling sql.Open and Close for every operation, in the spirit of using var conn = new SqlConnection(...), throws away the benefits of the pool and should be avoided.

Don't forget rows.Close() and rows.Err()​

The *sql.Rows returned by QueryContext must be closed when you finish, and defer rows.Close() is the standard idiom. When rows.Next() returns false, check with rows.Err() whether the loop ended normally or was interrupted by an error.

No rows is returned as an error (sql.ErrNoRows)​

When QueryRowContext(...).Scan(...) finds no row, it returns sql.ErrNoRows. It does not return null the way FirstOrDefault does. Check with errors.Is(err, sql.ErrNoRows) and convert "not found" into your own error (or a nil plus boolean).

NULL handling is clunky​

Columns that may be NULL cannot be scanned directly into string or int (it fails with an error). Use sql.NullString, sql.NullInt64, sql.Null[T] (Go 1.22 and later), or a pointer (*string).

Scan depends on column order and types​

SELECT * breaks when the column order changes, so name the columns explicitly. The number and order of Scan arguments must match the selected columns. A mismatch is a run-time error, not a compile-time one.

Use tx inside a transaction​

If you call db.ExecContext after tx, _ := db.BeginTx(...), that query runs outside the transaction. Always use tx.ExecContext. Also, writing defer tx.Rollback() first is safe: after a Commit, it just returns sql.ErrTxDone harmlessly, and an early return or a panic still rolls back.

Pass a context​

Use db.QueryContext(ctx, ...) rather than db.Query. By passing the request's ctx, the query can be interrupted when the client disconnects or the request times out.

Set limits on the connection pool​

By default, MaxOpenConns has no limit, and under load you can use up the database's connections. It is common to set a connection limit and SetConnMaxLifetime (shorter than any load balancer or DB-side timeout).

Watch out for implicit ORM behavior​

When you update with a struct, GORM skips Go zero values (0, empty string, false) from the update. When you want to set a value to 0, use a map or specify columns with Select. There is no change tracking as in EF Core; updates are explicit on each call.

There are no expression-tree-based queries like LINQ​

Go has no mechanism for building a query from a lambda such as Where(u => u.Age > 20). ORMs either express conditions with method chaining and strings (Where("age > ?", 20)) or provide a typed API generated from the schema. With sqlc, you write the SQL itself.

Migrations are managed separately​

Changing a model never generates a migration automatically. GORM's AutoMigrate is handy for development, but it does not do things like dropping columns and is not suited to production. In production, the usual practice is to use explicitly version-controlled migrations kept as SQL files.

4. Code Examples​

Example 1: The basics of database/sql (connect, query, scan)​

C# (Dapper):

await using var conn = new NpgsqlConnection(connectionString);

var user = await conn.QuerySingleOrDefaultAsync<User>(
    "SELECT id, name, email FROM users WHERE id = @id", new { id = 1 });

var users = await conn.QueryAsync<User>(
    "SELECT id, name, email FROM users WHERE age >= @min", new { min = 20 });

Go (database/sql):

package main

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

	_ "github.com/jackc/pgx/v5/stdlib" // registers the driver as "pgx"
)

type User struct {
	ID    int
	Name  string
	Email string
}

var ErrUserNotFound = errors.New("user not found")

func GetUser(ctx context.Context, db *sql.DB, id int) (*User, error) {
	var u User
	err := db.QueryRowContext(ctx,
		"SELECT id, name, email FROM users WHERE id = $1", id,
	).Scan(&u.ID, &u.Name, &u.Email)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrUserNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get user %d: %w", id, err)
	}
	return &u, nil
}

func ListUsers(ctx context.Context, db *sql.DB, minAge int) ([]User, error) {
	rows, err := db.QueryContext(ctx,
		"SELECT id, name, email FROM users WHERE age >= $1 ORDER BY id", minAge)
	if err != nil {
		return nil, fmt.Errorf("list users: %w", err)
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
			return nil, fmt.Errorf("scan user: %w", err)
		}
		users = append(users, u)
	}
	// Check that the loop was not interrupted by an error
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate users: %w", err)
	}
	return users, nil
}

func main() {
	db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/app?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Pool settings
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	db.SetConnMaxLifetime(30 * time.Minute)

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	// sql.Open does not connect, so verify connectivity with Ping
	if err := db.PingContext(ctx); err != nil {
		log.Fatal(err)
	}

	u, err := GetUser(ctx, db, 1)
	switch {
	case errors.Is(err, ErrUserNotFound):
		fmt.Println("not found")
	case err != nil:
		log.Fatal(err)
	default:
		fmt.Println(u.Name)
	}
}

Use ExecContext for INSERT / UPDATE / DELETE. You can get the number of affected rows from result.RowsAffected(). In PostgreSQL, to get the ID of an inserted row, run INSERT ... RETURNING id with QueryRowContext.

Example 2: Nullable columns and sqlx​

C#:

public record Profile(int Id, string? Bio, int? Age);

In Go, pick a type that can receive NULL.

package main

import (
	"context"
	"database/sql"
)

type Profile struct {
	ID  int
	Bio sql.NullString // NULL when Valid is false
	Age *int           // NULL when nil
}

func GetProfile(ctx context.Context, db *sql.DB, id int) (Profile, error) {
	var p Profile
	err := db.QueryRowContext(ctx,
		"SELECT id, bio, age FROM profiles WHERE id = $1", id,
	).Scan(&p.ID, &p.Bio, &p.Age)
	return p, err
}

func (p Profile) BioOrDefault() string {
	if p.Bio.Valid {
		return p.Bio.String
	}
	return "(none)"
}

If you want something closer to Dapper's feel, the third-party sqlx (github.com/jmoiron/sqlx) provides automatic mapping into structs.

// sqlx example (github.com/jmoiron/sqlx)
type User struct {
	ID    int    `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

var u User
err := db.GetContext(ctx, &u, "SELECT id, name, email FROM users WHERE id = $1", 1)

var users []User
err = db.SelectContext(ctx, &users, "SELECT id, name, email FROM users WHERE age >= $1", 20)

Example 3: Transactions​

C#:

await using var tx = await conn.BeginTransactionAsync();
try
{
    await conn.ExecuteAsync("UPDATE accounts SET balance = balance - @amt WHERE id = @from",
        new { amt, from }, tx);
    await conn.ExecuteAsync("UPDATE accounts SET balance = balance + @amt WHERE id = @to",
        new { amt, to }, tx);
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

In Go, wrapping the transaction in a helper function prevents forgetting to commit or roll back.

package main

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

// WithTx commits if fn succeeds, and rolls back on an error or a panic.
func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) (err error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}

	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p) // re-raise the panic to the caller
		}
		if err != nil {
			if rbErr := tx.Rollback(); rbErr != nil {
				err = fmt.Errorf("%w (rollback: %v)", err, rbErr)
			}
		}
	}()

	if err = fn(tx); err != nil {
		return err
	}
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("commit: %w", err)
	}
	return nil
}

func Transfer(ctx context.Context, db *sql.DB, from, to int, amount int64) error {
	return WithTx(ctx, db, func(tx *sql.Tx) error {
		// Use tx here, not db
		if _, err := tx.ExecContext(ctx,
			"UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
			return fmt.Errorf("debit: %w", err)
		}
		if _, err := tx.ExecContext(ctx,
			"UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
			return fmt.Errorf("credit: %w", err)
		}
		return nil
	})
}

If you use db inside fn, the statement runs outside the transaction. When designing a repository layer, a common technique is to define a small interface with ExecContext, QueryContext, and QueryRowContext that both *sql.DB and *sql.Tx satisfy, so either can be passed in.

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
}

Example 4: Generating Go code from SQL with sqlc​

The configuration file sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/query.sql"
    schema: "db/schema.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"

The schema db/schema.sql:

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

The queries db/query.sql (comments specify the query name and the shape of the result):

-- name: GetUser :one
SELECT id, name, email FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT id, name, email FROM users ORDER BY id;

-- name: CreateUser :one
INSERT INTO users (name, email) VALUES ($1, $2)
RETURNING id, name, email;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

Running sqlc generate produces the User struct, CreateUserParams, and the Queries type with its methods in the db package. The calling side looks like this.

queries := db.New(pool) // pool is a *pgxpool.Pool. Inside a transaction, use queries.WithTx(tx)

user, err := queries.CreateUser(ctx, db.CreateUserParams{
	Name:  "Alice",
	Email: "[email protected]",
})
if err != nil {
	return err
}

got, err := queries.GetUser(ctx, user.ID)
users, err := queries.ListUsers(ctx)
  • :one is a single row, :many is multiple rows, and :exec is for statements that return no result.
  • The SQL can be reviewed directly, and the generated code is just calls to database/sql or pgx, so behavior is easy to follow.
  • After changing the schema, rerun sqlc generate. Queries whose types no longer match fail at generation or compile time.

Example 5: A taste of GORM and ent​

EF Core:

var user = await db.Users.FirstOrDefaultAsync(u => u.Email == email);
user.Name = "Alice";
await db.SaveChangesAsync();

GORM (gorm.io/gorm):

// Connect (gorm.io/driver/postgres)
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

type User struct {
	ID    uint   `gorm:"primaryKey"`
	Name  string
	Email string `gorm:"uniqueIndex"`
}

// Fetch (always pass ctx with WithContext)
var user User
err = db.WithContext(ctx).Where("email = ?", email).First(&user).Error
if errors.Is(err, gorm.ErrRecordNotFound) {
	// not found
}

// Update (there is no change tracking, so update explicitly)
db.WithContext(ctx).Model(&user).Update("name", "Alice")

// When updating several columns, zero values in a struct are ignored. Use a map to set 0 or false
db.WithContext(ctx).Model(&user).Updates(map[string]any{"name": "Alice", "age": 0})

With ent (entgo.io/ent), you define the schema in Go code and a type-safe client is generated from it.

// Using a client generated by ent
u, err := client.User.
	Query().
	Where(user.EmailEQ(email)).
	Only(ctx)
  • For either one, add the package to your project, and for ent run code generation before use.
  • Even when you adopt an ORM, keep a way to run raw SQL (such as GORM's Raw and Exec) for complex queries.

Example 6: Operating migrations​

An example using the golang-migrate CLI.

# Create a new migration file pair (up / down)
migrate create -ext sql -dir db/migrations -seq create_users

# Apply
migrate -path db/migrations -database "$DATABASE_URL" up

# Roll back one step
migrate -path db/migrations -database "$DATABASE_URL" down 1

Write the apply and revert SQL in db/migrations/000001_create_users.up.sql and 000001_create_users.down.sql, respectively. With goose or Atlas as well, the flow is the same: keep SQL files in the repository and apply them from CI/CD.

# goose example
goose -dir db/migrations postgres "$DATABASE_URL" up

# Atlas example (apply directory-based migrations)
atlas migrate apply --dir "file://db/migrations" --url "$DATABASE_URL"

If you apply migrations automatically at application startup, rely on the tool's locking mechanism, or split it out as a deployment job, so that it stays safe when several instances start at once.

5. Summary​

  • database/sql is the standard abstraction, close to ADO.NET. A *sql.DB is a pool, not a connection; create it once at startup and share it.
  • Use the ...Context methods to pass ctx, and don't forget rows.Close(), rows.Err(), and the sql.ErrNoRows check.
  • Receive NULL with sql.NullString, sql.Null[T], or pointers.
  • Pass *sql.Tx around explicitly for transactions. A helper function that guarantees Rollback through defer keeps this safe.
  • Go has no standard equivalent of LINQ or change tracking. Choose sqlx for a Dapper-like feel, sqlc to write SQL first and get type-safe code, and GORM or ent for an EF Core-like experience.
  • Adopt an ORM only after understanding its implicit behavior (zero-value handling, AutoMigrate, and so on), and pair it with raw SQL for complex queries.
  • Manage migrations as SQL files with golang-migrate, goose, or Atlas, and apply them from the deployment pipeline.

References​