Back to Details

encore-go-database

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Encore Go Database Operations

Encore Go 数据库操作

Instructions

操作说明

Database Setup

数据库设置

go
package user

import "encore.dev/storage/sqldb"

var db = sqldb.NewDatabase("userdb", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})
go
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.
Encore 提供了基于泛型的类型安全查询方法。

Query
- Multiple Rows

Query
- 多行查询

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

QueryRow
- 单行查询

go
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
}
go
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

Exec
- 无返回值

For INSERT, UPDATE, DELETE operations:
go
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
}
适用于INSERT、UPDATE、DELETE操作：
go
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
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
  • 以数字开头（1、2等）
  • 后跟下划线和描述内容
  • 以
    .up.sql
    结尾
  • 数字必须按顺序排列

Example Migration

迁移示例

sql
-- 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);
sql
-- 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

事务

go
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()
}
go
func transferFunds(ctx context.Context, fromID, toID string, amount int) error {
    tx, err := db.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback()  // 若已提交则无操作
    
    _, 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:
go
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
标签映射到结构体字段：
go
type User struct {
    ID        string    `sql:"id"`
    Email     string    `sql:"email"`
    Name      string    `sql:"name"`
    CreatedAt time.Time `sql:"created_at"`
}

// 列名: id, email, name, created_at
// 会正确映射到结构体字段

SQL Injection Protection

SQL注入防护

Always use parameterized queries:
go
// 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)
始终使用参数化查询：
go
// 安全 - 值已参数化
user, err := sqldb.QueryRow[User](ctx, db, `
    SELECT * FROM users WHERE email = $1
`, email)

// 错误 - 存在SQL注入风险
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

Error Handling

错误处理

go
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
}
go
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
  • 始终使用参数化查询（
    $1
    、
    $2
    等）
  • 结合泛型使用
    sqldb.Query[T]
    和
    sqldb.QueryRow[T]
  • 当预期单行结果时，检查
    sqldb.ErrNoRows
  • 迁移会在启动时自动应用
  • 数据库名称应使用小写且具有描述性
  • 每个服务通常拥有独立的数据库
  • 对于必须原子性的操作，使用事务