Skip to content

Setup and manage your cloud SKDB database

SKDB is a local embedded database that can connect to a cloud replica and synchronise data with other users in real time. Multiple users connected to the same cloud database can communicate simply by reading and writing their local database.

This section of the manual explains how to create the cloud side of the database and how to configure it so that you can control

  • the shared data schema
  • privacy permissions and rules: who can read and write which rows
  • application data invariants: ensure that users cannot change data that would break application rules

Create a cloud database

You can create a cloud database using the SKDB self service console.

https://console.skdb.io/

Here you can create an account and sign in.

SKDB console sign in

Under the 'Databases' section, click 'Create a new database', provide a name that you'll use in code to establish a connection, and hit 'Submit'.

SKDB create database popup

Databases are created instantly. You can get started with your new database by finding it in the list of databases in your account and clicking on the name.

SKDB database listing

On the database detail page, you can click 'Fetch and display root private key' to get hold of the credential you'll need to connect as 'root'. You can also click 'Open Live Notebook...' to interact with your new database directly in the browser using an interface similar to a Jupyter notebook.

SKDB database detail page

Create a user

You can create a database user and fetch the credentials using code or a CLI that provides a text-based interface. Both methods are convenient for automation.

Create a user with code

You can create a user from the SKDB API. First, create a local database and connect to the cloud database as root - only the root user can create new users. Then can create a user:

const remoteDb = await skdb.connectedRemote();
const newUserCredentials = await remoteDb.createUser();

You do not need to provide any information for the new user. An 'access key' and 'private key' are generated for you. The access key identifies the user and the private key is the shared secret that allows the connection to authenticate with the server.

Let's demonstrate how you would use the credentials to connect:

const newUserSkdb = await createSkdb({asWorker: false});
await newUserSkdb.connect(
  "example_db",
  newUserCredentials.accessKey, // <==
  await crypto.subtle.importKey(
    "raw",
    newUserCredentials.privateKey, // <==
    { name: "HMAC", hash: "SHA-256" },
    false,
    ["sign"],
  )
);

console.log(
  "Successfully connected as:",
  await (await newUserSkdb.connectedRemote()).connectedAs()
);

newUserSkdb.closeConnection();

Successfully connected as: fG-c7tGpysLpevgODVIaRiYDSt2

Create a user with the CLI

You can use npx skdb to access the SKDB CLI.

You may first need to add your database root credential to allow connecting: 

$ npx skdb --db example_db --access-key root --add-cred <<< "<EXAMPLE_DB_ROOT_PRIVATE_KEY_BASE_64>"

Then creating a user is as simple as: 

$ npx skdb --db example_db --access-key root --create-user
Successfully created user:  {
  'fGoVtK4eCo1-hV_nbgTKphaEemA': '<NEW_USER_PRIVATE_KEY_BASE_64>'
}
Credentials were added to ~/.skdb/credentials.

Run SQL on the remote database

There are three ways of executing SQL on the remote database:

  • using code - perfect for automation
  • a CLI that provides a text based interface in your terminal - this allows conveniently running SQL scripts or interactive REPL-style debugging
  • the web-based Live Notebook - a friendly and convenient way of getting started

Run SQL using code

With a local database connected to the server, you can exec queries on the remote side by first getting hold of the RemoteSKDB instance using connectedRemote().

const remoteDb = await skdb.connectedRemote();
await remoteDb.exec("INSERT INTO mirror_demo_table (n, skdb_access) VALUES (1, 'root')");
console.log(await remoteDb.exec("SELECT * FROM mirror_demo_table"));
await remoteDb.exec("DELETE FROM mirror_demo_table");

SKDBTable(1) [
  {
    id: 'fG-c8Qu57RuAKzocj69AmafeNK6',
    n: 1,
    f: null,
    t: null,
    skdb_access: 'root'
  }
]

Run SQL using the CLI

This interactive session demonstrates how you can run SQL queries against the remote server.

$ npx skdb --db example_db --access-key root --remote-repl
root@wss://api.skiplabs.io/example_db> INSERT INTO mirror_demo_table (n, skdb_access) VALUES (1, 'root')
root@wss://api.skiplabs.io/example_db> SELECT * FROM mirror_demo_table
┌─────────┬───────────────────────────────┬───┬──────┬──────┬─────────────┐
│ (index) │ id                            │ n │ f    │ t    │ skdb_access │
├─────────┼───────────────────────────────┼───┼──────┼──────┼─────────────┤
│ 0       │ 'fGom0oFUvP5jxBb5JIiPT0kD962' │ 1 │ null │ null │ 'root'      │
└─────────┴───────────────────────────────┴───┴──────┴──────┴─────────────┘
root@wss://api.skiplabs.io/example_db> DELETE FROM mirror_demo_table
root@wss://api.skiplabs.io/example_db> SELECT * FROM mirror_demo_table
root@wss://api.skiplabs.io/example_db>

Run SQL using the web-based Live Notebook

You can run SQL against a database using the SKDB console.

https://console.skdb.io/

On the database detail page, you can click 'Open Live Notebook...' to interact with your a database directly in the browser using an interface similar to a Jupyter notebook.

SKDB database detail page

Define the data schema

You can define the remote schema by running SQL statements against the remote database.

With a connected SKDB client, you can define the schema for a table using exec on the RemoteSKDB instance.

const remoteDb = await skdb.connectedRemote();
await remoteDb.exec(
  `CREATE TABLE IF NOT EXISTS mirror_demo_table (
    id TEXT PRIMARY KEY,
    n INTEGER,
    f FLOAT,
    t TEXT,
    skdb_access TEXT NOT NULL
  );`
);

There are a few important things to call out from this example:

  • We create an skdb_access column. It must always be defined as TEXT NOT NULL. The skdb_access column value defines the read and modify permissions for the row. A table cannot be mirrored without this column defined. See 'control who can see and modify rows' for detail on how this works.

  • We use TEXT as the type for the primary key. It is best practice in SKDB to use TEXT primary keys. SKDB will generate globally unique ids to prevent conflict with other users.

  • Conflicts will be resolved using a 'last-write wins' policy because we defined a primary key. If no primary key is defined then conflicts are not automatically resolved and the application can decide how it wishes to resolve conflict. See controlling replication for detail on how this works.

  • We use IF NOT EXISTS so that this SQL statement can be idempotently executed in a script.

Control who can see and modify rows

A table can only be mirrored if it has an skdb_access column defined as text NOT NULL. For example:

CREATE TABLE cannot_be_mirrored (id text PRIMARY KEY);

CREATE TABLE can_be_mirrored (id text PRIMARY KEY, skdb_access text NOT NULL);

The value stored in the skdb_access column controls who can read or modify the row.

Privacy in SKDB is not something you opt-in to, but must define in order to share data with other users. This is why you cannot mirror a table that does not have an skdb_access column.

The value that you write in the skdb_access column should be a:

  • user ID (often also referred to as an 'access key')

    • only the user defined by this value is able to read or modify this row.

  • or a group ID

    • The group with this ID defines who can read or modify this row.
    • To write a row with this value, you must have the 'insert' permission. To read or delete this row, you must have the 'read' or 'delete' permission respectively.
    • Please see create and manage a group for a discussion on how groups define access policies.

Track who wrote a row

For some applications it is important to have a guarantee on who wrote a row. Consider for example an auction, it would be essential that bids can be attributed to a user and cannot be spoofed.

SKDB provides the skdb_author column for this purpose. skdb_author should be defined in a table like this:

CREATE TABLE example (
  id text PRIMARY KEY,
  skdb_author text NOT NULL,
  skdb_access text NOT NULL
);

When writing a row, you must specify the user ID of the currently connected and authenticated user as the value of the skdb_author column. Any other value will generate an error locally. If a malicious client tried to force replication, the transaction will be rejected by the cloud server and will not be stored or propagated to other users.

If a user has permission (defined by the skdb_access column), they may delete a row that was written by another user (and so has an skdb_author value that is not the current user's user id). Updates are also allowed, but you must update the skdb_author value with the current user's user id.

With a connected client, you can write a row to a table with an skdb_author column like this:

console.log(await remote.tableSchema("skdb_author_example"));

await skdb.mirror({table: "skdb_author_example", expectedColumns: "*"});

await skdb.exec(
  "INSERT INTO skdb_author_example (skdb_author, skdb_access) VALUES (@me, 'read-write')",
  {me: skdb.currentUser}
);

try {
  await skdb.exec(
    "INSERT INTO skdb_author_example (skdb_author, skdb_access) VALUES ('will_not_work', 'read-write')"
  );
} catch (ex) {
  console.log(ex);
}

CREATE TABLE skdb_author_example (
  id TEXT PRIMARY KEY,
  skdb_author TEXT NOT NULL,
  skdb_access TEXT NOT NULL
);

SkRuntimeExit [Error]: INSERT INTO skdb_author_example (skdb_author, skdb_access) VALUES ('will_not_work', 'read-write')
^
|
 ----- ERROR
Error: line 1, character 0:
skdb_author is set to the wrong id  expected root (found will_not_work) (user root)
    at Utils.main (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/sk_types.mjs:199:29)
    at SKDBSyncImpl.main [as runLocal] (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/skdb_skdb.mjs:202:33)
    at file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/skdb_database.mjs:80:29
    at SKDBSyncImpl.runner (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/skdb_skdb.mjs:193:30)
    at SKDBSyncImpl.exec (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/skdb_database.mjs:78:25)
    at SKDBImpl.exec (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/skdb_database.mjs:275:46)
    at file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/src.mjs:30:14
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 1,
  cause: SkException [Error]: skdb_author is set to the wrong id  expected root (found will_not_work) (user root)
      at Utils.ethrow (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/sk_types.mjs:342:23)
      at LinksImpl.js_throw (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/sk_runtime.mjs:30:53)
      at toWasm.js_throw (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/e68feef3-3bec-486f-b0dd-d7b494227cb44479666635330745170/node_modules/skdb/dist/sk_runtime.mjs:127:58)
      at SKIP_throw (wasm://wasm/010cfefa:wasm-function[7053]:0x146686)
      at sk.SKDB_AccessSolver__checkWrite (wasm://wasm/010cfefa:wasm-function[5407]:0xd0d91)
      at sk.SKStore_EagerDir__writeEntry (wasm://wasm/010cfefa:wasm-function[3881]:0x7f014)
      at sk.SKDB_Evaluator__insertTable (wasm://wasm/010cfefa:wasm-function[4166]:0x91430)
      at SKDB.Evaluator__insert__Closure2__call__Closure0__call (wasm://wasm/010cfefa:wasm-function[4165]:0x912a1)
      at sk.vtry__Closure0__call.16 (wasm://wasm/010cfefa:wasm-function[4959]:0xbb5c0)
      at SKIP_call0 (wasm://wasm/010cfefa:wasm-function[6918]:0x141570)
}

Define application rules to control which data updates are allowed

SKDB provides 'CHECK expressions' that can be used with reactive views. CHECK expressions are a powerful mechanism for expressing and maintaining data constraints in the database.

When you define your schema, you can create reactive views that use CHECK expressions. This allows you to define application rules at schema definition time and reject data updates from users that do not meet these rules.

With a client created, here is a simple example of a reactive view that ensures a data constraint:

await skdb.exec(`
  CREATE TABLE accounts(id text PRIMARY KEY, balance integer NOT NULL);
`);

await skdb.exec(`
  CREATE REACTIVE VIEW account_balance_check AS
  SELECT
    CHECK(balance >= 0) AS balance_is_non_negative
  FROM
    accounts
`);

// ok
await skdb.exec("INSERT INTO accounts VALUES ('example_account', 100)");

// not ok
try {
  await skdb.exec(`
    UPDATE
      accounts
    SET
      balance = balance - 200
    WHERE
      id = 'example_account'
  `);
} catch (ex) {
  console.log(ex);
}

SkRuntimeExit [Error]: ^
|
 ----- ERROR
Error: line 1, character 0:
Failed constraint 'balance_is_non_negative': balance >= 0
    at Utils.main (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/sk_types.mjs:199:29)
    at SKDBSyncImpl.main [as runLocal] (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/skdb_skdb.mjs:202:33)
    at file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/skdb_database.mjs:80:29
    at SKDBSyncImpl.runner (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/skdb_skdb.mjs:193:30)
    at SKDBSyncImpl.exec (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/skdb_database.mjs:78:25)
    at SKDBImpl.exec (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/skdb_database.mjs:275:46)
    at file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/src.mjs:19:14 {
  code: 1,
  cause: SkException [Error]: Failed constraint 'balance_is_non_negative': balance >= 0
      at Utils.ethrow (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/sk_types.mjs:342:23)
      at LinksImpl.js_throw (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/sk_runtime.mjs:30:53)
      at toWasm.js_throw (file:///private/var/folders/v3/mnrsv2tn40ncb6699fhczzgc0000gn/T/dfcdbed1-3ac7-4150-b373-2432fb90998918347521571839203848/node_modules/skdb/dist/sk_runtime.mjs:127:58)
      at SKIP_throw (wasm://wasm/010cfefa:wasm-function[7053]:0x146686)
      at sk.SKDB_ExprEvaluator__evalCIExpr (wasm://wasm/010cfefa:wasm-function[3668]:0x753f7)
      at sk.SKDB_ExprEvaluator__evalCGExpr (wasm://wasm/010cfefa:wasm-function[3746]:0x786a0)
      at sk.SKDB_SelectEvaluator___ConcreteMetaImpl__evalRow (wasm://wasm/010cfefa:wasm-function[3510]:0x6a7bd)
      at sk.SKDB_SelectEvaluator__evalSelectRow (wasm://wasm/010cfefa:wasm-function[6659]:0x131ff0)
      at sk.SKDB_SelectEvaluator__evalSelectTableRow (wasm://wasm/010cfefa:wasm-function[6655]:0x131c55)
      at sk.SKDB_SelectEvaluator__evalSelectTableRows (wasm://wasm/010cfefa:wasm-function[3503]:0x6a085)
}

A CHECK expression can be used on any SELECT query. If the expression is false for any row, the query fails with an error. This is primarily useful when used with a reactive view. When we use CHECK with a reactive view the CHECK expression is evaluated

  • whenever the database is updated. On any update to a table, SKDB propagates this in a cascade to all dependent reactive views. If a constraint no longer holds it will fail, cascading back up and causing the transaction as a whole to fail.

  • only when it needs to be. The SKDB engine was purpose built to maintain queries incrementally. As data is changed in source table(s) any dependent views are updated very efficiently.

Because the check is expressed in a select query it works with anything that you can do with SELECT. You can express constraints on rows and columns, aggregated data using GROUP BY, and even across tables with joins.

You can define reactive views

  • remotely to ensure that illegal updates are not accepted and propagated to other users
  • locally to prevent errors without going to the server and to ensure the application works entirely offline
  • or both locally and remotely, for the best of both worlds

Examples

Here are some example queries to get a sense of what you can achieve with this.

We can ensure that new rows are written using a particular skdb_access group id. Here we express that posts for a group of users must use the group's SKDB group.

CREATE REACTIVE VIEW post_access_check AS
SELECT
  CHECK(post.skdb_access = grp.members) AS group_posts_use_correct_skdb_access
FROM
  posts post
  INNER JOIN groups grp ON p.grp = g.group_id;

Notice that we are making use of a join to do this. This is impossible to express using traditional table CHECK expressions.

We are only specifying constraints in this query, so it is ephemeral: there is nothing stored outside of what is necessary to make the view incremental, it exists purely for the side effect that will prevent writes from breaking the invariant.

But if we want, we can also materialise data to make this reactive view usable for queries as well as checking writes:

CREATE REACTIVE VIEW post_access_check AS
SELECT
  post.title AS post_title,
  grp.name AS group_posted_to,
  CHECK(post.skdb_access = grp.members) AS group_posts_use_correct_skdb_access
FROM
  posts post
  INNER JOIN groups grp ON p.grp = g.group_id;

To demonstrate how aggregations can be useful, here's how we could prevent a user spamming they system with requests. After 5, further requests will be rejected.

CREATE REACTIVE VIEW requests_backpressure AS
SELECT
  CHECK(count(*) <= 5) AS limit_total_incomplete_reqs_per_user
FROM
  requests r
WHERE
  r.status != 'completed'
GROUP BY
  skdb_author