Documentation by ClonePartner

Start typing to search.

Connector Reference

SQLite

View Markdown

Configure the sqlite connector to query and write a local SQLite database file, and use it as a mapping DB and cursor store.

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 or 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

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:

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.
  • 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.