Database operations

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,
  })
})

REST API

Go SDK

JavaScript SDK

Field Types

Query builder

Basic queries

Filtering

Multiple conditions

OR conditions

Expressions

Hash expression

Custom expression

IN expression

LIKE expression

BETWEEN expression

EXISTS expression

Logical operators

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

​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

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