DWN SQL Stores

Research Preview — Enbox is under active development. APIs may change without notice.

SQL backed implementations of DWN MessageStore, DataStore, and StateIndex.

• Supported DBs
• Installation
• Usage
  • SQLite
  • MySQL
  • PostgreSQL
• Development
  • Prerequisites
    • Bun
    • Docker
  • Running Tests
  • Scripts

Supported DBs

• SQLite ✔️
• MySQL ✔️
• PostgreSQL ✔️

NOTE: See SQL Dialect Variations for the list of special handling to support the above SQL variations.

Installation

bun add @enbox/dwn-sql-store

Usage

SQLite

import { Dwn } from '@enbox/dwn-sdk-js'
import { createBunSqliteDatabase, SqliteDialect, MessageStoreSql, DataStoreSql, StateIndexSql } from '@enbox/dwn-sql-store';

const sqliteDialect = new SqliteDialect({
  database: async () => createBunSqliteDatabase('dwn.sqlite'),
});

const messageStore = new MessageStoreSql(sqliteDialect);
const dataStore = new DataStoreSql(sqliteDialect);
const stateIndex = new StateIndexSql(sqliteDialect);

const dwn = await Dwn.create({ messageStore, dataStore, stateIndex });

MySQL

import { createPool } from 'mysql2';
import { Dwn } from '@enbox/dwn-sdk-js'
import { MysqlDialect, MessageStoreSql, DataStoreSql, StateIndexSql } from '@enbox/dwn-sql-store';

const mysqlDialect = new MysqlDialect({
  pool: async () => createPool({
    host     : 'localhost',
    port     : 3306,
    database : 'dwn',
    user     : 'root',
    password : 'dwn'
  })
});

const messageStore = new MessageStoreSql(mysqlDialect);
const dataStore = new DataStoreSql(mysqlDialect);
const stateIndex = new StateIndexSql(mysqlDialect);

const dwn = await Dwn.create({ messageStore, dataStore, stateIndex });

PostgreSQL

NOTE: PostgreSQL requires setting the LC_COLLATE and LC_CTYPEto C during database creation. examples:

When using docker include the following option

POSTGRES_INITDB_ARGS='--lc-collate=C --lc-ctype=C'

Or when creating the database.

CREATE DATABASE dwn_data_store_dev
  WITH ENCODING='UTF8'
  ...
       LC_COLLATE='C'
       LC_CTYPE='C'
  ...

import pg from 'pg';
import Cursor from 'pg-cursor';

import { Dwn } from '@enbox/dwn-sdk-js'
import { PostgresDialect, MessageStoreSql, DataStoreSql, StateIndexSql } from '@enbox/dwn-sql-store';

const postgresDialect = new PostgresDialect({
  pool: async () => new pg.Pool({
    host     : 'localhost',
    port     : 5432,
    database : 'dwn',
    user     : 'root',
    password : 'dwn'
  }),
  cursor: Cursor
});

const messageStore = new MessageStoreSql(postgresDialect);
const dataStore = new DataStoreSql(postgresDialect);
const stateIndex = new StateIndexSql(postgresDialect);

const dwn = await Dwn.create({ messageStore, dataStore, stateIndex });

Development

Prerequisites

Bun

This project uses Bun >= 1.0 as its runtime and package manager.

Docker

Docker is used to spin up a local containerized DBs for testing purposes. Docker from here

Running Tests

💡 Make sure you have all the prerequisites

0. clone the repo and cd into the project directory
1. Install all project dependencies by running bun install
2. Start docker

   NOTE: You might need to delete the existing PostgreSQL and MySQL docker containers as well as dwn.sqlite file when a breaking change is introduced if you see tests that used to pass is now failing after a git pull. You can run ./scripts/delete-databases to do this.

3. start the test databases using ./scripts/start-databases (requires Docker)
4. run tests using bun run test

Scripts

| Script | Description | | ------------------- | ---------------------------- | | bun run build | compiles TypeScript | | bun run clean | deletes compiled JS | | bun run test | runs tests | | bun run lint | runs linter | | bun run lint:fix | auto-fix lint issues |