Advanced - Database - Docs

When using PocketBase as framework you have access to its internal query mechanism via the app.DB() and app.Dao() instances.

app.DB() is a DB query builder (dbx) instance, allowing you to execute safely various SQL statement (including raw queries).

app.Dao() on the other hand is a service layer on top of app.DB() that provides helpers to manage the app models. It is also responsible for triggering the OnModel* event hooks. You can check all available methods in the godoc package documentation but you could could also find some information in the Collection methods and Record methods docs.

Query builder

We use dbx as a query builder to safely compose and run SQL queries. You can find detailed documentation in the package README, but here is an example to get the general look-and-feel:

import (
    "github.com/pocketbase/dbx"
)

...

var total int

err := app.DB().
    Select("count(*)").
    From("articles").
    AndWhere(dbx.HashExp{"status": "active"}).
    AndWhere(dbx.Like("title", "example")).
    Row(&total)

Transaction

The easiest way to run multiple queries inside a transaction is to wrap them with Dao.RunInTransaction().
You can nest Dao.RunInTransaction() as many times as you want.
The transaction will be commited only if there are no errors.

err := app.Dao().RunInTransaction(func(txDao *daos.Dao) error {
    // update a record
    record, err := txDao.FindRecordById("articles", "RECORD_ID")
    if err != nil {
        return err
    }
    record.Set("status", "active")
    if err := txDao.SaveRecord(record); err != nil {
        return err
    }

    // run some custom raw query
    query := txDao.DB().NewQuery("DELETE articles WHERE status = 'pending'")
    if _, err := query.Execute(); err != nil {
        return err
    }

    return nil
})

DAO without event hooks

By default all DAO modify operations (create, update, delete) trigger the OnModel* event hooks.
If you don't want this behavior you can create a new Dao by just passing a DB instance (you can also access it from the original one):

record, _ := app.Dao().FindRecordById("articles", "RECORD_ID")

// the below WILL fire the OnModelBeforeUpdate and OnModelAfterUpdate hooks
app.Dao().SaveRecord(record)

// the below WILL NOT fire the OnModelBeforeUpdate and OnModelAfterUpdate hooks
dao := daos.New(app.Dao().DB())
dao.SaveRecord(record)