Database

Database operations and query building in PocketBase

PocketBase uses SQLite as its database engine and provides a powerful query builder for database operations.

Database instances

DB()

Get the main database builder (automatically routes queries):

db := app.DB()

The DB() method automatically routes:

• SELECT queries → Concurrent pool (for parallel reads)
• INSERT/UPDATE/DELETE → Nonconcurrent pool (serialized writes)

ConcurrentDB()

Get the concurrent database instance for read operations:

db := app.ConcurrentDB()

var count int
err := db.Select("COUNT(*)").From("posts").Row(&count)

NonconcurrentDB()

Get the nonconcurrent database instance for write operations:

db := app.NonconcurrentDB()

_, err := db.Insert("posts", dbx.Params{
    "id": "new_id",
    "title": "New Post",
}).Execute()

Query builder basics

PocketBase uses the dbx package for query building:

import "github.com/pocketbase/dbx"

SELECT queries

// Simple select
var results []struct {
    Title string `db:"title"`
    Views int    `db:"views"`
}

err := app.DB().
    Select("title", "views").
    From("posts").
    Where(dbx.HashExp{"published": true}).
    All(&results)

WHERE clauses

import "github.com/pocketbase/dbx"

// Hash expression (exact match)
query := app.DB().Select("*").From("posts").
    Where(dbx.HashExp{
        "published": true,
        "author": "user_id",
    })

// Custom expression with params
query = app.DB().Select("*").From("posts").
    Where(dbx.NewExp("views > {:min} AND views < {:max}", dbx.Params{
        "min": 100,
        "max": 1000,
    }))

// AND conditions
query = app.DB().Select("*").From("posts").
    AndWhere(dbx.HashExp{"published": true}).
    AndWhere(dbx.NewExp("created > {:date}", dbx.Params{
        "date": "2024-01-01",
    }))

// OR conditions
query = app.DB().Select("*").From("posts").
    Where(dbx.Or(
        dbx.HashExp{"status": "published"},
        dbx.HashExp{"status": "featured"},
    ))

ORDER BY

// Single column
query := app.DB().Select("*").From("posts").
    OrderBy("created DESC")

// Multiple columns
query = app.DB().Select("*").From("posts").
    OrderBy("status ASC", "created DESC")

LIMIT and OFFSET

query := app.DB().Select("*").From("posts").
    Limit(10).
    Offset(20)

JOINs

query := app.DB().
    Select("posts.*", "users.name as author_name").
    From("posts").
    LeftJoin("users", dbx.NewExp("users.id = posts.author"))

Executing queries

All()

Fetch all matching rows:

var records []*core.Record
err := app.RecordQuery("posts").
    Where(dbx.HashExp{"published": true}).
    All(&records)

One()

Fetch a single row:

var record *core.Record
err := app.RecordQuery("posts").
    Where(dbx.HashExp{"id": "RECORD_ID"}).
    One(&record)

Row()

Scan a single row into variables:

var title string
var views int

err := app.DB().
    Select("title", "views").
    From("posts").
    Where(dbx.HashExp{"id": "RECORD_ID"}).
    Row(&title, &views)

Rows()

Scan multiple rows:

var results []struct {
    Title string `db:"title"`
    Views int    `db:"views"`
}

err := app.DB().
    Select("title", "views").
    From("posts").
    Rows(&results)

Execute()

Execute a statement without fetching results:

_, err := app.DB().
    Delete("posts", dbx.HashExp{"id": "RECORD_ID"}).
    Execute()

INSERT operations

Single row insert

_, err := app.NonconcurrentDB().
    Insert("posts", dbx.Params{
        "id": core.GenerateDefaultRandomId(),
        "title": "New Post",
        "content": "Post content",
        "views": 0,
        "created": time.Now().Format("2006-01-02 15:04:05.000Z"),
        "updated": time.Now().Format("2006-01-02 15:04:05.000Z"),
    }).
    Execute()

Bulk insert

rows := []dbx.Params{
    {"id": "id1", "title": "Post 1"},
    {"id": "id2", "title": "Post 2"},
    {"id": "id3", "title": "Post 3"},
}

for _, row := range rows {
    _, err := app.NonconcurrentDB().
        Insert("posts", row).
        Execute()
    if err != nil {
        log.Fatal(err)
    }
}

UPDATE operations

Update with conditions

_, err := app.NonconcurrentDB().
    Update("posts", dbx.Params{
        "views": dbx.NewExp("views + 1"),
        "updated": time.Now().Format("2006-01-02 15:04:05.000Z"),
    }, dbx.HashExp{
        "id": "RECORD_ID",
    }).
    Execute()

DELETE operations

Delete with conditions

_, err := app.NonconcurrentDB().
    Delete("posts", dbx.HashExp{
        "status": "draft",
    }).
    Execute()

Transactions

RunInTransaction()

Execute multiple operations in a transaction:

err := app.RunInTransaction(func(txApp core.App) error {
    // Create a new post
    post := core.NewRecord(postsCollection)
    post.Set("title", "New Post")
    if err := txApp.Save(post); err != nil {
        return err // Rollback
    }
    
    // Update author's post count
    author, _ := txApp.FindRecordById("users", "author_id")
    author.Set("post_count+", 1)
    if err := txApp.Save(author); err != nil {
        return err // Rollback
    }
    
    return nil // Commit
})

if err != nil {
    log.Fatal("Transaction failed:", err)
}

Transactions automatically rollback if the function returns an error, and commit if it returns nil.

Nested transactions

err := app.RunInTransaction(func(txApp1 core.App) error {
    // Outer transaction
    
    return txApp1.RunInTransaction(func(txApp2 core.App) error {
        // Inner transaction (uses same transaction as outer)
        return txApp2.Save(record)
    })
})

Raw SQL

NewQuery()

Execute raw SQL queries:

var count int
err := app.DB().
    NewQuery("SELECT COUNT(*) FROM posts WHERE published = {:pub}").
    Bind(dbx.Params{"pub": true}).
    Row(&count)

With results

var results []struct {
    Category string `db:"category"`
    Total    int    `db:"total"`
}

err := app.DB().
    NewQuery(`
        SELECT category, COUNT(*) as total
        FROM posts
        WHERE published = true
        GROUP BY category
        ORDER BY total DESC
    `).
    All(&results)

Table operations

HasTable()

Check if a table exists:

if app.HasTable("posts") {
    log.Println("Posts table exists")
}

TableColumns()

Get all column names:

columns, err := app.TableColumns("posts")
if err != nil {
    log.Fatal(err)
}

for _, col := range columns {
    log.Println("Column:", col)
}

TableInfo()

Get detailed table information:

info, err := app.TableInfo("posts")
if err != nil {
    log.Fatal(err)
}

for _, row := range info {
    log.Printf("Column: %s, Type: %s", row.Name, row.Type)
}

TableIndexes()

Get table indexes:

indexes, err := app.TableIndexes("posts")
if err != nil {
    log.Fatal(err)
}

for name, sql := range indexes {
    log.Printf("Index %s: %s", name, sql)
}

Vacuum

Reclaim unused disk space:

// Vacuum main database
if err := app.Vacuum(); err != nil {
    log.Fatal(err)
}

// Vacuum auxiliary database
if err := app.AuxVacuum(); err != nil {
    log.Fatal(err)
}

Model queries

ModelQuery()

Create a preconfigured query for a model:

collection := &core.Collection{}
err := app.ModelQuery(collection).
    Where(dbx.HashExp{"name": "posts"}).
    One(collection)

Best practices

Use transactions for related operations

err := app.RunInTransaction(func(txApp core.App) error {
    // Multiple related saves
    if err := txApp.Save(record1); err != nil {
        return err
    }
    if err := txApp.Save(record2); err != nil {
        return err
    }
    return nil
})

Always use parameterized queries

// Good - prevents SQL injection
query := app.DB().Select("*").From("posts").
    Where(dbx.NewExp("title = {:title}", dbx.Params{
        "title": userInput,
    }))

// Bad - vulnerable to SQL injection
query := app.DB().Select("*").From("posts").
    Where(dbx.NewExp("title = '" + userInput + "'"))

Handle errors properly

var record *core.Record
err := app.RecordQuery("posts").
    Where(dbx.HashExp{"id": id}).
    One(&record)

if err != nil {
    if errors.Is(err, sql.ErrNoRows) {
        // Handle not found
        return fmt.Errorf("record not found")
    }
    // Handle other errors
    return err
}