sqljs

# Skill: sqljs

## What this skill knows

This skill covers sql.js v1: SQLite compiled to WebAssembly for use in the browser and Node.js. It covers initialization, database creation, query execution, schema inspection, and common patterns.

## Installation

```bash
pnpm add sql.js
pnpm add -D @types/sql.js
```

## Browser initialization

sql.js requires the WASM binary. The easiest setup is to serve the binary from `public/` and point `locateFile` at it:

```ts
import initSqlJs from 'sql.js';

// Fetch WASM from /sql-wasm.wasm (copied to public/)
const SQL = await initSqlJs({
  locateFile: (file) => `/${file}`,
});
```

For Vite, use `vite-plugin-wasm` or manually copy `node_modules/sql.js/dist/sql-wasm.wasm` to `public/`.

## Creating a database

```ts
// Empty in-memory database
const db = new SQL.Database();

// From an existing database file (Uint8Array)
const response = await fetch('/my-database.db');
const buffer = await response.arrayBuffer();
const db = new SQL.Database(new Uint8Array(buffer));
```

## Running queries

### exec - for SELECT (returns results)

```ts
const results = db.exec('SELECT * FROM customers');
// results: Array<{ columns: string[], values: any[][] }>

if (results.length === 0) {
  // Query returned no result sets (e.g., DDL)
}

const { columns, values } = results[0];
// columns: ['id', 'name', 'email', 'city']
// values: [[1, 'Alice', 'alice@example.com', 'Austin'], ...]
```

`exec` can run multiple statements separated by `;`. Returns one object per SELECT.

### run - for DDL/DML (no results)

```ts
db.run('CREATE TABLE foo (id INTEGER PRIMARY KEY, bar TEXT)');
db.run("INSERT INTO foo (bar) VALUES (?)", ['hello']);
db.run("UPDATE foo SET bar = ? WHERE id = ?", ['world', 1]);
```

The second argument to `run` is an array of bind parameters.

### Parameterized queries

```ts
// Positional parameters
db.run('SELECT * FROM users WHERE id = ?', [42]);

// Named parameters
db.run('SELECT * FROM users WHERE id = :id', { ':id': 42 });
```

Always use parameterized queries when inserting user input. Do not concatenate user strings into SQL.

## Prepared statements

For repeated queries, use a prepared statement:

```ts
const stmt = db.prepare('SELECT * FROM orders WHERE customer_id = ?');

stmt.bind([customerId]);
while (stmt.step()) {
  const row = stmt.getAsObject();
  console.log(row.id, row.total_amount);
}
stmt.free();
```

- `stmt.bind(params)` binds parameters.
- `stmt.step()` returns `true` while there are more rows.
- `stmt.getAsObject()` returns the current row as `{ columnName: value }`.
- `stmt.free()` releases the statement's memory. Always call this when done.

## Transaction

```ts
db.run('BEGIN TRANSACTION');
try {
  db.run("INSERT INTO orders ...");
  db.run("UPDATE inventory ...");
  db.run('COMMIT');
} catch (err) {
  db.run('ROLLBACK');
  throw err;
}
```

## Error handling

`db.exec` and `db.run` throw a JavaScript `Error` when the SQL is invalid:

```ts
try {
  db.exec('SELECT * FORM users'); // typo: FORM instead of FROM
} catch (err) {
  console.error(err.message);
  // "near "FORM": syntax error"
}
```

Always wrap `exec` in try/catch in application code.

## Schema introspection

```ts
// List all tables
const tableResult = db.exec(
  "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
);
const tableNames = tableResult[0]?.values.map(([name]) => name) ?? [];

// Get column info for a table
const infoResult = db.exec(`PRAGMA table_info(customers)`);
// columns: cid, name, type, notnull, dflt_value, pk
const columns = infoResult[0]?.values.map(([cid, name, type, notNull, dflt, pk]) => ({
  cid: Number(cid),
  name: String(name),
  type: String(type),
  notNull: Boolean(notNull),
  defaultValue: dflt,
  primaryKey: Number(pk) > 0,
})) ?? [];

// Row count
const countResult = db.exec(`SELECT COUNT(*) FROM customers`);
const count = Number(countResult[0].values[0][0]);
```

## Exporting the database

```ts
// Export as Uint8Array (can be saved as a .db file)
const data = db.export();
const blob = new Blob([data], { type: 'application/octet-stream' });
const url = URL.createObjectURL(blob);
```

## Importing data from SQL string

```ts
// Execute a .sql dump file
const sqlDump = await fetch('/datasets/ecommerce.sql').then(r => r.text());
db.run(sqlDump);
```

## Performance characteristics

- sql.js runs in the main thread by default. For large queries (> 100ms), move to a Web Worker.
- Each `exec` call parses and compiles the SQL. Use `prepare` for queries run many times.
- `db.exec` with multiple rows is faster than multiple individual `db.run` calls. Batch INSERTs in a transaction.
- The WASM module is ~1.5 MB (gzip: ~650 KB). Load it once on app init.

## Web Worker pattern (for heavy queries)

```ts
// worker.ts
import initSqlJs from 'sql.js';

let db: any;

self.onmessage = async ({ data: { type, sql, params } }) => {
  if (type === 'init') {
    const SQL = await initSqlJs({ locateFile: f => `/${f}` });
    db = new SQL.Database();
    self.postMessage({ type: 'ready' });
  }
  if (type === 'exec') {
    try {
      const results = db.exec(sql);
      self.postMessage({ type: 'result', results });
    } catch (err: any) {
      self.postMessage({ type: 'error', message: err.message });
    }
  }
};

// main thread
const worker = new Worker(new URL('./worker.ts', import.meta.url));
worker.postMessage({ type: 'init' });
worker.onmessage = ({ data }) => {
  if (data.type === 'ready') {
    worker.postMessage({ type: 'exec', sql: 'SELECT * FROM customers' });
  }
};
```

For the sql-tutorial-engine (max ~2400 rows), the main thread is fast enough. Use a Web Worker only if queries start blocking the UI.

## NULL handling

sql.js returns JavaScript `null` for SQL NULL values in query results. Display them as the string `"NULL"` with muted styling rather than rendering an empty cell.

## Node.js usage

In Node.js, sql.js works without a WASM file - it bundles the WASM inline:

```ts
import initSqlJs from 'sql.js';
const SQL = await initSqlJs();
const db = new SQL.Database();
```

## Type definitions

```ts
import type { Database, Statement, QueryExecResult } from 'sql.js';

// QueryExecResult shape:
interface QueryExecResult {
  columns: string[];
  values: SqlValue[][];
}

type SqlValue = string | number | null | Uint8Array;
```

## Common mistakes

- Calling `new SQL.Database()` before `await initSqlJs()` - `SQL` will be undefined.
- Using `db.exec` for DML and expecting a row count - `exec` always returns result sets. For DML, use `db.run` and `db.getRowsModified()`.
- Not calling `stmt.free()` after using a prepared statement - memory leak.
- Storing `db` in React component state - this causes React re-render issues. Store it in `useRef` or a Zustand store that is not compared by value.
- Forgetting `db.run('COMMIT')` after a `BEGIN TRANSACTION` - the transaction stays open and blocks subsequent writes.