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

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
}

​Database instances

​DB()

​ConcurrentDB()

​NonconcurrentDB()

​Query builder basics

​SELECT queries

​WHERE clauses

​ORDER BY

​LIMIT and OFFSET

​JOINs

​Executing queries

​All()

​One()

​Row()

​Rows()

​Execute()

​INSERT operations

​Single row insert

​Bulk insert

​UPDATE operations

​Update with conditions

​DELETE operations

​Delete with conditions

​Transactions

​RunInTransaction()

​Nested transactions

​Raw SQL

​NewQuery()

​With results

​Table operations

​HasTable()

​TableColumns()

​TableInfo()

​TableIndexes()

​Vacuum

​Model queries

​ModelQuery()

​Best practices

​Use transactions for related operations

​Always use parameterized queries

​Handle errors properly

Database instances

DB()

ConcurrentDB()

NonconcurrentDB()

Query builder basics

SELECT queries

WHERE clauses

ORDER BY

LIMIT and OFFSET

JOINs

Executing queries

All()

One()

Row()

Rows()

Execute()

INSERT operations

Single row insert

Bulk insert

UPDATE operations

Update with conditions

DELETE operations

Delete with conditions

Transactions

RunInTransaction()

Nested transactions

Raw SQL

NewQuery()

With results

Table operations

HasTable()

TableColumns()

TableInfo()

TableIndexes()

Vacuum

Model queries

ModelQuery()

Best practices

Use transactions for related operations

Always use parameterized queries

Handle errors properly