Queries

Copper’s csql package provides a thin, productive layer over database/sql. Queries are parameterized and scanned into plain structs, so there is no ORM to learn and no query language beyond SQL itself.

The Queries Struct

Each package that talks to the database keeps its queries in a Queries struct. You may scaffold one using the CLI:

copper scaffold:queries rockets

This creates pkg/rockets/queries.go and adds it to the package’s wire module:

pkg/rockets/queries.go

func NewQueries(querier csql.Querier) *Queries {
	return &Queries{querier: querier}
}

type Queries struct {
	querier csql.Querier
}

Models

Rows are scanned into plain structs whose db: tags map columns to fields. The examples on this page use the following model; to learn more about defining models, tag rules, and custom column types, see Models:

pkg/rockets/models.go

type Rocket struct {
	ID        int64     `db:"id,readonly"`
	CreatedAt time.Time `db:"created_at,readonly"`
	Name      string    `db:"name"`
	Fuel      int64     `db:"fuel"`
	Note      *string   `db:"note"` // nullable column
}

Reading Rows

The Get method scans a single row into a struct, while Select scans multiple rows into a slice. Rather than writing SELECT * or a hand-maintained column list, render the columns from the model’s db: tags with qb.Columns:

func (q *Queries) GetRocket(ctx context.Context, id int64) (*Rocket, error) {
	var rocket Rocket

	query := fmt.Sprintf("SELECT %s FROM rockets WHERE id = ?", qb.Columns(Rocket{}))

	err := q.querier.Get(ctx, &rocket, query, id)
	if err != nil {
		return nil, err
	}

	return &rocket, nil
}

func (q *Queries) ListRockets(ctx context.Context) ([]Rocket, error) {
	var rockets []Rocket

	query := fmt.Sprintf("SELECT %s FROM rockets ORDER BY created_at DESC", qb.Columns(Rocket{}))

	err := q.querier.Select(ctx, &rockets, query)
	if err != nil {
		return nil, err
	}

	return rockets, nil
}

You should always write ? placeholders, regardless of your database. Copper will automatically rebind them to your dialect’s native style, such as $1 for Postgres, before executing the query.

Writing Rows

Exec runs INSERT, UPDATE, and DELETE statements:

func (q *Queries) RefuelRocket(ctx context.Context, id int64, fuel int64) error {
	result, err := q.querier.Exec(ctx,
		"UPDATE rockets SET fuel = ? WHERE id = ?", fuel, id)
	if err != nil {
		return err
	}

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

	if n == 0 {
		return ErrRocketNotFound
	}

	return nil
}

Handling Missing Rows

Get returns sql.ErrNoRows when nothing matches. It is common to alias it as a package sentinel so that callers do not need to import database/sql:

pkg/rockets/models.go

var ErrRocketNotFound = sql.ErrNoRows

rocket, err := ro.queries.GetRocket(ctx, id)
if errors.Is(err, rockets.ErrRocketNotFound) {
	// respond with 404
}

IN Queries

To pass a slice into an IN (?) clause, use WithIn():

var rockets []Rocket

query := fmt.Sprintf("SELECT %s FROM rockets WHERE id IN (?)", qb.Columns(Rocket{}))

err := q.querier.WithIn().Select(ctx, &rockets, query, []int64{101, 102})

Copper expands the single ? into one placeholder per element of the slice.

Connections and Drivers

Copper opens the connection from your config and closes it on shutdown:

config/dev.toml

[csql]
dialect = "pgx"
dsn = "postgresql://postgres:dev@127.0.0.1/postgres?sslmode=disable"

The database driver is blank-imported by your application rather than by Copper. copper create sets this up for you in cmd/app/wire.go:

import (
	_ "github.com/jackc/pgx/v5/stdlib"
)

The pool defaults to 25 open connections, 25 idle connections, and a 5-minute connection lifetime. If you would like to tune these, set max_open_connections, max_idle_connections, and conn_max_lifetime_mins under [csql]. All of the available keys are listed in the Configuration Reference.

Now that you can read and write rows, you may want to learn how Copper wraps mutating requests in automatic Transactions, or let Query Builder write your column lists for you.

Getting Started

The Basics

Architecture

HTTP

Database

Frontend

Digging Deeper

Reference

The Queries Struct

Models

Reading Rows

Writing Rows

Handling Missing Rows

IN Queries

Connections and Drivers

Next

​The Queries Struct

​Models

​Reading Rows

​Writing Rows

​Handling Missing Rows

​IN Queries

​Connections and Drivers

​Next

The Queries Struct

Models

Reading Rows

Writing Rows

Handling Missing Rows

IN Queries

Connections and Drivers

Next