# Database Capabilities

> The `db.capabilities` property exposes database feature support at runtime.

Use capabilities to write portable code that adapts to the underlying database.

## Usage

You can access capabilities directly on the database instance:

```ts
import { createDatabase } from "db0";
import sqlite from "db0/connectors/better-sqlite3";

const db = createDatabase(sqlite({}));

console.log(db.capabilities);
// { json: true, booleans: false, arrays: false, ... }

if (db.capabilities.booleans) {
  // Use a native boolean column
} else {
  // Fall back to an integer 0/1 column
}
```

Capabilities are derived from the connector's SQL dialect, with per-connector
overrides where a specific backend differs from its dialect (see the table below).

## Available Flags

| Flag           | Description                                                                      |
| -------------- | -------------------------------------------------------------------------------- |
| `json`         | The database has JSON support (a native type and/or JSON functions).             |
| `booleans`     | The database has a boolean column type (MySQL maps it to `TINYINT(1)`).          |
| `arrays`       | The database supports native array column types.                                 |
| `dates`        | The database supports native date/timestamp types.                               |
| `uuids`        | The database supports native UUID column types.                                  |
| `transactions` | Explicit `BEGIN`/`COMMIT` through `db.sql` opens a transaction (see note below). |

## Capabilities by Connector

> [!TIP]
> See [db-compat.onmax.me](https://db-compat.onmax.me) for a comprehensive database feature comparison.

<!-- automd:file src="./_capabilities-table.md" -->

<!-- Auto-generated by scripts/gen-capabilities-docs.ts. Do not edit manually. -->

| Connector                        | JSON | Bool | Array | Date | UUID | Tx  |
| :------------------------------- | :--: | :--: | :---: | :--: | :--: | :-: |
| better-sqlite3                   |  ✓   |  —   |   —   |  —   |  —   |  ✓  |
| bun-sqlite                       |  ✓   |  —   |   —   |  —   |  —   |  ✓  |
| cloudflare-d1                    |  ✓   |  —   |   —   |  —   |  —   |  —  |
| cloudflare-hyperdrive-mysql      |  ✓   |  ✓   |   —   |  ✓   |  —   |  ✓  |
| cloudflare-hyperdrive-postgresql |  ✓   |  ✓   |   ✓   |  ✓   |  ✓   |  ✓  |
| libsql-core                      |  ✓   |  —   |   —   |  —   |  —   |  ✓  |
| libsql-http                      |  ✓   |  —   |   —   |  —   |  —   |  —  |
| libsql-node                      |  ✓   |  —   |   —   |  —   |  —   |  ✓  |
| libsql-web                       |  ✓   |  —   |   —   |  —   |  —   |  —  |
| mysql2                           |  ✓   |  ✓   |   —   |  ✓   |  —   |  ✓  |
| neon                             |  ✓   |  ✓   |   ✓   |  ✓   |  ✓   |  ✓  |
| node-sqlite                      |  ✓   |  —   |   —   |  —   |  —   |  ✓  |
| pglite                           |  ✓   |  ✓   |   ✓   |  ✓   |  ✓   |  ✓  |
| planetscale                      |  ✓   |  ✓   |   —   |  ✓   |  —   |  —  |
| postgresql                       |  ✓   |  ✓   |   ✓   |  ✓   |  ✓   |  ✓  |
| sqlite3                          |  ✓   |  —   |   —   |  —   |  —   |  ✓  |

<!-- /automd -->

> [!NOTE]
> `transactions` describes whether `BEGIN`/`COMMIT` sent through `db.sql` actually
> open a transaction. It is a property of the driver's session model rather than of the SQL
> dialect, so several connectors report `false` even though their database engine supports
> transactions:
>
> - [Cloudflare D1](/connectors/cloudflare) rejects `BEGIN`/`COMMIT` outright; only implicit
>   transactions via `D1Database.batch()` are available.
> - [PlanetScale](/connectors/planetscale) opens a new HTTP session per query, so consecutive
>   statements never share a transaction. Use `Client.transaction()` on the underlying client.
> - [libsql-http and libsql-web](/connectors/libsql) open and close a Hrana stream within a
>   single request per query. Use `client.transaction()` on the underlying client.
>
> `libsql-node` and `libsql-core` report `true` because they are commonly used against a local
> file, where a single connection is held open. Pointed at a remote `libsql:`/`http:` URL they
> behave like `libsql-http`, and this flag cannot detect that statically — reach for
> `client.transaction()` if you are unsure.

## Use Cases

### Conditional Feature Usage

You can adapt your queries to what the database supports:

```ts
import type { Database } from "db0";

function storeList(db: Database, items: string[]) {
  const value = db.capabilities.arrays
    ? `{${items.join(",")}}` // PostgreSQL array literal
    : JSON.stringify(items);
  return db.sql`INSERT INTO data (items) VALUES (${value})`;
}
```

> [!NOTE]
> Values interpolated into `db.sql` must be primitives (`string`,
> `number`, `boolean`, `null` or `undefined`), so arrays and objects have to be serialized
> to a value the target database understands.

### Runtime Validation

You can validate that the database meets your application's requirements:

```ts
import type { Database } from "db0";

function initDatabase(db: Database) {
  if (!db.capabilities.transactions) {
    throw new Error("This application requires transaction support");
  }
}
```

### Feature Detection in Libraries

You can build database-agnostic libraries that adapt automatically:

```ts
import type { Database } from "db0";

export function createRepository(db: Database) {
  return {
    saveFlag(id: string, enabled: boolean) {
      const value = db.capabilities.booleans ? enabled : Number(enabled);
      return db.sql`UPDATE items SET enabled = ${value} WHERE id = ${id}`;
    },
  };
}
```