Setup your local embedded SKDB database¶
Create an SKDB database¶
The following example will create a local SKDB database.
The skdb instance allows you to interact with the local database.
The database is initially empty but can be populated by running SQL
statements or by mirroring tables and views from the server.
The above SKDB instance will run on the main thread.
Options supported are:
asWorker- boolean - create the instance as a worker?disableWarnings- boolean - if true - defaults to false - silence SKDB from logging warnings to the console.
Connect to a cloud database and mirror data¶
Connect to the cloud as the root user¶
With an SKDB client created, you can connect to a server and mirror tables or reactive views.
Assuming our database is called "example_db":
const keyBytes = Buffer.from(
"<EXAMPLE_DB_ROOT_PRIVATE_KEY_BASE_64>",
"base64",
);
const key = await crypto.subtle.importKey(
"raw",
keyBytes,
{ name: "HMAC", hash: "SHA-256" },
false,
["sign"], // private keys are never sent over the wire, but are used
// to sign messages, proving that the originator knows the secret key
);
await skdb.connect("example_db", "root", key);
const remote = await skdb.connectedRemote();
console.log("Successfully connected as:", await remote.connectedAs());
await skdb.closeConnection();
And we see that we've established a connection as the 'root' user.
The root user, much like on a Unix system, is the superuser of the database. Only the root user can add users and execute queries directly on the remote database (e.g. defining schema or examining data).
Specify the tables and views to mirror¶
With a connection established to the server, you can begin mirroring tables or views.
Assuming we have a remote table mirror_demo_table already setup,
then we can mirror it:
Mirroring creates a table locally with the same schema as the remote table or view and keeps both synchronized: any changes made to either will be instantly reflected to the other. When mirroring a view, the local table is read only.
The above code is convenient for development but we should specify the columns we expect. Specifying the columns ensures the application will get data in this shape even if the schema changes on the server.
await skdb.mirror({
table: "mirror_demo_table",
expectedColumns: `(
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT,
skdb_access TEXT NOT NULL
)`,
});
console.log("mirror_demo_table:", await skdb.exec("SELECT * FROM mirror_demo_table"));
We were able to successfully query the table, but it does not have any data yet.
You can pass as many table definitions to mirror as you need. SKDB will keep them all synchronized.
NOTE: the list of tables provided to mirror replaces the current mirror setup. This allows you to stop mirroring tables and change any filters.
Stop mirroring all tables:
Mirror only some of the data in a table¶
You can mirror all of the data in a table or you can choose to mirror a subset.
When passing a mirror definition to mirror you can also specify a
filter. This is any valid SQL expression that SKDB supports. Only the
rows where this expression evaluates to true are mirrored (you can
imagine that the mirror has a WHERE clause).
Anything written locally - even if the filter expression isn't true - will be synchronized to the server, but the client will only receive updates for new and existing rows passing the filter.
You can pass a simple filter string:
await skdb.mirror({
table: "mirror_demo_table",
expectedColumns: `(
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT,
skdb_access TEXT NOT NULL
)`,
filterExpr: "f > 21.5 AND n % 2 = 0",
});
Or, if you need to pass in data to your filter expression, you can use parameters. This is convenient but also ensures that the input is not interpreted as SQL, avoiding SQL injection attacks.
const prefix = "foo";
await skdb.mirror({
table: "mirror_demo_table",
expectedColumns: `(
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT,
skdb_access TEXT NOT NULL
)`,
filterExpr: "t LIKE @searchText",
filterParams: {
searchText: prefix + "%",
},
});
Define additional schema for your local database¶
Create a local-only table¶
You may wish to work with data locally that does not need to be shared. You can create and use tables as you would expect.
With an SKDB client created, you create a table by
passing a SQL statement to exec.
Here we create a table test with four columns representing some of
the data types that SKDB supports.
await skdb.exec(
`CREATE TABLE test(
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT
);`
);
console.log("Database schema:")
console.log(await skdb.schema());
console.log("test table:", await skdb.exec("SELECT * FROM test"));
You should see
Database schema:
CREATE TABLE test (
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT
);
test table: SKDBTable(0) []
Create a reactive view¶
Reactive views materialize queries - they build and maintain a new read only table defined by any SQL query supported by SKDB. This allows the resulting table to be queried without recomputing the view constantly.
This allows you to very efficiently maintain expensive joins, filters, and aggregations, for example.
The SKDB engine was purpose built to maintain reactive views incrementally. As data is changed in source table(s) any dependent views are updated very efficiently. SKDB knows if the view is affected by the update and will update just the rows that need to be.
Let's assume that we have created a local table -- but this of course works for mirrored tables too.
We can build a reactive view using exec:
await skdb.exec(
`CREATE REACTIVE VIEW test_totals AS
SELECT
t,
sum(n) AS total
FROM
test
GROUP BY
t;`
);
console.log("Schema with the reactive view:")
console.log(await skdb.schema());
await skdb.exec("INSERT INTO test (t, n) VALUES ('foo', 1), ('bar', 1), ('foo', 7)");
console.log("Querying the table:")
console.table(await skdb.exec("SELECT t, n FROM test"));
console.log("Querying the view:")
console.table(await skdb.exec("SELECT * FROM test_totals ORDER BY total"));
Schema with the reactive view:
CREATE TABLE test (
id TEXT PRIMARY KEY,
n INTEGER,
f FLOAT,
t TEXT
);
CREATE REACTIVE VIEW test_totals AS SELECT t, sum(n) AS total FROM test GROUP BY t;
Querying the table:
┌─────────┬───────┬───┐
│ (index) │ t │ n │
├─────────┼───────┼───┤
│ 0 │ 'bar' │ 1 │
│ 1 │ 'foo' │ 7 │
│ 2 │ 'foo' │ 1 │
└─────────┴───────┴───┘
Querying the view:
┌─────────┬───────┬───────┐
│ (index) │ t │ total │
├─────────┼───────┼───────┤
│ 0 │ 'bar' │ 1 │
│ 1 │ 'foo' │ 8 │
└─────────┴───────┴───────┘
Reactive views can be built on top of reactive views and the whole graph will be maintained incrementally.
Create an index¶
SKDB supports indexing data. This allows you to greatly speed up queries.
You can index one or more columns on any table and you can also index reactive views.
Let's demonstrate with a locally created table.
const N = 10000;
for (let i = 0; i < N; i++) {
await skdb.exec("INSERT INTO test (n) VALUES (@i)", {i});
}
console.time("without an index");
await skdb.exec("SELECT n FROM test WHERE n IN (21, 999, 5432)");
console.timeEnd("without an index");
// define the index
await skdb.exec("CREATE INDEX test_n ON test(n)");
console.time("with an index");
await skdb.exec("SELECT n FROM test WHERE n IN (21, 999, 5432)");
console.timeEnd("with an index");
Define local data constraint rules¶
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