@canvas-js/modeldb

ModelDB is a minimalist cross-platform relational database wrapper. It currently supports the following backends:

- IndexedDB (browser)
- SQLite + WASM (browser) with either an OPFS store or transient in-memory storage
- PostgreSQL (NodeJS)
- Native SQLite (NodeJS)
- Native SQLite (React Native with Expo)
- Durable Objects (Cloudflare) (experimental, not officially supported)

Table of Contents

- Usage
- Initialization
- Schemas
- Setting and deleting records
- Queries
- Indexes
- Migrations
- Name restrictions
- Testing
- License

Usage

$3

Import ModelDB from one of:

- @canvas-js/modeldb-idb (browser)
- @canvas-js/modeldb-sqlite-wasm (browser)
- @canvas-js/modeldb-pg (NodeJS)
- @canvas-js/modeldb-sqlite (NodeJS)
- @canvas-js/modeldb-sqlite-expo (React Native)
- @canvas-js/modeldb-durable-objects (Durable Objects)

Then open a database using the static async open method:

``ts
import { ModelDB } from "@canvas-js/modeldb-sqlite"

const db = await ModelDB.open(
"/path/to/db.sqlite", // or null for an in-memory database
{ models: { ... } },
)
`

`ts
import { ModelDB } from "@canvas-js/modeldb-idb"

const db = await ModelDB.open(
"my-database-name", // used as the IndexedDB database name
{ models: { ... } },
)
`

For more initialization examples, see the test directory in each subpackage.

$3

Databases are configured with a models schema, provided as a JSON DSL. Every model has a mandatory string primary key and supports nullable and non-nullable integer, float, string and bytes datatypes. It also supports a non-nullable json datatype.

`ts
import { ModelDB } from "@canvas-js/modeldb-sqlite"

const db = await ModelDB.open(null, {
models: {
user: {
// exactly one "primary" property is required
id: "primary",
// properties are non-null by default
name: "string",
// declare nullable properties using ?
birthday: "string?",
// json data is also supported
metadata: "json",
},
},
})

await db.set("user", { id: "xxx", name: "John", birthday: "1990-01-01", metadata: {} })
await db.set("user", { id: "xxx", name: "John Doe", birthday: "1990-01-01", metadata: { home: "New York" } })
await db.get("user", "xxx") // { id: "xxx", name: "John Doe", birthday: "1990-01-01", metadata: { home: "New York" } }
`

Reference properties (@user with string values), nullable reference properties (@user? with string | null values), and relation properties (@user[] with string[] values) are also supported, although the foreign key constraint is not enforced.

`ts
const db = await ModelDB.open(null, {
models: {
user: {
user_id: "primary",
name: "string",
},
room: {
room_id: "primary",
members: "@user[]",
},
message: {
message_id: "primary",
user: "@user",
content: "string",
timestamp: "integer",
},
},
})
`

$3

Mutate the database using either the set and delete methods, or the lower-level apply method to batch operations in an atomic transaction:

`ts
await db.set("user", { user_id: "xxx", name: "John Doe" })
await db.set("user", { user_id: "yyy", name: "Jane Doe" })
await db.delete("user", "xxx")

await db.apply([
{ model: "user", operation: "set", value: { user_id: "xxx", name: "John Doe" } },
{ model: "user", operation: "set", value: { user_id: "yyy", name: "Jane Doe" } },
{ model: "user", operation: "delete", key: "xxx" },
])
`

$3

Access data using the query method, or use the get to retrieve records by primary key.

`ts
await db.set("user", { user_id: "a", name: "Alice" })
await db.set("user", { user_id: "b", name: "Bob" })
await db.set("user", { user_id: "c", name: "Carol" })

await db.get("user", "a") // { user_id: "a", name: "Alice" }
await db.get("user", "d") // null

await db.query("user", { where: { user_id: { gte: "b" } } })
// [
// { user_id: "b", name: "Bob" },
// { user_id: "c", name: "Carol" },
// ]
`

Queries support select, where, orderBy, and limit expressions. where conditions can have equality, inequality, and range terms.

`ts
export type QueryParams = {
select?: Record
where?: WhereCondition
orderBy?: Record
limit?: number
offset?: number
}

export type WhereCondition = Record
export type NotExpression = {
neq: PropertyValue
}

export type RangeExpression = {
gt?: PrimitiveValue
gte?: PrimitiveValue
lt?: PrimitiveValue
lte?: PrimitiveValue
}
`

$3

By default, queries translate into filters applied to a full table scan. You can create indexes using the special $indexes: string[] property:

`ts
const db = await ModelDB.init({
models: {
// ...
message: {
message_id: "primary",
user: "@user",
content: "string",
timestamp: "integer",
$indexes: ["timestamp"],
},
},
})

// this will use the timestamp index to avoid a full table scan
const recentMessages = await db.query("message", { orderBy: { timestamp: "desc" }, limit: 10 })
`

Multi-property index support will be added soon.

$3

ModelDB has a unique kind of migration system designed to support multiple distinct schemas co-existing in the database with indepedent versions.

To use the migration system, begin by providing an object version: Record of namespaced version numbers to the init object.

`ts
const db = await ModelDB.open("path/to/db.sqlite", {
models: { ... },
version: { myapp: 1 },
})
`

ModelDB will store each namespaced version number in an internal $versions table. If you don't provide anything else, ModelDB.open will compare the provided versions to any existing versions, and will throw an error if there is an existing version that does not exactly match the provided version.

To handle migrations between versions, provide an async upgrade callback to the init object:

`ts
const db = await ModelDB.open("path/to/db.sqlite", {
models: { ... },
version: { myapp: 2 },
upgrade: async (upgradeAPI: DatabaseUpgradeAPI, oldConfig, oldVersion, newVersion) => {
// Execute your upgrade here using upgradeAPI.
await upgradeAPI.createModel("users", {
id: "primary",
address: "string?",
})

await upgradeAPI.addProperty("posts", "timestamp", "number", 0)
await upgradeAPI.addProperty("posts", "reply_to", "string?", null)
await upgradeAPI.addIndex("posts", "timestamp")
},
})
`

Inside the upgrade callback, the upgradeAPI handle provides all of the methods of a db - including get, set, delete, clear, query, and iterate - plus additional methods for adding and removing models, properties, and indexes.

`ts
export interface DatabaseUpgradeAPI extends DatabaseAPI {
createModel(name: string, init: ModelInit): Awaitable
deleteModel(name: string): Awaitable

addProperty(
modelName: string,
propertyName: string,
propertyType: PropertyType,
defaultPropertyValue: PropertyValue,
): Awaitable
removeProperty(modelName: string, propertyName: string): Awaitable

addIndex(modelName: string, index: string): Awaitable
removeIndex(modelName: string, index: string): Awaitable
}
`

After the ugprade callback completes, the database will verify that the resulting schema _exactly_ matches the provided models: { ... }. Any discrepency will result in a thrown exception.

$3

Model names and property names can contain [a-zA-Z0-9$:_\-\.].

Testing

ModelDB has a test suite that uses Ava as its test runner.

`sh
npm run test --workspace=@canvas-js/modeldb
``

License

MIT © Canvas Technologies, Inc.