SQLite
Persists walkerOS events to SQLite. One destination, two drivers behind a single interface: better-sqlite3 for local files (and :memory:), and @libsql/client for remote Turso / libSQL / sqld over HTTP or WebSocket. Driver selection is URL-driven; both SDKs are optional peer dependencies so you install only the one you need.
SQLite is a server destination in the walkerOS flow:
Receives events server-side from the collector, serializes them into a canonical row, and inserts them into a SQLite-compatible database. Good fit for single-host deployments (local file), embedded analytics (in-memory), and edge-deployed Turso databases.
Installation
# Local file / :memory:
npm install @walkeros/server-destination-sqlite better-sqlite3
# Remote Turso / libSQL
npm install @walkeros/server-destination-sqlite @libsql/client- Integrated
- Bundled
import { startFlow } from '@walkeros/collector';
import { destinationSQLite } from '@walkeros/server-destination-sqlite';
await startFlow({
destinations: {
sqlite: {
code: destinationSQLite,
config: {
settings: {
sqlite: {
url: './events.db',
schema: 'auto',
},
},
},
},
},
});A complete flow.json with this destination:
{
"version": 4,
"flows": {
"default": {
"config": { "platform": "server" },
"destinations": {
"sqlite": {
"package": "@walkeros/server-destination-sqlite",
"config": {
"settings": {
"sqlite": { "url": "./events.db", "schema": "auto" }
}
}
}
}
}
}
}The CLI does not install optional peer dependencies, so the bundle contains no
SQLite driver. The destination loads better-sqlite3 or @libsql/client with
Node's module resolution from the bundle's folder, so install the driver there.
Without a driver, init fails with SQLite driver load failed.
schema: "auto" creates the table at the first push, with the columns listed in Table columns. Without it, the table has to exist before the first event (see Setup).
Configuration
This destination uses the standard destination config wrapper (consent, data, env, id, ...). For the shared fields see destination configuration. Package-specific fields live under config.settings and are listed below.
Settings
| Property | Type | Description | More |
|---|---|---|---|
sqlite | sqlite | SQLite / libSQL configuration | |
url | string | SQLite connection URL. libsql://, http(s)://, ws(s):// route to libSQL/Turso. Anything else is treated as a local file path via better-sqlite3. Use ':memory:' for an ephemeral in-memory database. | |
authToken | string | libSQL / Turso auth token. Ignored for better-sqlite3 (local) connections. | |
table | string | Target table name. Defaults to "events". | |
schema | 'auto' | 'manual' | "auto" creates the table (and each rule table) at the first push, with the columns every insert writes. "manual" skips the table check: the table is yours, and init fails without a config.mapping. Either value logs a WARN at init. |
Mapping
Per-event rules under config.mapping. For the standard rule fields (consent, condition, data, batch, name, policy) see mapping.
| Property | Type | Description | More |
|---|---|---|---|
table | string | Override target table name for this rule. Takes precedence over settings.sqlite.table. |
Examples
Custom table
A destination-level table setting inserts events into a custom SQLite table with the same column layout.
{
"name": "form submit",
"data": {
"type": "contact"
},
"context": {
"dev": [
"test",
1
]
},
"globals": {},
"custom": {
"completely": "random"
},
"user": {
"session": "sess-99",
"id": ""
},
"nested": [
{
"entity": "child",
"data": {
"is": "subordinated"
}
}
],
"consent": {},
"id": "evt-2",
"trigger": "test",
"entity": "form",
"action": "submit",
"timestamp": 1700000101000,
"timing": 3.14,
"source": {
"count": 1,
"trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
"type": "browser",
"platform": "web",
"url": "https://example.com/contact"
}
}client.runInsert([
1700000101000,
"evt-2",
"form submit",
"form",
"submit",
"sess-99",
"",
"https://example.com/contact",
"",
"",
"{\"type\":\"contact\"}",
"{}",
"{}"
])Default insert
A walker event is inserted into the default events table with canonical columns and JSON-encoded sections.
{
"name": "page view",
"data": {
"title": "Home"
},
"context": {
"dev": [
"test",
1
]
},
"globals": {
"env": "prod"
},
"custom": {
"completely": "random"
},
"user": {
"session": "sess-1",
"id": "user-42"
},
"nested": [
{
"entity": "child",
"data": {
"is": "subordinated"
}
}
],
"consent": {
"analytics": true
},
"id": "evt-1",
"trigger": "load",
"entity": "page",
"action": "view",
"timestamp": 1700000100000,
"timing": 3.14,
"source": {
"count": 1,
"trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
"type": "browser",
"platform": "web",
"url": "https://example.com/",
"referrer": "https://example.com/prev"
}
}client.runInsert([
1700000100000,
"evt-1",
"page view",
"page",
"view",
"sess-1",
"user-42",
"https://example.com/",
"Home",
"https://example.com/prev",
"{\"title\":\"Home\"}",
"{\"env\":\"prod\"}",
"{\"analytics\":true}"
])Order insert
An order complete is inserted with numeric data serialized as JSON in the data column.
{
"name": "order complete",
"data": {
"id": "ORD-1",
"value": 99
},
"context": {
"shopping": [
"complete",
0
]
},
"globals": {},
"custom": {
"completely": "random"
},
"user": {
"session": "",
"id": ""
},
"nested": [
{
"entity": "product",
"data": {
"id": "ers",
"name": "Everyday Ruck Snack",
"color": "black",
"size": "l",
"price": 420
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
},
{
"entity": "product",
"data": {
"id": "cc",
"name": "Cool Cap",
"size": "one size",
"price": 42
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
},
{
"entity": "gift",
"data": {
"name": "Surprise"
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
}
],
"consent": {},
"id": "evt-3",
"trigger": "load",
"entity": "order",
"action": "complete",
"timestamp": 1700000102000,
"timing": 3.14,
"source": {
"count": 1,
"trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
"type": "collector",
"schema": "4"
}
}client.runInsert([
1700000102000,
"evt-3",
"order complete",
"order",
"complete",
"",
"",
"",
"",
"",
"{\"id\":\"ORD-1\",\"value\":99}",
"{}",
"{}"
])Table override
A mapping rule overrides the target table so specific events are inserted into a dedicated SQLite table.
{
"name": "order complete",
"data": {
"id": "ORD-2",
"value": 42
},
"context": {
"shopping": [
"complete",
0
]
},
"globals": {},
"custom": {
"completely": "random"
},
"user": {
"session": "",
"id": ""
},
"nested": [
{
"entity": "product",
"data": {
"id": "ers",
"name": "Everyday Ruck Snack",
"color": "black",
"size": "l",
"price": 420
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
},
{
"entity": "product",
"data": {
"id": "cc",
"name": "Cool Cap",
"size": "one size",
"price": 42
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
},
{
"entity": "gift",
"data": {
"name": "Surprise"
},
"context": {
"shopping": [
"complete",
0
]
},
"nested": []
}
],
"consent": {},
"id": "evt-4",
"trigger": "load",
"entity": "order",
"action": "complete",
"timestamp": 1700000103000,
"timing": 3.14,
"source": {
"count": 1,
"trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
"type": "collector",
"schema": "4"
}
}{
"settings": {
"table": "orders"
}
}client.runInsert([
1700000103000,
"evt-4",
"order complete",
"order",
"complete",
"",
"",
"",
"",
"",
"{\"id\":\"ORD-2\",\"value\":42}",
"{}",
"{}"
])The destination opens the connection during init(). The table check and the prepared INSERT run at the first push, so a flow starts without querying the database. With schema unset, a missing table fails that push with a config error that names walkeros setup destination.sqlite (for a rule's table, it names the table); with schema: "manual" the check is skipped and the push fails with SQLite prepare failed. A failed INSERT fails the event with a transport error and the event goes to the dead-letter queue (when delivery fails); a constraint violation (a duplicate under a unique index, a NOT NULL column) is marked not retryable. On destroy() the connection is closed cleanly. User-provided clients (wired via env.client or settings.sqlite._client) are left untouched.
Table columns
Each event is one INSERT into these columns. The table that schema: "auto" creates has them, plus id INTEGER PRIMARY KEY AUTOINCREMENT and created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
| Column | Type | Value |
|---|---|---|
timestamp | INTEGER | event.timestamp, epoch milliseconds |
event_id | TEXT | event.id |
name | TEXT | event.name |
entity | TEXT | event.entity |
action | TEXT | event.action |
session_id | TEXT | event.user.session |
user_id | TEXT | event.user.id |
page_url | TEXT | event.source.url |
page_title | TEXT | event.data.title |
referrer_url | TEXT | event.source.referrer |
data | TEXT | event.data as JSON |
globals | TEXT | event.globals as JSON |
consent | TEXT | event.consent as JSON |
A missing text value is an empty string, a missing data, globals or consent is {}, and a missing timestamp is the time of the insert. context, custom, nested, trigger, timing, the other user fields and the other source fields are not stored, and a mapping's data does not change the row.
Setup
walkeros setup destination.sqlite applies pragmas and creates the table and
its indexes when config.setup is true or an object. When it is omitted or
false, the command provisions nothing.
By default setup creates one column per event field (name, data,
context, globals, custom, user, nested, consent, id, trigger,
entity, action, timestamp, timing, source), while each event is
inserted into the columns in Table columns. Every push into
that table fails with SQLite prepare failed (the driver reports
table events has no column named event_id). Use schema: "auto", or declare
the inserted columns in setup.schema.
"setup": {
"schema": [
{ "name": "timestamp", "type": "INTEGER" },
{ "name": "event_id", "type": "TEXT" },
{ "name": "name", "type": "TEXT" },
{ "name": "entity", "type": "TEXT" },
{ "name": "action", "type": "TEXT" },
{ "name": "session_id", "type": "TEXT" },
{ "name": "user_id", "type": "TEXT" },
{ "name": "page_url", "type": "TEXT" },
{ "name": "page_title", "type": "TEXT" },
{ "name": "referrer_url", "type": "TEXT" },
{ "name": "data", "type": "TEXT" },
{ "name": "globals", "type": "TEXT" },
{ "name": "consent", "type": "TEXT" }
],
"indexes": [{ "name": "idx_events_name", "columns": ["name"] }]
}Setup applies four pragmas before it creates the table, each overridable in
setup.pragmas (for example { "journal_mode": "DELETE" }):
journal_mode = WAL(better concurrent reads)synchronous = NORMAL(good durability vs. perf balance)foreign_keys = ONtemp_store = MEMORY
Setup is idempotent: it runs CREATE TABLE IF NOT EXISTS and
CREATE INDEX IF NOT EXISTS, so re-running it against a populated database
changes nothing. A column that is missing or has another type than declared is
logged as a setup.drift warning with the field, the declared and the actual
type. Setup never runs ALTER TABLE and never recreates a table.
Use mapping.settings.table to route specific events to a dedicated table (for
example orders to orders, identities to identities). Setup creates only
settings.sqlite.table; a rule's table has to exist, or schema: "auto"
creates it at its first push. A missing rule table fails its events with a
config error.
settings.sqlite.schema
"auto"creates the table, and each rule table, at its first push with the columns in Table columns."manual"skips the table check: the table is yours, and init fails with aconfigerror whenconfig.mappingis not set.
Either value logs a WARN at init that points to config.setup.
Drivers
Local, better-sqlite3
Sync native driver, fastest option for single-host deployments. The URL is treated as a filesystem path; :memory: works too. All four default pragmas are honored.
{
"sqlite": {
"url": "/var/lib/walkeros/events.db"
}
}Remote, @libsql/client
Async HTTP/WSS driver for Turso, self-hosted sqld, or any libSQL-compatible endpoint. Auth via authToken. Setup sends the same four pragmas over this driver. Turso Cloud manages journaling itself and does not allow changing the journal mode (see Turso Cloud limitations).
{
"sqlite": {
"url": "libsql://my-db.turso.io",
"authToken": "$secret.TURSO_TOKEN"
}
}URL prefixes libsql://, http://, https://, ws://, wss:// route to the libSQL driver. Anything else (bare paths, :memory:) routes to better-sqlite3.
Limitations
- Each event is one
INSERT. The destination has nopushBatch, so abatchsetting has no effect, and the collector logs an ERROR line that names it. - Nothing resends a failed
INSERT: the event fails and goes to the dead-letter queue. A failed table check or prepare is tried again at the next push.