go-database_skill

Overview

This skill provides concise guidance and examples for performing database queries, migrations, and transactions using Encore with Go. It focuses on type-safe query methods, migration file conventions, struct-to-column mapping, and safe patterns for error handling and SQL injection prevention. The goal is to help you implement common CRUD operations, migrations, and atomic workflows in Encore services.

How this skill works

The skill explains Encore's sqldb package: create a named Database with a migrations path, then use generic helpers sqldb.Query[T], sqldb.QueryRow[T], and sqldb.Exec for multi-row, single-row, and non-returning operations. Migrations are simple .up.sql files numbered sequentially and applied automatically at service startup. Transactions are provided via db.Begin to get a tx that supports Exec and Commit/Rollback for atomic multi-statement updates.

When to use it

• Implementing CRUD endpoints with type-safe mapping between SQL rows and Go structs.
• Defining schema changes and versioned migrations for a service-specific database.
• Performing multi-statement, atomic updates such as transfers or batch updates.
• Querying lists or single records while handling not-found cases explicitly.
• Protecting queries from SQL injection by using parameterized statements.

Best practices

• Always use parameterized queries ($1, $2, ...) instead of string concatenation.
• Use generics: sqldb.Query[T] for lists and sqldb.QueryRow[T] for single rows.
• Check for sqldb.ErrNoRows when expecting a single row and return a NotFound error.
• Keep migration filenames sequential and descriptive (1_description.up.sql, 2_...).
• Give each service its own lowercased, descriptive database name.
• Use transactions (db.Begin/tx.Commit) for operations that must be atomic.

Example use cases

• List active users: sqldb.Query[User] to stream rows and map to a slice of User structs.
• Fetch a single user by ID: sqldb.QueryRow[User] and handle sqldb.ErrNoRows.
• Create, update, delete records with sqldb.Exec for non-returning statements.
• Define schema in migrations/1_create_users.up.sql and let Encore apply them on startup.
• Transfer funds between accounts using a transaction to update both balances atomically.