SQLite

Connect to a local SQLite file and get full CRUD through a direct better-sqlite3 driver connection. Ideal for development, testing, and embedded use cases.

import { createEngine } from '@superapp/backend'

const engine = createEngine({
  connections: {
    local: './data/app.db',
  },
})

Connection Config

SQLite connections are just file path strings:

Format	Description
`'./data/app.db'`	Relative path to the `.db` file
`'/absolute/path/app.db'`	Absolute path to the `.db` file

The file must exist before the engine starts. To create a new database, use the sqlite3 CLI or any SQLite tool.

How It Works

When the engine starts, it detects the file path and opens a connection using the better-sqlite3 driver. All tables in the SQLite file become available under the local namespace.

Capabilities

Operation	Supported
`findMany` / `findOne`	Yes
`create`	Yes
`update`	Yes
`delete`	Yes
`count` / `aggregate`	Yes
Transactions	Yes

Development Setup

SQLite is the fastest way to develop locally. Seed a database and point the engine at it:

sqlite3 ./data/dev.db <<'SQL'
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  amount DECIMAL(10,2) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  customer_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO orders (amount, status, customer_id) VALUES
  (99.99, 'active', 1),
  (149.50, 'active', 2),
  (29.00, 'pending', 1);
SQL

const engine = createEngine({
  connections: {
    main: './data/dev.db',
  },
})

Testing with In-Memory SQLite

For tests, create a temporary database before each test run:

import { createEngine } from '@superapp/backend'
import { mkdtempSync, cpSync } from 'fs'
import { join } from 'path'
import { tmpdir } from 'os'

function createTestEngine() {
  const dir = mkdtempSync(join(tmpdir(), 'superapp-test-'))
  cpSync('./fixtures/test.db', join(dir, 'test.db'))

  return createEngine({
    connections: {
      main: join(dir, 'test.db'),
    },
  })
}

Troubleshooting

Problem	Cause	Fix
`unable to open database file`	File not found	Check the path; it must exist before engine starts
`database is locked`	Concurrent writes from another process	Use WAL mode (`PRAGMA journal_mode=WAL`)
`read-only database`	File permissions	Ensure the process has write access to the file