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

# Database Overview

> Native SQLite database with actor-based concurrency — no ORM overhead, no external dependencies.

# Database Overview

Prism ships with a built-in SQLite database layer that talks directly to the C API through `sqlite3.h`. There's no ORM abstraction tax — you get raw performance with a clean Swift interface, all wrapped in an actor for safe concurrent access.

<Note>
  Prism uses the system SQLite that ships with macOS and Linux. No additional installation needed.
</Note>

## Why SQLite?

SQLite is the most deployed database in the world. For many server applications — APIs, microservices, internal tools — it's the right choice:

* **Zero configuration** — no separate database process to manage
* **Single-file storage** — easy backups, easy deployment
* **Incredible read performance** — faster than client-server databases for most workloads
* **ACID compliant** — full transaction support

<Tip>
  SQLite handles thousands of concurrent reads and hundreds of writes per second. For most APIs, that's more than enough. When you outgrow it, Prism's `PrismDatabase` protocol makes it straightforward to swap backends.
</Tip>

## Creating a Database

```swift title="Basic Setup" theme={null}
let db = try PrismDatabase(path: "app.db")
```

For testing, use an in-memory database:

```swift title="In-Memory Database" theme={null}
let db = try PrismDatabase(path: ":memory:")
```

## Creating Tables

```swift title="Creating a Users Table" theme={null}
try await db.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")
```

## Basic CRUD Operations

<Tabs>
  <Tab title="Insert">
    ```swift title="Insert a Row" theme={null}
    try await db.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        parameters: [.text("Alice"), .text("alice@example.com")]
    )

    let id = await db.lastInsertID
    print("Created user with ID: \(id)")
    ```
  </Tab>

  <Tab title="Query">
    ```swift title="Query Rows" theme={null}
    let users = try await db.query(
        "SELECT * FROM users WHERE name LIKE ?",
        parameters: [.text("%Ali%")]
    )

    for user in users {
        let name = user["name"]?.textValue ?? "Unknown"
        let email = user["email"]?.textValue ?? ""
        print("\(name) — \(email)")
    }
    ```
  </Tab>

  <Tab title="Update">
    ```swift title="Update a Row" theme={null}
    let affected = try await db.execute(
        "UPDATE users SET name = ? WHERE id = ?",
        parameters: [.text("Alice Smith"), .integer(1)]
    )
    print("Updated \(affected) rows")
    ```
  </Tab>

  <Tab title="Delete">
    ```swift title="Delete a Row" theme={null}
    let affected = try await db.execute(
        "DELETE FROM users WHERE id = ?",
        parameters: [.integer(1)]
    )
    print("Deleted \(affected) rows")
    ```
  </Tab>
</Tabs>

## Parameter Binding

Always use parameterized queries to prevent SQL injection:

```swift title="Safe Parameter Binding" theme={null}
// PrismDatabaseValue enum
let params: [PrismDatabaseValue] = [
    .text("Alice"),          // String
    .integer(42),            // Int
    .real(3.14),             // Double
    .blob(imageData),        // Data
    .null                    // NULL
]

try await db.execute("INSERT INTO items (name, count, price, image, notes) VALUES (?, ?, ?, ?, ?)", parameters: params)
```

<Warning>
  Never interpolate user input directly into SQL strings. Always use `?` placeholders and pass values through the `parameters` array.
</Warning>

## Transactions

Group multiple operations into an atomic transaction:

```swift title="Transaction Example" theme={null}
try await db.transaction { db in
    try db.execute(
        "INSERT INTO orders (user_id, total) VALUES (?, ?)",
        parameters: [.integer(1), .real(99.99)]
    )
    try db.execute(
        "UPDATE users SET order_count = order_count + 1 WHERE id = ?",
        parameters: [.integer(1)]
    )
}
```

If any statement fails, the entire transaction rolls back automatically.

## Query Results

`PrismRow` gives you typed access to column values:

```swift title="Working with Rows" theme={null}
let row = try await db.queryFirst("SELECT * FROM users WHERE id = ?", parameters: [.integer(1)])

if let user = row {
    let name = user["name"]?.textValue      // String?
    let age = user["age"]?.intValue          // Int?
    let score = user["score"]?.realValue     // Double?
    let avatar = user["avatar"]?.blobValue   // Data?
}
```

## Using with the Server

```swift title="Database in Route Handlers" theme={null}
let db = try PrismDatabase(path: "app.db")
let server = PrismHTTPServer(port: 8080)

await server.get("/users") { request in
    let users = try await db.query("SELECT id, name, email FROM users")
    let result = users.map { row in
        [
            "id": row["id"]?.intValue ?? 0,
            "name": row["name"]?.textValue ?? "",
            "email": row["email"]?.textValue ?? ""
        ] as [String: Any]
    }
    return .json(result)
}

await server.post("/users") { request in
    let name: String = try request.decodeJSON(key: "name")
    let email: String = try request.decodeJSON(key: "email")

    try await db.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        parameters: [.text(name), .text(email)]
    )

    return .json(["id": await db.lastInsertID], status: .created)
}
```

<CardGroup cols={2}>
  <Card title="Query Builder" icon="magnifying-glass" href="/server/database/queries">
    Build queries fluently without writing raw SQL
  </Card>

  <Card title="Models" icon="cube" href="/server/database/models">
    Map database rows to Swift structs automatically
  </Card>

  <Card title="Migrations" icon="arrows-rotate" href="/server/database/migrations">
    Version your database schema with up/down migrations
  </Card>

  <Card title="Connection Pool" icon="water" href="/server/database/connection-pool">
    Handle concurrent requests with pooled connections
  </Card>
</CardGroup>