Database operations - PocketBase

PocketBase provides powerful database query capabilities through the $dbx namespace and query builder methods.

Query builder

The query builder allows you to construct complex SQL queries using a fluent interface.

Basic queries

const records = arrayOf(new Record())

// Simple query
$app.recordQuery("posts")
  .all(records)

console.log("Total posts:", records.length)

Filtering

const records = arrayOf(new Record())

$app.recordQuery("posts")
  .andWhere($dbx.hashExp({"status": "published"}))
  .all(records)

Multiple conditions

const records = arrayOf(new Record())

$app.recordQuery("posts")
  .andWhere($dbx.hashExp({"status": "published"}))
  .andWhere($dbx.exp("created >= {:date}", {
    date: "2024-01-01 00:00:00.000Z"
  }))
  .all(records)

OR conditions

const records = arrayOf(new Record())

$app.recordQuery("posts")
  .andWhere($dbx.or(
    $dbx.hashExp({"status": "published"}),
    $dbx.hashExp({"status": "featured"})
  ))
  .all(records)

Expressions

The $dbx namespace provides several expression builders:

Hash expression

// Simple equality
$dbx.hashExp({"author": "user123"})

// Multiple fields
$dbx.hashExp({
  "status": "published",
  "featured": true,
})

Custom expression

// With parameters
$dbx.exp("view_count > {:min}", {min: 100})

// Multiple parameters
$dbx.exp(
  "created >= {:start} AND created <= {:end}",
  {
    start: "2024-01-01 00:00:00.000Z",
    end: "2024-12-31 23:59:59.999Z",
  }
)

IN expression

// Array of values
$dbx.in("status", ["published", "featured", "archived"])

// NOT IN
$dbx.notIn("status", ["draft", "deleted"])

LIKE expression

// Contains
$dbx.like("title", "JavaScript")

// OR LIKE
$dbx.orLike("title", "TypeScript")

// NOT LIKE
$dbx.notLike("title", "spam")

BETWEEN expression

// Numeric range
$dbx.between("price", 10, 100)

// NOT BETWEEN
$dbx.notBetween("price", 1000, 9999)

EXISTS expression

// Subquery exists
$dbx.exists("SELECT 1 FROM posts WHERE author = users.id")

// NOT EXISTS
$dbx.notExists("SELECT 1 FROM bans WHERE user_id = users.id")

Logical operators

// AND
$dbx.and(
  $dbx.hashExp({"status": "published"}),
  $dbx.exp("view_count > 100")
)

// OR
$dbx.or(
  $dbx.hashExp({"featured": true}),
  $dbx.exp("view_count > 1000")
)

// NOT
$dbx.not($dbx.hashExp({"deleted": true}))

Sorting and pagination

Order by

const records = arrayOf(new Record())

// Single field
$app.recordQuery("posts")
  .orderBy("created DESC")
  .all(records)

// Multiple fields
$app.recordQuery("posts")
  .orderBy("status ASC, created DESC")
  .all(records)

Limit and offset

const records = arrayOf(new Record())

// Limit results
$app.recordQuery("posts")
  .limit(10)
  .all(records)

// Pagination
const page = 2
const perPage = 20

$app.recordQuery("posts")
  .limit(perPage)
  .offset((page - 1) * perPage)
  .orderBy("created DESC")
  .all(records)

Counting records

// Get total count
const total = $app.countRecords("posts")

// Count with filter
const query = $app.recordQuery("posts")
  .andWhere($dbx.hashExp({"status": "published"}))

const published = query.count()

Aggregations

Using raw SQL

const result = new DynamicModel({
  total: 0,
  average: -0,
  max: 0,
})

$app.db().newQuery(`
  SELECT
    COUNT(*) as total,
    AVG(view_count) as average,
    MAX(view_count) as max
  FROM posts
  WHERE status = 'published'
`).one(result)

console.log("Total:", result.total)
console.log("Average views:", result.average)
console.log("Max views:", result.max)

Group by

const results = arrayOf(new DynamicModel({
  status: "",
  count: 0,
}))

$app.db().newQuery(`
  SELECT
    status,
    COUNT(*) as count
  FROM posts
  GROUP BY status
`).all(results)

for (let row of results) {
  console.log(row.status + ":", row.count)
}

Joins

Inner join

const results = arrayOf(new DynamicModel({
  post_title: "",
  author_name: "",
  author_email: "",
}))

$app.db().newQuery(`
  SELECT
    posts.title as post_title,
    users.username as author_name,
    users.email as author_email
  FROM posts
  INNER JOIN users ON posts.author = users.id
  WHERE posts.status = 'published'
  ORDER BY posts.created DESC
  LIMIT 10
`).all(results)

for (let row of results) {
  console.log(row.post_title, "by", row.author_name)
}

Left join

const results = arrayOf(new DynamicModel({
  post_id: "",
  post_title: "",
  comment_count: 0,
}))

$app.db().newQuery(`
  SELECT
    posts.id as post_id,
    posts.title as post_title,
    COUNT(comments.id) as comment_count
  FROM posts
  LEFT JOIN comments ON comments.post = posts.id
  WHERE posts.status = 'published'
  GROUP BY posts.id
  ORDER BY comment_count DESC
`).all(results)

Transactions

Transactions ensure that multiple database operations either all succeed or all fail together.

Basic transaction

$app.runInTransaction((txApp) => {
  // All operations in this function use the same transaction
  
  const user = txApp.findRecordById("users", userId)
  const credits = user.getInt("credits")
  
  if (credits < 100) {
    throw new BadRequestError("Insufficient credits")
  }
  
  user.set("credits", credits - 100)
  txApp.save(user)
  
  const purchase = new Record(txApp.findCollectionByNameOrId("purchases"))
  purchase.set("user", userId)
  purchase.set("amount", 100)
  purchase.set("item", "premium_feature")
  txApp.save(purchase)
  
  // Return null for success, or an error to rollback
  return null
})

Error handling

try {
  $app.runInTransaction((txApp) => {
    // Perform multiple operations
    const record1 = new Record(txApp.findCollectionByNameOrId("collection1"))
    record1.set("field", "value")
    txApp.save(record1)
    
    // This will rollback everything if it fails
    const record2 = new Record(txApp.findCollectionByNameOrId("collection2"))
    record2.set("relation", record1.id)
    txApp.save(record2)
    
    return null
  })
  
  console.log("Transaction successful")
} catch (err) {
  console.log("Transaction failed:", err.message)
}

Raw SQL queries

SELECT queries

// Single result
const result = new DynamicModel({
  id: "",
  title: "",
  view_count: 0,
})

$app.db().newQuery(`
  SELECT id, title, view_count
  FROM posts
  WHERE id = {:id}
`).bind({id: "RECORD_ID"}).one(result)

console.log(result.title, "has", result.view_count, "views")

Multiple results

const results = arrayOf(new DynamicModel({
  id: "",
  email: "",
  username: "",
}))

$app.db().newQuery(`
  SELECT id, email, username
  FROM users
  WHERE verified = {:verified}
  ORDER BY created DESC
  LIMIT {:limit}
`).bind({
  verified: true,
  limit: 50,
}).all(results)

console.log("Found", results.length, "verified users")

INSERT queries

$app.db().newQuery(`
  INSERT INTO logs (user_id, action, created)
  VALUES ({:userId}, {:action}, {:created})
`).bind({
  userId: user.id,
  action: "login",
  created: new DateTime().string(),
}).execute()

UPDATE queries

$app.db().newQuery(`
  UPDATE posts
  SET view_count = view_count + 1
  WHERE id = {:id}
`).bind({id: recordId}).execute()

DELETE queries

$app.db().newQuery(`
  DELETE FROM sessions
  WHERE expires < {:now}
`).bind({now: new DateTime().string()}).execute()

Performance tips

Use indexes

// Create an index for frequently queried fields
$app.db().newQuery(`
  CREATE INDEX IF NOT EXISTS idx_posts_status
  ON posts (status)
`).execute()

$app.db().newQuery(`
  CREATE INDEX IF NOT EXISTS idx_posts_author
  ON posts (author)
`).execute()

Limit result sets

Always use limit() when you don’t need all records:

// Good - limits results
$app.recordQuery("posts").limit(100).all(records)

// Bad - loads all records (could be thousands)
$app.recordQuery("posts").all(records)

Use select fields

When using raw SQL, only select the fields you need:

// Good - only selects needed fields
$app.db().newQuery(`
  SELECT id, title
  FROM posts
  WHERE status = 'published'
`).all(results)

// Bad - selects all fields
$app.db().newQuery(`
  SELECT *
  FROM posts
  WHERE status = 'published'
`).all(results)

Example: Search with pagination

routerAdd("GET", "/api/posts/search", (e) => {
  const query = e.request.url.query.get("q") || ""
  const page = parseInt(e.request.url.query.get("page") || "1")
  const perPage = 20
  
  const records = arrayOf(new Record())
  
  const qb = $app.recordQuery("posts")
    .andWhere($dbx.hashExp({"status": "published"}))
  
  if (query) {
    qb.andWhere($dbx.or(
      $dbx.like("title", query),
      $dbx.like("content", query)
    ))
  }
  
  const total = qb.count()
  
  qb.orderBy("created DESC")
    .limit(perPage)
    .offset((page - 1) * perPage)
    .all(records)
  
  return e.json(200, {
    page: page,
    perPage: perPage,
    totalItems: total,
    totalPages: Math.ceil(total / perPage),
    items: records,
  })
})

​Query builder

​Basic queries

​Filtering

​Multiple conditions

​OR conditions

​Expressions

​Hash expression

​Custom expression

​IN expression

​LIKE expression

​BETWEEN expression

​EXISTS expression

​Logical operators

​Sorting and pagination

​Order by

​Limit and offset

​Counting records

​Aggregations

​Using raw SQL

​Group by

​Joins

​Inner join

​Left join

​Transactions

​Basic transaction

​Error handling

​Raw SQL queries

​SELECT queries

​Multiple results

​INSERT queries

​UPDATE queries

​DELETE queries

​Performance tips

​Use indexes

​Limit result sets

​Use select fields

​Example: Search with pagination