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)