# SQLite

> Source: https://docs.clonepartner.com/connectors/sqlite/

<!-- sources: src/connectors/sqlite.ts, src/connectors/readonly-sql.ts, references/envoy/connectors.md -->

# SQLite

The `sqlite` connector queries and writes a local SQLite database file, and doubles as the standard mapping database and cursor store for sync tasks.

## When to use it

Use `sqlite` for local state a task needs to keep between runs — ID mappings, dedup hashes, staging tables, cursors — or as a lightweight relational source or destination. For shared or concurrent access across machines, prefer [PostgreSQL](/connectors/postgres/) or [MySQL](/connectors/mysql/).

## Configuration

| Key | Type | Required | Default | Description |
|---|---|---|---|---|
| `path` | string | Yes | — | Path to SQLite database file |

## Methods

| Method | Arguments | Returns | Behavior |
|---|---|---|---|
| `find` | `table` (required), `filter`, `select`, `limit`, `offset`, `order_by` | array | Streams matching rows one at a time via a prepared-statement iterator |
| `find_one` | `table` (required), `filter`, `select` | object | Returns the first matching row |
| `insert_one` | `table` (required), `data` (required) | object | Inserts a single record |
| `upsert` | `table` (required), `data` (required), `conflict_columns` (required), `update_columns` | object | Inserts or updates on conflict with `conflict_columns` |
| `update` | `table` (required), `data` (required), `filter` (required) | object | Updates rows matching the filter; a non-empty filter is mandatory |
| `delete` | `table` (required), `filter` | object | Deletes rows matching the filter |
| `execute` | `statements` (required) | object | Runs raw SQL; statements are split on `;` and the count is returned as `statements_executed` |
| `select` | `sql` (required), `params` | array | Runs a read-only `SELECT` on a separate read-only database connection |

`filter` uses the shared JSON filter syntax: top-level keys are ANDed equality conditions, operator objects support `gt`, `gte`, `lt`, `lte`, `ne`, `in`, `nin`, `like`, `regex`, and `exists`, and `or`/`and` keys nest condition lists.

## Example

```yaml
name: stage_orders
connectors:
  staging:
    type: sqlite
steps:
  - name: read_pending
    type: call_method
    config: |
      {
        "connector": "staging",
        "method": "find",
        "args": { "table": "orders", "filter": { "status": "pending" } }
      }
```

The matching `connectors.yaml` entry for CLI runs:

```yaml
staging:
  type: sqlite
  config:
    path: /path/to/staging.db
```

## Behavior notes

- `select` rejects anything that is not a `SELECT` or `WITH ... SELECT` statement (`select method only allows SELECT or WITH ... SELECT statements`) and runs on a read-only connection, so dashboards cannot mutate data through it.
- The connector implements the mapping-DB interface: on first use it creates a `resource_mapping` table for source-to-destination ID mappings and hashes, used by `sync_record` and related steps. See [Idempotent sync and the mapping DB](/building/idempotent-sync/).
- It also implements the cursor store (an `envoy_cursors` table) for tasks configured with `cursor_store`.
- `update` refuses to run with an empty filter, preventing accidental full-table updates.

## Related

- [Connectors overview](/connectors/) — all compiled connector types
- [Idempotent sync and the mapping DB](/building/idempotent-sync/) — how the mapping table is used
- [PostgreSQL](/connectors/postgres/) — the same method set on a server database