Index of every page on this site: llms.txt
All skills
encoredev avatar

/go-database

@e86a3ab official
by Encoreencoredev/skills30 stars
6
Run it yourself

Work with PostgreSQL in Encore Go using `sqldb.NewDatabase` from `encore.dev/storage/sqldb` — schema migrations and SQL queries.

  • 1 file
  • 6.1 KB
  • Updated 6 months ago
  • GitHub
Use this Skill: https://skilld.dev/gh/encoredev/skills/go-database

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈36 tokens for metadata: the name and description. ≈1.4k when used: this file.

Description uses 8.1% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈646 characters, or 8.1%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

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.

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

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

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

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)

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
  • 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

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at e86a3ab. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 4 weeks ago.

Activeupdated 6 months ago
Other metadata
when_to_use
User wants to add a database table, write a migration, run a SQL query, insert/update/delete rows, or design a relational schema in an Encore Go service. Covers `sqldb.NewDatabase`, `db.QueryRow(ctx, ...)`, `db.Query(ctx, ...)`, `db.Exec(ctx, ...)`, `db.Stdlib()` for ORM integration, the `migrations/` directory, `*.up.sql` files, and sequential migration numbering. Trigger phrases: "Postgres table", "user_sessions table", "SQL", "migration", "QueryRow", "INSERT", "SELECT", "schema", "sqldb".
  • Go
  • Database
  • postgresql
  • postgres
  • encore
  • sqldb
  • migrations
  • transactions
  • sql

README badge

README badge for encoredev/skills/go-database

Manages PostgreSQL connections and queries in Encore Go services using `sqldb.NewDatabase`, with support for schema migrations and standard SQL operations (QueryRow, Query, Exec). Covers parameterized queries, transaction handling, and the migrations/ directory workflow for sequential .up.sql files.

Generated from the current SKILL.md.

Does this skill work with MySQL or just PostgreSQL?
PostgreSQL only. The skill covers Encore's sqldb package configured for Postgres and does not address MySQL or other database engines.
How do I run migrations in Encore?
Place `.up.sql` files in the `migrations/` directory with sequential numbering (1_*, 2_*, etc.). Encore applies them automatically on startup.
How do I prevent SQL injection?
Always use parameterized queries with `$1`, `$2` placeholders instead of string concatenation or fmt.Sprintf.
Can I use an ORM like GORM with Encore's sqldb?
Yes. The skill mentions `db.Stdlib()` which returns a standard `*sql.DB` compatible with most Go ORMs.

Generated from the current SKILL.md. These answers refresh after source changes.