go-database

Encore Go Database Operations

Instructions

Database Setup

package user

import "encore.dev/storage/sqldb"

var db = sqldb.NewDatabase("userdb", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

Query Methods

Encore's database API mirrors Go's standard database/sql package. Use .Scan() to read query results into variables. Each snippet below is an independent example, not one file; the same function name may appear in more than one snippet, so keep only one version in your code. The snippets assume the context, errors, time and errs packages are imported.

Query - Multiple Rows

type User struct {
    ID    string
    Email string
    Name  string
}

func listActiveUsers(ctx context.Context) ([]*User, error) {
    rows, err := db.Query(ctx, `
        SELECT id, email, name FROM users WHERE active = true
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []*User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Email, &u.Name); err != nil {
            return nil, err
        }
        users = append(users, &u)
    }
    return users, rows.Err()
}

For list queries exposed through an API, add a stable sort order and a row limit passed as a parameter, optionally with keyset pagination, and cap the page size so results stay bounded.

QueryRow - Single Row

func getUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := db.QueryRow(ctx, `
        SELECT id, email, name FROM users WHERE id = $1
    `, id).Scan(&u.ID, &u.Email, &u.Name)

    if errors.Is(err, sqldb.ErrNoRows) {
        return nil, &errs.Error{
            Code:    errs.NotFound,
            Message: "user not found",
        }
    }
    if err != nil {
        return nil, err
    }
    return &u, nil
}

Exec - No Return Value

For INSERT, UPDATE, DELETE operations:

func createUser(ctx context.Context, email, name string) error {
    _, err := db.Exec(ctx, `
        INSERT INTO users (id, email, name)
        VALUES ($1, $2, $3)
    `, generateID(), email, name)
    return err
}

func updateUser(ctx context.Context, id, name string) error {
    _, err := db.Exec(ctx, `
        UPDATE users SET name = $1 WHERE id = $2
    `, name, id)
    return err
}

func deleteUser(ctx context.Context, id string) error {
    _, err := db.Exec(ctx, `
        DELETE FROM users WHERE id = $1
    `, id)
    return err
}

Migrations

File Structure

user/
└── migrations/
    ├── 1_create_users.up.sql
    ├── 2_add_posts.up.sql
    └── 3_add_indexes.up.sql

Naming Convention

• Start with a number (1, 2, etc.)
• Followed by underscore and description
• End with .up.sql
• Numbers must be sequential
• Migrations are forward only: once a migration has been applied, leave it as is and add a new numbered file for further changes
• Prefer additive, backward compatible changes
• Get explicit user confirmation before writing a migration that drops or destructively alters tables or columns, since migrations run on startup
• Have the user review the SQL and back up data before deploying beyond local development

Example Migration

-- migrations/1_create_users.up.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Transactions

func transferFunds(ctx context.Context, fromID, toID string, amount int) error {
    tx, err := db.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback()  // No-op if committed
    
    _, err = tx.Exec(ctx, `
        UPDATE accounts SET balance = balance - $1 WHERE id = $2
    `, amount, fromID)
    if err != nil {
        return err
    }
    
    _, err = tx.Exec(ctx, `
        UPDATE accounts SET balance = balance + $1 WHERE id = $2
    `, amount, toID)
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

This example is minimal. In real code, reject a zero or negative amount and a source account equal to the destination account before beginning the transaction, guard the debit so the balance stays at or above zero, and check that each update changed exactly one row, returning an error otherwise so the deferred rollback undoes the transfer.

Using Scan

The Scan method reads columns from query results into variables. Columns are mapped by position, not by name - the order of arguments to Scan must match the order of columns in your SELECT statement.

type User struct {
    ID        string
    Email     string
    Name      string
    CreatedAt time.Time
}

// Single row with QueryRow
func getUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := db.QueryRow(ctx, `
        SELECT id, email, name, created_at FROM users WHERE id = $1
    `, id).Scan(&u.ID, &u.Email, &u.Name, &u.CreatedAt)
    if err != nil {
        return nil, err
    }
    return &u, nil
}

// You can also scan into an inline struct
func getItem(ctx context.Context, id int64) error {
    var item struct {
        ID    int64
        Title string
        Done  bool
    }
    err := db.QueryRow(ctx, `
        SELECT id, title, done FROM items WHERE id = $1
    `, id).Scan(&item.ID, &item.Title, &item.Done)
    return err
}

SQL Injection Protection

Always use parameterized queries:

// SAFE - values are parameterized
var u User
err := db.QueryRow(ctx, `
    SELECT id, email, name FROM users WHERE email = $1
`, email).Scan(&u.ID, &u.Email, &u.Name)

// WRONG - SQL injection risk
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

Placeholders only cover values. Table names, column names, sort columns and sort direction cannot be passed as placeholders, so pick them from a fixed allowlist in code that maps user input to known names, and reject any other input.

Error Handling

import (
    "errors"
    "encore.dev/storage/sqldb"
    "encore.dev/beta/errs"
)

func getUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := db.QueryRow(ctx, `
        SELECT id, email, name FROM users WHERE id = $1
    `, id).Scan(&u.ID, &u.Email, &u.Name)

    if errors.Is(err, sqldb.ErrNoRows) {
        return nil, &errs.Error{
            Code:    errs.NotFound,
            Message: "user not found",
        }
    }
    if err != nil {
        return nil, err
    }
    return &u, nil
}

Guidelines

• Always use parameterized queries ($1, $2, etc.)
• Use Scan to read query results - columns are mapped by position
• Check for sqldb.ErrNoRows when expecting a single row
• In API endpoints, wrap unexpected database errors in an internal error with a generic message and log the original error, rather than returning it to clients; keep the not found mapping for sqldb.ErrNoRows
• Migrations are applied automatically on startup, so have the user review each migration before deploying
• Database names should be lowercase, descriptive
• Each service typically has its own database
• Use transactions for operations that must be atomic