DWN SQL Stores


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

- Supported DBs
- Installation
- Usage
- SQLite
- MySQL
- PostgreSQL
- Development
- Prerequisites
- node and npm
- Docker
- Running Tests
- npm scripts

Supported DBs

* SQLite ✔️
* MySQL ✔️
* PostgreSQL ✔️

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

Installation

``bash
npm install @tbd54566975/dwn-sql-store
`

Usage

SQLite

`typescript
import Database from 'better-sqlite3';

import { Dwn } from '@tbd54566975/dwn-sdk-js'
import { SqliteDialect, MessageStoreSql, DataStoreSql, EventLogSql } from '@tbd54566975/dwn-sql-store';

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

const messageStore = new MessageStoreSql(sqliteDialect);
const dataStore = new DataStoreSql(sqliteDialect);
const eventLog = new EventLogSql(sqliteDialect);

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

MySQL

`typescript
import { createPool } from 'mysql2';
import { Dwn } from '@tbd54566975/dwn-sdk-js'
import { MysqlDialect, MessageStoreSql, DataStoreSql, EventLogSql } from '@tbd54566975/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 eventLog = new EventLogSql(mysqlDialect);

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

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'
...

`


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

import { Dwn } from '@tbd54566975/dwn-sdk-js'
import { PostgresDialect, MessageStoreSql, DataStoreSql, EventLogSql } from '@tbd54566975/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 eventLog = new EventLogSql(postgresDialect);

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

Development

Prerequisites

$3

This project is developed and tested with Node.js
v18 and v20 and NPM v9. You can verify your node and npm installation via the terminal:

`
$ node --version
v20.3.0
$ npm --version
9.6.7
`

If you don't have node installed. Feel free to choose whichever approach you feel the most comfortable with. If you don't have a preferred installation method, i'd recommend using nvm (aka node version manager). nvm allows you to install and use different versions of node. It can be installed by running brew install nvm (assuming that you have homebrew)

Once you have installed nvm, install the desired node version with nvm install vX.Y.Z.

$3

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 npm 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 npm run test

npm scripts

| Script | Description |
| ----------------------- | ------------------------------------------- |
| npm run build:cjs | compiles typescript into CommonJS |
| npm run build:esm | compiles typescript into ESM JS |
| npm run build | compiles typescript into ESM JS & CommonJS |
| npm run clean | deletes compiled JS |
| npm run test | runs tests. |
| npm run test-coverage | runs tests and includes coverage |
| npm run lint | runs linter |
| npm run lint:fix` | runs linter and fixes auto-fixable problems |

[codeowners-link]: https://github.com/TBD54566975/web5-js/blob/main/sql-dialect-variations.md