> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/pocketbase/pocketbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Database operations and query building in 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):

```go theme={null}
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:

```go theme={null}
db := app.ConcurrentDB()

var count int
err := db.Select("COUNT(*)").From("posts").Row(&count)
```

### NonconcurrentDB()

Get the nonconcurrent database instance for write operations:

```go theme={null}
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:

```go theme={null}
import "github.com/pocketbase/dbx"
```

### SELECT queries

```go theme={null}
// 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

```go theme={null}
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

```go theme={null}
// 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

```go theme={null}
query := app.DB().Select("*").From("posts").
    Limit(10).
    Offset(20)
```

### JOINs

```go theme={null}
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:

```go theme={null}
var records []*core.Record
err := app.RecordQuery("posts").
    Where(dbx.HashExp{"published": true}).
    All(&records)
```

### One()

Fetch a single row:

```go theme={null}
var record *core.Record
err := app.RecordQuery("posts").
    Where(dbx.HashExp{"id": "RECORD_ID"}).
    One(&record)
```

### Row()

Scan a single row into variables:

```go theme={null}
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:

```go theme={null}
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:

```go theme={null}
_, err := app.DB().
    Delete("posts", dbx.HashExp{"id": "RECORD_ID"}).
    Execute()
```

## INSERT operations

### Single row insert

```go theme={null}
_, 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

```go theme={null}
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

```go theme={null}
_, 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

```go theme={null}
_, err := app.NonconcurrentDB().
    Delete("posts", dbx.HashExp{
        "status": "draft",
    }).
    Execute()
```

## Transactions

### RunInTransaction()

Execute multiple operations in a transaction:

```go theme={null}
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)
}
```

<Info>
  Transactions automatically rollback if the function returns an error, and commit if it returns nil.
</Info>

### Nested transactions

```go theme={null}
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:

```go theme={null}
var count int
err := app.DB().
    NewQuery("SELECT COUNT(*) FROM posts WHERE published = {:pub}").
    Bind(dbx.Params{"pub": true}).
    Row(&count)
```

### With results

```go theme={null}
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:

```go theme={null}
if app.HasTable("posts") {
    log.Println("Posts table exists")
}
```

### TableColumns()

Get all column names:

```go theme={null}
columns, err := app.TableColumns("posts")
if err != nil {
    log.Fatal(err)
}

for _, col := range columns {
    log.Println("Column:", col)
}
```

### TableInfo()

Get detailed table information:

```go theme={null}
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:

```go theme={null}
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:

```go theme={null}
// 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:

```go theme={null}
collection := &core.Collection{}
err := app.ModelQuery(collection).
    Where(dbx.HashExp{"name": "posts"}).
    One(collection)
```

## Best practices

### Use transactions for related operations

```go theme={null}
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

```go theme={null}
// 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

```go theme={null}
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
}
```