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 provides type-safe query methods using generics.

Query – Multiple Rows

type User struct {
    ID    string
    Email string
    Name  string
}

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

QueryRow – Single Row

func getUser(ctx context.Context, id string) (*User, error) {
    user, err := sqldb.QueryRow[User](ctx, db, `
        SELECT id, email, name FROM users WHERE id = $1
    `, id)
    if errors.Is(err, sqldb.ErrNoRows) {
        return nil, &errs.Error{
            Code:    errs.NotFound,
            Message: "user not found",
        }
    }
    if err != nil {
        return nil, err
    }
    return user, nil
}

Exec – No Return Value

For INSERT, UPDATE, DELETE operations:

func createUser(ctx context.Context, email, name string) error {
    _, err := sqldb.Exec(ctx, db, `
        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 := sqldb.Exec(ctx, db, `
        UPDATE users SET name = $1 WHERE id = $2
    `, name, id)
    return err
}

func deleteUser(ctx context.Context, id string) error {
    _, err := sqldb.Exec(ctx, db, `
        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

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()
}

Struct Mapping

Query results map to struct fields by name (case-insensitive) or sql tag:

type User struct {
    ID        string    `sql:"id"`
    Email     string    `sql:"email"`
    Name      string    `sql:"name"`
    CreatedAt time.Time `sql:"created_at"`
}

// Columns: id, email, name, created_at
// Will map correctly to struct fields

SQL Injection Protection

Always use parameterized queries:

// SAFE - values are parameterized
user, err := sqldb.QueryRow[User](ctx, db, `
    SELECT * FROM users WHERE email = $1
`, email)

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

Error Handling

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

func getUser(ctx context.Context, id string) (*User, error) {
    user, err := sqldb.QueryRow[User](ctx, db, `
        SELECT id, email, name FROM users WHERE id = $1
    `, id)
    
    if errors.Is(err, sqldb.ErrNoRows) {
        return nil, &errs.Error{
            Code:    errs.NotFound,
            Message: "user not found",
        }
    }
    if err != nil {
        return nil, err
    }
    return user, nil
}

Guidelines

• Always use parameterized queries ($1, $2, etc.)
• Use generics with sqldb.Query[T] and sqldb.QueryRow[T]
• Check for sqldb.ErrNoRows when expecting a single row
• Migrations are applied automatically on startup
• Database names should be lowercase, descriptive
• Each service typically has its own database
• Use transactions for operations that must be atomic