Vulcan

https://vlcn.io/docs
Admin
Vulcan is a database extension and platform SDKs built on CR-SQLite, enabling multi-master, P2P data...
Tokens:18,588
Snippets:126
Trust Score:7.7
Update:1 year ago
Show doc for...
Tokens:
Raw
### Run and Install Vulcan App Dependencies

Source: https://vlcn.io/docs/cr-sqlite/js/first-app

Commands to navigate into the newly scaffolded Vulcan CR-SQLite project directory, install its dependencies (using NPM, Yarn, or PNPM), and start the development server. This prepares the application for local development and testing.

```bash
cd your-app-name
npm install # or yarn or pnpm
npm run dev
```

--------------------------------

### CR-SQLite Example: Full Workflow

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This comprehensive SQL script demonstrates the full lifecycle of using the cr-sqlite extension. It covers loading the extension, creating tables, converting them to CRRs, inserting data, querying changes, merging changes from a peer, and finally, finalizing the extension.

```sql
-- load the extension if it is not statically linked
-- this should always be the first operation on the connection
.load crsqlite
.mode column
-- create tables as normal
create table foo (a primary key NOT NULL, b);
create table baz (a primary key NOT NULL, b, c, d);

-- update those tables to be crrs / crdts
select crsql_as_crr('foo');
select crsql_as_crr('baz');

-- insert some data / interact with tables as normal
insert into foo (a,b) values (1,2);
insert into baz (a,b,c,d) values ('a', 'woo', 'doo', 'daa');


-- ask for a record of what has changed
select "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" from crsql_changes;

-- merge changes from a peer
insert into crsql_changes
  ("table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq")
  values
  ('foo', x'010905', 'b', 'thing', 5, 5, X'7096E2D505314699A59C95FABA14ABB5', 1, 0);
insert into crsql_changes ("table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq")
  values
  ('baz', x'010b0161', 'b', 123, 101, 233, X'7096E2D505314699A59C95FABA14ABB5', 1, 0);

-- check that peer's changes were applied
-- select * from foo;
-- select * from baz;

-- tear down the extension before closing the connection
-- https://sqlite.org/forum/forumpost/c94f943821
select crsql_finalize();
```

--------------------------------

### Live Example: Setup rx-tbl with Database

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This JavaScript code imports the rx-tbl package and initializes it with the previously created SQLite database connection, setting up the reactivity layer.

```javascript
use db;
const tblrx = await import('https://esm.sh/@vlcn.io/rx-tbl@0.15.0');
const rx = tblrx.default(db);
provide rx;
```

--------------------------------

### Reactivity Layer Setup

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

Install and import the rx-tbl package to enable reactivity for your database.

```APIDOC
## Install
```
npm install @vlcn.io/rx-tbl
```

## Import
```
import tblrx from "@vlcn.io/rx-tbl";
```
```

--------------------------------

### Install @vlcn.io/rx-tbl Package

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This command installs the rx-tbl package, which provides a JavaScript interface for subscribing to database changes. It's a necessary step before using the reactivity features.

```bash
npm install @vlcn.io/rx-tbl
```

--------------------------------

### Install CR-SQLite WASM Package

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Installs the CR-SQLite WASM package using npm. This is the first step to using the library in a browser environment.

```bash
> npm install @vlcn.io/crsqlite-wasm
```

--------------------------------

### Live Example: Initialize CR-SQLite WASM and Database

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This JavaScript snippet shows how to import and initialize the CR-SQLite WebAssembly module, and then open an in-memory database and create a 'todo' table.

```javascript
const initWasm = await import('https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0');
const sqlite = await initWasm.default(
() => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm"
);
  

const nanoid=(t=21)=>crypto.getRandomValues(new Uint8Array(t)).reduce(((t,e)=>t+=(e&=63)<36?e.toString(36):e<62?(e-26).toString(36).toUpperCase():e>62?"-":"_"),"");
provide nanoid;
  

const db = await sqlite.open(":memory:");
await db.exec(`CREATE TABLE IF NOT EXISTS todo (id PRIMARY KEY NOT NULL, content, complete)`);
provide db;
```

--------------------------------

### Begin Schema Migration with CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL command initiates a schema migration process for a specified table. It should be followed by ALTER TABLE statements and then `crsql_commit_alter` to finalize the changes.

```sql
-- begin altering `table_name`
SELECT crsql_begin_alter(:table_name);
```

--------------------------------

### Example: Preparing and Finalizing a Statement (TypeScript)

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

This example demonstrates how to prepare a SQL statement, execute it multiple times, and then finalize the statement to release its resources. It shows the typical workflow for using prepared statements.

```typescript
const stmt = await db.prepare("INSERT INTO users (id, name) VALUES (?, ?)");
await stmt.run(null, shortuuid(), "Alice");
await stmt.run(null, shortuuid(), "Bob");
await stmt.finalize(null);
```

--------------------------------

### Install CR-SQLite React Packages

Source: https://vlcn.io/docs/cr-sqlite/js/react

Installs the necessary CR-SQLite React integration and reactive table packages using npm. These packages enable seamless data synchronization and querying within React components.

```bash
> npm install @vlcn.io/react @vlcn.io/rx-tbl
```

--------------------------------

### Live Example: Listen for Changes on 'todo' Table

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This JavaScript snippet demonstrates how to use rx.onRange to listen for any changes (insert, update, delete) made to the 'todo' table and log them to the console.

```javascript
use rx;
const disposable = rx.onRange(["todo"], (updateTypes) => {
console.log("todo table changed", updateTypes);
});
provide disposable;
```

--------------------------------

### Setup WebSocket Sync Server for Real-time Synchronization

Source: https://context7.com/context7/vlcn_io/llms.txt

This snippet demonstrates setting up a WebSocket sync server using '@vlcn.io/ws-server' with Express and Node.js. It configures the server to handle database synchronization, manage database and schema folders, and optionally authenticate clients. The `attachWebsocketServer` function integrates the sync functionality into an existing HTTP server.

```javascript
import express from "express";
import * as http from "http";
import { attachWebsocketServer } from "@vlcn.io/ws-server";

const PORT = parseInt(process.env.PORT || "8080");
const app = express();
const server = http.createServer(app);

// Attach WebSocket sync server
attachWebsocketServer(server, {
  dbFolder: "./dbs",           // Where to store server-side databases
  schemaFolder: "./src/schemas", // Schema definitions
  pathPattern: /\/sync/,       // WebSocket endpoint pattern
  // Optional: authenticate clients
  authenticate: async (token) => {
    // Verify JWT or session token
    const user = await verifyToken(token);
    return user ? { userId: user.id } : null;
  }
});

server.listen(PORT, () => {
  console.log(`Sync server listening on http://localhost:${PORT}`);
});

```

--------------------------------

### Finalize CR-SQLite Extension Before Closing Connection

Source: https://vlcn.io/docs/cr-sqlite/installation

This example shows the recommended way to finalize the CR-SQLite extension before closing a database connection. Executing `SELECT crsql_finalize();` allows the extension to perform necessary cleanup, preventing potential exceptions during connection closure. The JavaScript wrapper handles this automatically.

```sql
conn.exec('SELECT crsql_finalize();');
conn.close();
```

--------------------------------

### Upgrade Tables to CRRs with CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL function upgrades an existing table to a conflict-free replicated relation (CRR). Ensure the cr-sqlite extension is loaded before using this function.

```sql
SELECT crsql_as_crr('table_name');
```

--------------------------------

### Scaffold Vulcan App with Yarn

Source: https://vlcn.io/docs/cr-sqlite/js/first-app

Creates a new Vulcan CR-SQLite application using Yarn. This command is an alternative to NPM for initializing a new project, providing a similar setup for a SPA with a SQLite database.

```bash
yarn create @vlcn.io your-app-name
```

--------------------------------

### Scaffold Vulcan App with PNPM

Source: https://vlcn.io/docs/cr-sqlite/js/first-app

Creates a new Vulcan CR-SQLite application using PNPM. This command offers another package manager option for initializing a project, ensuring a consistent SPA and SQLite database setup.

```bash
pnpm create @vlcn.io your-app-name
```

--------------------------------

### Create and Setup CR-SQLite Tables

Source: https://context7.com/context7/vlcn_io/llms.txt

Demonstrates how to create a new table with specified columns and then register it as a Conflict-free Replicated Data Type (CRDT) using `crsql_as_crr`. This ensures the table can be synchronized across multiple sites. Dependencies include an active database connection (`conn`) and its cursor (`cursor`).

```python
cursor = conn.cursor()
cusror.execute("""
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT
  )
""")
cursor.execute("SELECT crsql_as_crr('users')")
```

--------------------------------

### Finalize CR-SQLite Extension

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL function should be called before closing a database connection that has loaded the cr-sqlite extension. It cleans up any resources used by the extension.

```sql
select crsql_finalize();
```

--------------------------------

### Connect Browser Clients for Automatic Synchronization

Source: https://context7.com/context7/vlcn_io/llms.txt

This code illustrates how to set up client-side synchronization with a VLCN.IO WebSocket sync server. It shows two methods: using the `useSync` React hook for seamless integration within React components, and using the `startSync` function for manual setup outside of React. Both methods require a database name, server endpoint, room identifier, and optionally an authentication token.

```javascript
import { useSync } from "@vlcn.io/react";
import SyncWorker from "./sync-worker.js?worker";

const worker = new SyncWorker();

function App() {
  const dbname = "myapp.db";
  const room = "user-123-devices"; // Sync room identifier

  // Setup sync in React component
  useSync({
    dbname,
    endpoint: "ws://localhost:8080/sync",
    room,
    worker,
    // Optional: send auth token
    token: "jwt-token-here"
  });

  return <TodoList ctx={ctx} />;
}

// Manual sync without React hook
import { startSync } from "@vlcn.io/websocket-client";

const config = {
  db,
  endpoint: "ws://localhost:8080/sync",
  room: "user-123-devices",
  worker: new Worker("sync-worker.js")
};

const stopSync = startSync(config);

// Later: stop syncing
stopSync();

```

--------------------------------

### Live Example: Insert Data into 'todo' Table

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This JavaScript code inserts a new record into the 'todo' table. This action will trigger the 'onRange' listener set up previously, logging the change to the console.

```javascript
use db;
await db.exec(
`INSERT INTO todo (id, content, complete) VALUES (?, ?, ?)`,
[nanoid(), "Bleh", 0],
);
```

--------------------------------

### Commit Schema Migration with CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL command finalizes schema alterations for a table that were previously initiated with `crsql_begin_alter`. Use this after executing all necessary `ALTER TABLE` statements.

```sql
SELECT crsql_commit_alter(:table_name);
```

--------------------------------

### Query All Changes Excluding Specific Site ID after Version v using CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL query fetches all changes from `crsql_changes` with a database version greater than 'v', excluding changes originating from a specific site (`some_site`). This helps in synchronizing changes while ignoring already processed or irrelevant data.

```sql
SELECT "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" FROM crsql_changes WHERE db_version > :v AND site_id IS NOT :some_site
```

--------------------------------

### Open Persistent Database

Source: https://vlcn.io/docs/cr-sqlite/js/persistence

Opens a CR-SQLite database. Providing a filename like "guide-persistence2" creates a persistent database. Omitting the filename or using ":memory:" will result in an in-memory database that is lost when the page is closed. The example also demonstrates setting up a 'todo' table and closing the database on page unload.

```javascript
use sqlite;
const db = await sqlite.open("guide-persistence2");
window.onbeforeunload = () => {
return db.close();
};
await db.exec(
`CREATE TABLE IF NOT EXISTS todo (id primary key NOT NULL, "text", completed)`
);
provide db;
```

--------------------------------

### Load CR-SQLite Extension in Python

Source: https://vlcn.io/docs/cr-sqlite/installation

This Python code snippet demonstrates how to load the CR-SQLite extension into an active SQLite database connection. It requires the `sqlite3` module and assumes the extension file is accessible at the specified path. Ensure extension loading is enabled before calling `load_extension`.

```python
import sqlite3
 
conn = sqlite3.connect(db_file)
c.enable_load_extension(True)
conn.load_extension('path/to/crsqlite')
```

--------------------------------

### Complete Schema Alteration Example (SQL)

Source: https://vlcn.io/docs/cr-sqlite/api-methods/crsql_begin_alter

Demonstrates a typical workflow for altering a table's schema using `crsql_begin_alter`, performing the alteration, and then committing it with `crsql_commit_alter`.

```sql
SELECT crsql_begin_alter('foo');
ALTER TABLE foo ADD COLUMN c TEXT;
SELECT crsql_commit_alter('foo');
```

--------------------------------

### Query Local Changesets After Version v using CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL query retrieves changesets from the `crsql_changes` virtual table that have a database version greater than 'v' and are not associated with a specific site ID. This is useful for fetching local changes since a particular version.

```sql
SELECT "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" FROM crsql_changes WHERE db_version > :v AND site_id IS NULL
```

--------------------------------

### Example CRDT Table Definition (Custom Syntax)

Source: https://vlcn.io/docs/cr-sqlite/crdts/about

This snippet demonstrates a notional example of defining a table with CRDT types for its columns using a custom syntax. It specifies types like CLSet, COUNTER, PERITEXT, and LWW INTEGER for different columns.

```sql
CREATE CLSet post (
 id INTEGER PRIMARY KEY NOT NULL,
 views COUNTER,
 content PERITEXT,
 owner_id LWW INTEGER
);
```

--------------------------------

### Merge Changesets from Another Peer using CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/quickstart

This SQL INSERT statement is used to merge incoming changesets into the local `crsql_changes` table. The 'patches' should be the result of a SELECT query from another peer's `crsql_changes` table.

```sql
INSERT INTO crsql_changes VALUES [patches received from select on another peer]
```

--------------------------------

### Get Database Site ID and Version Information (SQL)

Source: https://context7.com/context7/vlcn_io/llms.txt

Retrieves the unique site identifier (a 16-byte UUID) and the current database version (a Lamport timestamp) for the local database instance. This information is essential for tracking and synchronizing data across different database replicas.

```sql
-- Get this database's unique site ID (16-byte UUID)
SELECT quote(crsql_site_id());
-- Returns: X'049C48EADF4440D7944ED9EC88B13EA5'

-- Get current database version (Lamport timestamp)
SELECT crsql_db_version();
-- Returns: 42

-- Get next version to be assigned
SELECT crsql_next_db_version();
-- Returns: 43
```

--------------------------------

### Insert and Select Data in CR-SQLite

Source: https://vlcn.io/docs/cr-sqlite/js/persistence

Demonstrates inserting data into a 'todo' table and then selecting all records. This snippet uses `nanoid()` (assuming it's defined elsewhere) to generate unique IDs. The `execO` method is used for selecting data, returning an array of objects. This is a basic example of data manipulation within a persistent CR-SQLite database.

```javascript
use db;
const items = ['Milk', 'Bread', 'Peas', 'Carrots', 'Lentils', 'Cake', 'Squirrels'];
await db.exec(
`INSERT INTO todo (id, "text", completed) VALUES (
'${nanoid()}',
'Buy ${items[(Math.random() * items.length) | 0]}',
0
)`
);
return await db.execO(`SELECT * FROM todo`);
```

--------------------------------

### Automatic CR-SQLite Schema Migration

Source: https://vlcn.io/docs/cr-sqlite/migrations

Shows how to perform automatic database schema migrations in CR-SQLite by providing the desired schema content. This feature, intended for development, automatically generates and applies `ALTER TABLE` and `CREATE INDEX` statements by diffing the current and desired schemas.

```sql
SELECT crsql_migrate('contents of your schema file');
```

--------------------------------

### CR-SQLite Table Definition with CRDT Types

Source: https://vlcn.io/docs/cr-sqlite/custom-syntax

This example demonstrates the proposed syntax for creating a table as a Conflict-free Replicated Data Type (CRDT) in CR-SQLite. It specifies the table's CRDT set type and the CRDT types for individual columns.

```sql
CREATE TABLE post (
 id INTEGER PRIMARY KEY NOT NULL,
 views COUNTER,
 content PERITEXT,
 owner_id LWW INTEGER
) AS CausalLengthSet;
```

--------------------------------

### Bind Values to Prepared Statement (JavaScript)

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Binds an array of values to a prepared SQL statement, allowing the statement to be executed with specific parameters later. This method returns the statement instance itself, enabling method chaining. The bound values are used when `get()`, `all()`, or `run()` is called on the statement. Dependencies: `SQLiteCompatibleType`.

```javascript
const stmt = await db.prepare("SELECT * FROM users WHERE id = ?");
stmt.bind([id]);
const user = await stmt.get<{ id: bigint, name: string }>(null);
```

--------------------------------

### Get All Rows from Statement (JavaScript)

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Executes a prepared SQL statement and returns all matching rows as an array of objects. If no rows match, an empty array is returned. This is used for retrieving multiple records that satisfy the query conditions. It can optionally run within a specified transaction (`tx`) or against the root database. Dependencies: `SQLiteCompatibleType`, `TXAsync`, generic type `T`.

```javascript
const stmt = await db.prepare("SELECT * FROM users");
const users = await stmt.all<{ id: bigint, name: string }>(null);
```

--------------------------------

### Setting up a New Database for Sync

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

This snippet initializes a new in-memory SQLite database and provides a handle to it. It's a prerequisite for syncing data, as the target database needs to exist before changes can be applied.

```javascript
use sqlite;
const db2 = await sqlite.open(":memory:");
provide db2;
```

--------------------------------

### Start Schema Alteration with crsql_begin_alter (SQL)

Source: https://vlcn.io/docs/cr-sqlite/api-methods/crsql_begin_alter

Initiates a schema alteration for a specified table in CR-SQLite. This function starts a transaction if one is not already active. It must be followed by `crsql_commit_alter`.

```sql
SELECT crsql_begin_alter('table_name');
```

--------------------------------

### Scaffold Vulcan App with NPM

Source: https://vlcn.io/docs/cr-sqlite/js/first-app

Creates a new Vulcan CR-SQLite application using NPM. This command initializes a new project with the specified name and sets up the basic structure for a Single Page Application (SPA) with a SQLite database.

```bash
npm create @vlcn.io@latest your-app-name
```

--------------------------------

### Basic Usage of rx-tbl for Reactivity

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

This JavaScript code demonstrates the basic usage of the rx-tbl package. It shows how to initialize the reactivity layer, subscribe to changes on specific tables or any change, and how to unsubscribe from these listeners.

```javascript
// see the wasm guide, or live example on this page, for creating a database
const rx = tblrx(db);

const disposable = rx.onRange(["posts"], (updateTypes) => {
  console.log("posts changed", updateTypes);
});

disposable(); // unsubscribe the `onRange` listener

rx.onAny(() => {
  console.log("db had some change");
});

rx.onPoint("posts", "xyz", (updateTypes) => {
  console.log("post with id xyz changed", updateTypes);
});

rx.dispose(); // uninstall the rx layer
```

--------------------------------

### Initialize cr-sqlite WASM Bundle and Database

Source: https://vlcn.io/docs/cr-sqlite/js/react

This snippet initializes the @vlcn.io/crsqlite-wasm bundle and opens a SQLite database. It then creates a 'todo' table if it doesn't exist and makes the database instance available for use. Dependencies include the cr-sqlite WASM bundle.

```javascript
const initWasm = await import('https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0');
const sqlite = await initWasm.default(
() => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm"
);
  

const db = await sqlite.open("react_example.db");
await db.exec(`CREATE TABLE IF NOT EXISTS todo (id PRIMARY KEY NOT NULL, content, complete)`);
provide db;
```

--------------------------------

### Use Prepared Statements for Performance

Source: https://context7.com/context7/vlcn_io/llms.txt

Explains how to use prepared statements for optimizing repeated query execution. Covers preparing statements, executing them with parameters, fetching single rows or multiple rows, and the importance of finalizing statements.

```javascript
import { nanoid } from 'nanoid';

// Prepare a statement
const insertStmt = await db.prepare(
  "INSERT INTO todos (id, content, complete) VALUES (?, ?, ?)"
);

// Execute it multiple times
await insertStmt.run(null, nanoid(), "Task 1", 0);
await insertStmt.run(null, nanoid(), "Task 2", 0);
await insertStmt.run(null, nanoid(), "Task 3", 1);

// Prepare a select statement
const selectStmt = await db.prepare("SELECT * FROM todos WHERE id = ?");

// Get single row as object
const todo = await selectStmt.get(null, "todo-123");
console.log(todo);
// { id: 'todo-123', content: 'Buy groceries', complete: 0n }

// Get all matching rows
const allStmt = await db.prepare("SELECT * FROM todos WHERE complete = ?");
const incompleteTodos = await allStmt.all(null, 0);

// Always finalize prepared statements
await insertStmt.finalize(null);
await selectStmt.finalize(null);
await allStmt.finalize(null);
```

--------------------------------

### Get CR-SQLite Site ID

Source: https://vlcn.io/docs/cr-sqlite/api-methods/crsql_site_id

Retrieves the unique site ID of the current database using the crsql_site_id() function. The site ID is a 16-byte UUID used to identify the database. No specific input is required.

```sql
SELECT crsql_site_id();
```

--------------------------------

### Get CR-SQLite Database Version

Source: https://vlcn.io/docs/cr-sqlite/api-methods/crsql_db_version

Retrieves the current Lamport timestamp of the database. This value is incremented with each transaction and used for causal ordering during synchronization. The `crsql_next_db_version()` function returns the version to be assigned in the current transaction.

```sql
SELECT crsql_db_version();

```

```sql
SELECT crsql_next_db_version();

```

--------------------------------

### Initialize and Open CR-SQLite Database

Source: https://context7.com/context7/vlcn_io/llms.txt

Loads the CR-SQLite WASM module and establishes database connections. Supports both persistent (IndexedDB) and in-memory databases. Includes schema creation and conversion to Conflict-Free Replicated Data Types (CRDTs).

```javascript
import initWasm from "@vlcn.io/crsqlite-wasm";
import wasmUrl from "@vlcn.io/crsqlite-wasm/crsqlite.wasm?url";

// Initialize WASM module
const sqlite = await initWasm(() => wasmUrl);

// Open a persistent database (stored in IndexedDB)
const db = await sqlite.open("myapp.db");

// Or open in-memory database
const memDb = await sqlite.open(":memory:");

// Create schema
await db.exec(`
  CREATE TABLE IF NOT EXISTS todos (
    id TEXT PRIMARY KEY NOT NULL,
    content TEXT,
    complete INTEGER DEFAULT 0
  )
`);

// Convert to CRR
await db.exec("SELECT crsql_as_crr('todos')");
```

--------------------------------

### Get Hex Encoded CR-SQLite Site ID

Source: https://vlcn.io/docs/cr-sqlite/api-methods/crsql_site_id

Retrieves the site ID of the current database and returns it as a hex-encoded string using the quote() function in conjunction with crsql_site_id(). This is useful for displaying or transmitting the ID in a more readable format.

```sql
SELECT quote(crsql_site_id());
```

--------------------------------

### Create React TodoList Component with @vlcn.io/react

Source: https://vlcn.io/docs/cr-sqlite/js/react

This snippet defines a React component for a todo list using @vlcn.io/react. It imports necessary libraries, defines a `TodoItem` component for individual list items, and a `TodoList` component that queries and displays todos. It includes functions for toggling completion and deleting items. Dependencies: @vlcn.io/react, react, htm.

```javascript
use db;
const [vlcnReact, React, htm] = await Promise.all([
import("https://esm.sh/@vlcn.io/react@3.1.0"),
import("https://esm.sh/react@18.2.0"),
import("https://esm.sh/htm@3.1.1/react"),
]);
  

const { useQuery, firstPick } = vlcnReact;
const { html } = htm;
const { useState } = React;
const nanoid=(t=21)=>crypto.getRandomValues(new Uint8Array(t)).reduce(((t,e)=>t+=(e&=63)<36?e.toString(36):e<62?(e-26).toString(36).toUpperCase():e>62?"-":"_"),"");
  

function TodoItem({ctx, todo}) {
const toggle = () => {
ctx.db.exec("UPDATE todo SET complete = ? WHERE id = ?", [
todo.complete === 0 ? 1 : 0,
todo.id,
]);
};
const del = () => {
ctx.db.exec("DELETE FROM todo WHERE id = ?", [todo.id]);
};
  
return html`<li key=${todo.id} class=${todo.complete ? "complete" : ""} onClick=${toggle}>
${todo.content}
<button onClick=${del} />
</li>`;
}
  
function TodoList({ ctx }) {
const todos = useQuery(ctx, "SELECT * FROM todo").data;
const remaining = useQuery(
ctx,
"SELECT count(*) as c FROM todo WHERE complete = 0",
null,
firstPick

```

--------------------------------

### Manual CR-SQLite Schema Alteration

Source: https://vlcn.io/docs/cr-sqlite/migrations

Demonstrates the manual process of altering a CR-SQLite table schema using `crsql_begin_alter` and `crsql_commit_alter`. These calls manage bookkeeping metadata during schema modifications like adding or dropping columns. They can be nested and will rollback on failure.

```sql
SELECT crsql_begin_alter('table_name');
-- Schema modification statements here
SELECT crsql_commit_alter('table_name');
```

```sql
SELECT crsql_begin_alter('foo');
ALTER TABLE foo ADD COLUMN c TEXT;
SELECT crsql_commit_alter('foo');
SELECT crsql_begin_alter('bar');
ALTER TABLE bar ADD COLUMN baz TEXT;
SELECT crsql_commit_alter('bar');
```

--------------------------------

### Render React TodoList Component to the DOM

Source: https://vlcn.io/docs/cr-sqlite/js/react

This snippet demonstrates how to render the `TodoList` React component into the DOM. It imports `react-dom/client`, `htm/react`, and `@vlcn.io/rx-tbl`. It sets up a context object with the database and reactive table instance, then uses `createRoot` to render the component. Dependencies: react-dom, htm, @vlcn.io/rx-tbl.

```javascript
use TodoList;
use db;
const [reactDom, htm, tblrx] = await Promise.all([
import("https://esm.sh/react-dom@18.2.0/client"),
import("https://esm.sh/htm@3.1.1/react"),
import("https://esm.sh/@vlcn.io/rx-tbl@0.15.0"),
]);
  

const { html } = htm;
const { createRoot } = reactDom;
  

const rx = await tblrx.default(db);
const ctx = {db, rx};
  

const root = createRoot(document.getElementById("todo-app"));
root.render(html`<${TodoList} ctx=${ctx} />`);
```

--------------------------------

### Initialize CR-SQLite WASM

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Demonstrates the `initWasm` function, which initializes the WebAssembly module for CR-SQLite. It requires a function that returns the URL of the WASM file. The function returns a promise that resolves with the SQLite API.

```javascript
import initWasm from "@vlcn.io/crsqlite-wasm";
import wasmUrl from "@vlcn.io/crsqlite-wasm/crsqlite.wasm?url";
 
const sqlite = await initWasm(() => wasmUrl);
```

--------------------------------

### Initialize and Open CR-SQLite Database in Memory

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

Initializes the `crsqlite-wasm` module and opens an in-memory SQLite database. This is a prerequisite for applying the CRR schema and performing sync operations. It imports the WASM module and provides access to the SQLite interface and the opened database.

```javascript
const initWasm = await import('https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0');
const sqlite = await initWasm.default(() => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm");
const db1 = await sqlite.open(":memory:");
provide sqlite;
provide db1;
```

--------------------------------

### Execute SQL Queries and Retrieve Data

Source: https://context7.com/context7/vlcn_io/llms.txt

Demonstrates various methods for executing SQL statements. Supports executing without returning data, fetching results as objects or arrays, and executing multiple statements at once.

```javascript
// Execute without returning data
await db.exec(
  "INSERT INTO todos (id, content, complete) VALUES (?, ?, ?)",
  ["todo-123", "Buy groceries", 0]
);

// Execute and get results as objects
const todos = await db.execO("SELECT * FROM todos WHERE complete = ?", [0]);
console.log(todos);
// [{ id: 'todo-123', content: 'Buy groceries', complete: 0n }]

// Execute and get results as arrays
const todoArrays = await db.execA("SELECT id, content FROM todos");
console.log(todoArrays);
// [['todo-123', 'Buy groceries']]

// Execute multiple statements
await db.execMany([
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
  "CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT)"
]);
```

--------------------------------

### Applying Schema to New Database

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

This snippet applies a predefined schema to a new database (db2). This ensures that both the source and target databases have the same structure, which is necessary for successful data synchronization.

```javascript
use db2;
db2.execMany(schema);
return 'schema written';
```

--------------------------------

### initWasm

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Initializes the WebAssembly module for CR-SQLite and returns the top-level SQLite API. This is the entry point for using CR-SQLite in the browser.

```APIDOC
## initWasm

### Description
Initializes the wasm module. Returns the top level SQLite API.

### Method
`initWasm`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```javascript
import initWasm from "@vlcn.io/crsqlite-wasm";
import wasmUrl from "@vlcn.io/crsqlite-wasm/crsqlite.wasm?url";

const sqlite = await initWasm(() => wasmUrl);
```

### Response
#### Success Response (200)
- **SQLite** - The top-level SQLite API object.

#### Response Example
```javascript
// Example of the returned SQLite API object structure (simplified)
{
  "open": "function",
  // ... other methods
}
```
```

--------------------------------

### Open Database Connection

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Shows how to open a database connection using the `open` method from the initialized SQLite API. An optional path can be provided for persistent storage; otherwise, an in-memory database is created.

```javascript
const db = await sqlite.open("my-database.db");
```

--------------------------------

### Import CR-SQLite React Hooks and Utilities

Source: https://vlcn.io/docs/cr-sqlite/js/react

Imports the `useQuery` hook from the `@vlcn.io/react` package and the `tblrx` utility from `@vlcn.io/rx-tbl`. These are essential for interacting with the CR-SQLite database in a React environment.

```javascript
import { useQuery } from "@vlcn.io/react";
import tblrx from "@vlcn.io/rx-tbl";
```

--------------------------------

### Get Single Row from Statement (JavaScript)

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Executes a prepared SQL statement and returns the first row found as an object. If no rows match the query, it returns `null`. This is ideal for retrieving a single record based on a unique identifier. It can optionally run within a specified transaction (`tx`) or against the root database. Dependencies: `SQLiteCompatibleType`, `TXAsync`, generic type `T`.

```javascript
const stmt = await db.prepare("SELECT * FROM users WHERE id = ?");
const user = await stmt.get<{ id: bigint, name: string }>(null, id);
```

--------------------------------

### Import and Initialize CR-SQLite WASM in Browser

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Imports the necessary functions and WASM file for CR-SQLite and initializes the WASM module. It assumes a bundler that supports the '?url' import syntax for WASM files. This function returns the top-level SQLite API.

```javascript
import initWasm from "@vlcn.io/crsqlite-wasm";
import wasmUrl from "@vlcn.io/crsqlite-wasm/crsqlite.wasm?url";

const sqlite = await initWasm(() => wasmUrl);
const db = await sqlite.open("my-database.db");
```

--------------------------------

### Initialize CR-SQLite WASM Build

Source: https://vlcn.io/docs/cr-sqlite/js/persistence

Initializes the CR-SQLite WebAssembly build, which is necessary before interacting with the database. This involves importing the necessary package and calling the default export with the path to the WASM file. Ensure the correct version and path are used for the WASM module.

```javascript
const initWasm = await import('https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0')
const sqlite = await initWasm.default(() => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm");
provide sqlite;
```

--------------------------------

### Querying Data from Database

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

This snippet shows how to query data from the 'deck', 'slide', and 'component' tables using SELECT statements. It uses db1.execO to execute a query and return a single object, suitable for retrieving all records from a table.

```javascript
use deckid;
const decks = await db1.execO(`SELECT * FROM deck`);
const slides = await db1.execO(`SELECT * FROM slide`);
const components = await db1.execO(`SELECT * FROM component`);
return {
decks,
slides,
components,
};
```

--------------------------------

### Use React useQuery Hook for Reactive Database Queries

Source: https://context7.com/context7/vlcn_io/llms.txt

The `useQuery` hook from '@vlcn.io/react' allows components to subscribe to database queries. Data automatically re-renders when underlying data changes. It supports various query types including parameterized queries, single value retrieval, first row retrieval, and array of single column retrieval. Dependencies include '@vlcn.io/react' and '@vlcn.io/rx-tbl'.

```javascript
import { useQuery, firstPick, first, pick } from "@vlcn.io/react";
import tblrx from "@vlcn.io/rx-tbl";

// Setup database context
const db = await sqlite.open("myapp.db");
const rx = tblrx(db);
const ctx = { db, rx };

// Component with reactive query
function TodoList({ ctx }) {
  // Query returns all todos
  const { data: todos, loading, error } = useQuery(
    ctx,
    "SELECT * FROM todos ORDER BY complete, id"
  );

  // Query with parameters
  const { data: incompleteTodos } = useQuery(
    ctx,
    "SELECT * FROM todos WHERE complete = ?",
    [0]
  );

  // Get single value using firstPick
  const { data: count } = useQuery(
    ctx,
    "SELECT COUNT(*) FROM todos WHERE complete = 0",
    null,
    firstPick
  );

  // Get first row using first
  const { data: firstTodo } = useQuery(
    ctx,
    "SELECT * FROM todos LIMIT 1",
    null,
    first
  );

  // Get array of single column using pick
  const { data: todoIds } = useQuery(
    ctx,
    "SELECT id FROM todos",
    null,
    pick
  );

  if (loading) return <div>Loading...</div>;
  if (error) return <div>Error: {error.message}</div>;

  return (
    <div>
      <h2>Remaining: {count}</h2>
      {todos.map(todo => (
        <TodoItem key={todo.id} todo={todo} ctx={ctx} />
      ))}
    </div>
  );
}

// Component that mutates data
function TodoItem({ todo, ctx }) {
  const toggleComplete = () => {
    ctx.db.exec(
      "UPDATE todos SET complete = ? WHERE id = ?",
      [todo.complete === 0 ? 1 : 0, todo.id]
    );
  };

  const deleteTodo = () => {
    ctx.db.exec("DELETE FROM todos WHERE id = ?", [todo.id]);
  };

  return (
    <li onClick={toggleComplete}>
      {todo.content}
      <button onClick={deleteTodo}>Delete</button>
    </li>
  );
}

```

--------------------------------

### tblrx API

Source: https://vlcn.io/docs/cr-sqlite/js/reactivity

Wraps the database with the reactivity layer and returns a TblRx instance.

```APIDOC
## tblrx

Wraps the database with the reactivity layer and returns a `TblRx` instance.

```
function tblrx(db: DB): TblRx;
```

**Params:**
  1. `db` - The database connection to wrap with the reactivity layer. Gotten by opening a database.
```

--------------------------------

### Manage Database Transactions

Source: https://context7.com/context7/vlcn_io/llms.txt

Details how to perform atomic operations using transactions. Shows executing multiple statements within a transaction, using prepared statements inside transactions, and querying within a transaction context.

```javascript
import { nanoid } from 'nanoid';

// Execute multiple operations atomically
await db.tx(async (tx) => {
  await tx.exec(
    "INSERT INTO todos (id, content, complete) VALUES (?, ?, ?)",
    [nanoid(), "Write docs", 0]
  );
  await tx.exec(
    "UPDATE todos SET complete = 1 WHERE id = ?",
    ["todo-123"]
  );
  await tx.exec(
    "DELETE FROM todos WHERE complete = 1 AND id != ?",
    ["todo-123"]
  );
});

// Use prepared statements within transactions
const stmt = await db.prepare("INSERT INTO todos (id, content, complete) VALUES (?, ?, ?)");

await db.tx(async (tx) => {
  for (let i = 0; i < 100; i++) {
    await stmt.run(tx, nanoid(), `Task ${i}`, 0);
  }
});

await stmt.finalize(null);

// Query within transaction
await db.tx(async (tx) => {
  const count = await tx.execO("SELECT COUNT(*) as c FROM todos");
  console.log(`Total todos: ${count[0].c}`);
});
```

--------------------------------

### Client-side WebSocket Sync Integration - JavaScript

Source: https://vlcn.io/docs/cr-sqlite/js/networking

This snippet shows how to integrate a WebSocket synchronization client using a SyncWorker and the useSync hook from @vlcn.io/react. It configures the connection to the WebSocket server using the database name, endpoint, and room, and passes the worker instance.

```javascript
import SyncWorker from "./sync-worker.js?worker";
import { useSync } from "@vlcn.io/react";
 
const worker = new SyncWorker();
function App() {
  useSync({
    dbname,
    endpoint: "ws://localhost:8080/sync",
    room,
    worker,
  });
}
```

--------------------------------

### Define Base Schema for Presentation Editor

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

Defines the base data model for a collaborative presentation editor using SQLite tables. This includes tables for decks, slides, components, and selection states. These tables form the foundation for both local-only and synchronized data.

```javascript
const baseSchema = [
`CREATE TABLE IF NOT EXISTS "deck" (
"id" primary key NOT NULL,
"name"
);`,
`CREATE TABLE IF NOT EXISTS "slide" (
"id" primary key NOT NULL,
"deck_id",
"order"
);`,
`CREATE TABLE IF NOT EXISTS "component" (
"id" primary key NOT NULL,
"slide_id",
"type",
"content",
"x",
"y",
"w",
"h",
"rotation"
);`,
`CREATE TABLE IF NOT EXISTS "selected_slide" (
"deck_id" NOT NULL, "slide_id" NOT NULL, primary key ("deck_id", "slide_id")
);`,
`CREATE TABLE IF NOT EXISTS "selected_component" (
"slide_id" NOT NULL, "component_id" NOT NULL, primary key ("slide_id", "component_id")
);
];
provide baseSchema;
return baseSchema;
```

--------------------------------

### Database Lifecycle Management

Source: https://context7.com/context7/vlcn_io/llms.txt

Covers essential lifecycle management for CR-SQLite databases, including registering update notifications for real-time changes and properly closing the database connection.

```javascript
// Register update notifications
db.onUpdate((type, dbName, tblName, rowid) => {
  console.log(`${type} on ${dbName}.${tblName} row ${rowid}`);
  // type is 'INSERT', 'UPDATE', or 'DELETE'
});

// Close database (automatically calls crsql_finalize)
await db.close();
```

--------------------------------

### Writing Data to Database

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

This snippet demonstrates how to write data to a database by inserting records into 'deck', 'slide', and 'component' tables. It utilizes the nanoid() function for generating unique IDs and db1.execMany for executing multiple SQL statements.

```javascript
use db1;
const deckid = nanoid();
const slideid1 = nanoid();
const slideid2 = nanoid();
await db1.execMany([
`INSERT INTO "deck" ("id", "name") VALUES ('${deckid}', 'First Presentation');`,
`INSERT INTO "slide" ("id", "deck_id", "order") VALUES ('${slideid1}', '${deckid}', 0);`,
`INSERT INTO "slide" ("id", deck_id, "order") VALUES ('${slideid2}', '${deckid}', 1);`,
`INSERT INTO component ("id", "slide_id", "type", "content", x, y, w, h, "rotation") VALUES ('${nanoid()}', '${slideid1}', 'text', 'Some Title', 0, 0, 600, 100, 0);`,
`INSERT INTO component ("id", "slide_id", "type", "content", x, y, w, h, "rotation") VALUES ('${nanoid()}', '${slideid1}', 'text', 'Some subtext', 500, 150, 400, 100, 0);`
]);
provide deckid;
return 'data written';
```

--------------------------------

### Verify Schema Application in CR-SQLite Database

Source: https://vlcn.io/docs/cr-sqlite/networking/whole-crr-sync

Executes a query against the database to verify that all tables, including CRR-specific internal tables, have been successfully created. This confirms that the schema, including the CRR configurations, has been correctly applied.

```javascript
use schemaApplied;
return (await db1.execA(`SELECT name FROM sqlite_master WHERE type = 'table'`)).join("\n");
```

--------------------------------

### SQLite::open

Source: https://vlcn.io/docs/cr-sqlite/js/wasm

Opens a database connection to a specified SQLite database file. If no path is provided, an in-memory database is created.

```APIDOC
## SQLite::open

### Description
Opens a database connection. If no path is provided, an in-memory database will be created.

### Method
`sqlite.open(path?: string): Promise<DB>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
#### Path Parameters
- **path** (string) - Optional - The path to the database file. If not provided, an in-memory database will be created.

### Request Example
```javascript
const db = await sqlite.open("my-database.db");
```

### Response
#### Success Response (200)
- **DB** - A database connection object.

#### Response Example
```javascript
// Example of the returned DB object structure (simplified)
{
  "exec": "function",
  "execO": "function",
  "execA": "function",
  "execMany": "function",
  "prepare": "function",
  "close": "function"
}
```
```
Tokens:
Raw