Skip to content

Client/Server

SKDB is composed of two main components: the client and the server. Both sides are SQL databases that are kept in sync automatically.

There is a production and a dev SKDB server. Currently, for the alpha release, only the dev server is available.

The dev server runs locally and has extra features to make developing and testing applications with SKDB easy. User creation is a breeze and credential management is handled for you. To keep you moving quickly you just state the schema you want. As this definition is updated SKDB will make any changes needed and auto-migrate your test data.

In the future, SKDB client libraries will be available in a number of languages. For the current alpha release, we are making available a JavaScript library, which can be used to write clients that run in either a browser or a Node environment (using WASM in either case).

Getting Started

To connect to the dev server, specify the schema of the database, get a local database instance, and begin mirroring tables, this code should get you started.

import { skdbDevServerDb, createLocalDbConnectedTo } from 'skdb-dev'

async function getStarted(): Promise<SKDB> {
  const remoteDb = await skdbDevServerDb("database-name");

  await remoteDb.schema(
    "CREATE TABLE example_table (id STRING PRIMARY KEY, x STRING, y INTEGER, z FLOAT, skdb_access STRING);",
    "CREATE TABLE another_example (id STRING PRIMARY KEY, x STRING, y INTEGER, z FLOAT, skdb_access STRING);",
  );

  const localDb = await createLocalDbConnectedTo(remoteDb);

  await localDb.mirror({table: "example_table", expectedColumns: "(id STRING PRIMARY KEY, x STRING, y INTEGER, z FLOAT, skdb_access STRING)"});

  // begin populating data and sending it to the server with a local SQL statement
  await localDb.exec("INSERT INTO example_table VALUES (id(), 'foo', 42, 99.9, 'read-write');")

  return localDb;
}

API

Remote connection (dev-mode)

  • Function: skdbDevServerDb
  • Purpose: Creates a new database on the server in dev-mode.
  • Usage:
    const remoteDb = await skdbDevServerDb("database-name", "localhost", port);
    
  • Imports:
    import { skdbDevServerDb } from 'skdb-dev'
    

Local connection (dev-mode)

  • Function: createLocalDbConnectedTo
  • Purpose: Creates a new local database in dev-mode, connected to the given remote.
  • Usage:
    // remoteDb was created using skdbDevServerDb
    const localDb = await createLocalDbConnectedTo(remoteDb, userID);
    
  • Imports:
    import { createLocalDbConnectedTo } from 'skdb-dev'
    

Mirroring

  • Method: localDb.mirror
  • Purpose: Request one or more tables from the server, creating a local database representation.
  • Usage:
    await localDb.mirror(table_1, ..., table_n);
    await localDb.mirror({table: "example_table", expectedColumns: "(id STRING PRIMARY KEY, x STRING, y INTEGER, z FLOAT, skdb_access STRING)"})
    
  • This defines the set of tables that will be mirrored. Subsequent calls replace this set.
  • Imports:
    import type { SKDB } from 'skdb'
    
  • Parameters:
  • mirror takes one or more arguments, with each being an object that specifies how to mirror a table. Object properties are:
    • table The name of the table to be mirrored. Mandatory.
    • expectedColumns The schema of the table you expect to see as a string. Specifying this allows SKDB to detect, and in some cases adapt to, clients with out-of-date views of the database. Mandatory, but if passed * the remote schema will be mirrored unconditionally.
    • filterExpr and filterParams allow you to only mirror a subset of data locally. Optional.

Note: Mirroring respects server-side access constraints. Data transferred will only contain what the authenticated user can see, following SKDB privacy rules.

  • Replication: Changes made locally are automatically reflected on the server.

Querying the Local Database

  • Method: localDb.exec
  • Purpose: Run SQL queries on your local mirrored database.
  • Usage:
    const query = "SELECT * FROM skdb_users WHERE userID = @id";
    const parameters = {id: 1234};
    const result = await localDb.exec(query, parameters);
    
  • Recommendation: Always use parameterized queries to avoid SQL injection vulnerabilities. Parameters are indicated with the '@' symbol in your SQL query.

Rejected updates

In rare scenarios the server may reject changes to tables. This could happen if, for example, you update a row while privacy rules are being concurrently updated that remove your access.

Rejected rows are sent back and stored in a table that SKDB creates for you. This table is always named after the mirrored table with __skdb_mirror_feedback appended.

For example, if you mirror a table called example_table, SKDB will create example_table and also example_table__skdb_mirror_feedback.

example_table__skdb_mirror_feedback will have the same schema as example_table and you can query it like you would any table. You may wish to use localDb.exec if there are points in your application where it makes sense to check for and handle any rejected rows. Or you may wish to monitor this by subscribing to the table with localDb.watch or localDb.watchChanges.