Skip to content

CLI

SKDB comes with a CLI which can be useful to experiment with, in order to familiarize yourself with SKDB or to better understand your own schema and data.

Running the CLI

The easiest way to explore SKDB's CLI is to use the dev server, which you can run with:

$ docker run -it -p 3586:3586 skiplabs/skdb-dev-server
...
------------------------------------------------------
The following dev resources are available:
GET /dbs/{database}/users
PUT /dbs/{database}/schema
------------------------------------------------------

We can then run the CLI with npx skdb:

npx skdb --dev --db demo --remote-repl

If you don't have the skdb NPM package installed, npx will first download and install it. The --dev flag tells the CLI to connect to a dev server instance running at localhost port 3586, allowing us to bypass the usual authorization and credentials system. --db demo says to connect to the database called "demo", which the dev server will automatically create if it does not exist (note that a SKDB production server does not auto-create databases). --remote-repl says that we want to start a REPL on the server with its "remote" view of the data. When the CLI starts we will see a prompt telling us what user and on what SKDB instance we are connected to:

$ npx skdb --dev --db demo --remote-repl
root@ws://localhost:3586/demo>

The CLI accepts both normal SQL statements as well as special CLI commands, with the latter having a . prefix. You can can get help with .help; you can exit the CLI with .exit.

Remote and local views

SKDB is unique because it gives programmers both a "permanent remote" view of the data on the server and a "temporary local" view of the data. By default, an SKDB database connection accesses data via the remote server, much like a normal database, but we can request that data is cached locally. This seemingly small difference is key to understanding SKDB.

Let's start with the "permanent remote" view. After running the CLI, we are logged in as root. We can see the database schema by executing .schema which prints output along the lines of:

root@ws://localhost:3586/demo> .schema                                          
CREATE TABLE skdb_group_permissions (                                           
  groupUUID TEXT NOT NULL,                                                      
  userUUID TEXT,                                                                
  permissions INTEGER NOT NULL,
  skdb_access TEXT NOT NULL
);
CREATE TABLE skdb_groups (
  groupUUID TEXT PRIMARY KEY,
  skdb_author TEXT,
  adminUUID TEXT NOT NULL,
  skdb_access TEXT NOT NULL
);
...

Tables beginning with skdb_ have special meaning to SKDB, mostly related to permissions.

We can create our own table in mostly normal fashion, though for reasons we'll soon see, we want to add a column with the special name skdb_access and type STRING:

root@ws://localhost:3586/demo> CREATE TABLE test (name STRING, age INTEGER, skdb_access STRING );

We can also insert and read values from it:

root@ws://localhost:3586/demo> INSERT INTO test (name, age, skdb_access) VALUES ('Alice', 42, 'read-write');
root@ws://localhost:3586/demo> SELECT * FROM test;
┌─────────┬─────────┬─────┬──────────────┐
 (index)   name    age  skdb_access  
├─────────┼─────────┼─────┼──────────────┤
    0     'Alice'  42   'read-write' 
└─────────┴─────────┴─────┴──────────────┘

This mostly looks like any other SQL database, with the exception of the skdb_access column. This column allows us to define who, other than the root user (which, like root in Unix, is all-powerful), can read, update, or delete a row. SKDB has a powerful concept of users and groups but, for now, we'll use the predefined read-write group which, as its name might suggest, allows all users to read, update, or delete the row.

With that in mind, we can now switch to what makes SKDB unique — the "temporary local" views. We can switch to the local view with .local:

root@ws://localhost:3586/demo> .local
local>

Note the prompt has now become local>. Alternatively we could have started the database in this mode by replacing --remote-repl with --local-repl.

As with the "permanent remote" view, the CLI accepts SQL statements and CLI commands prefixed with .. Despite the seeming similarity, the differences between "permanent remote" and "temporary local" are profound. The first hint of that is that, if we ask to see the local database schema, nothing is printed:

local> .schema

We have to explicitly tell SKDB what subset of the remote data we're interested in dealing with locally, by asking it to mirror specific table(s). Let's mirror our test table:

local> .mirror-table test

We can now execute a query on the mirrored table:

local> SELECT * FROM test;
┌─────────┬─────────┬─────┬──────────────┐
 (index)   name    age  skdb_access  
├─────────┼─────────┼─────┼──────────────┤
    0     'Alice'  42   'read-write' 
└─────────┴─────────┴─────┴──────────────┘

The data from the remote server is now available locally, and we can execute normal SQL commands on our local database:

local> INSERT INTO test (name, age, skdb_access) VALUES ('Bob', 43, 'read-write');
local> SELECT * FROM test;
┌─────────┬─────────┬─────┬──────────────┐
 (index)   name    age  skdb_access  
├─────────┼─────────┼─────┼──────────────┤
    0     'Alice'  42   'read-write' 
    1      'Bob'   43   'read-write' 
└─────────┴─────────┴─────┴──────────────┘

What’s happening on the server? We can return there with the .remote command and see:

local> .remote
root@ws://localhost:3586/demo> SELECT * FROM test;
┌─────────┬─────────┬─────┬──────────────┐
 (index)   name    age  skdb_access  
├─────────┼─────────┼─────┼──────────────┤
    0     'Alice'  42   'read-write' 
    1      'Bob'   43   'read-write' 
└─────────┴─────────┴─────┴──────────────┘

As this shows, updates are propagated back to the remote view automatically.

root@ws://localhost:3586/demo> UPDATE test SET age = 44 WHERE name='Alice';
root@ws://localhost:3586/demo> .local
local> SELECT * FROM test;
┌─────────┬─────────┬─────┬──────────────┐
 (index)   name    age  skdb_access  
├─────────┼─────────┼─────┼──────────────┤
    0     'Alice'  44   'read-write' 
    1      'Bob'   43   'read-write' 
└─────────┴─────────┴─────┴──────────────┘

Updates are also propagated from the local to the remote view — provided you have the permissions to do so! In the following example, root in the remote inserts a read-only row: the local client can read that row but not update it. However, the local client can update read-write rows:

This example will change slightly when the client implements privacy checks

local> .remote
root@ws://localhost:3586/demo> INSERT INTO test (name, age, skdb_access) VALUES ('Charlie', 45, 'read-only');
root@ws://localhost:3586/demo> .local
local> UPDATE test SET age=41 WHERE name='Bob';
local> UPDATE test SET age=46 WHERE name='Charlie';
local> .remote
root@ws://localhost:3586/demo> SELECT * FROM test;
┌─────────┬───────────┬─────┬──────────────┐
 (index)    name     age  skdb_access  
├─────────┼───────────┼─────┼──────────────┤
    0      'Alice'   44   'read-write' 
    1       'Bob'    41   'read-write' 
    2     'Charlie'  45   'read-only'  
└─────────┴───────────┴─────┴──────────────┘

Although it's hard to show in textual format, try firing up a second SKDB CLI, performing inserts (and maybe deletes too!) in "local" mode while keeping the first CLI open in local mode too -- you'll see updates synchronize immediately between the two.

If you're feeling very adventurous, disable the network on one of the CLIs, make updates and check the other CLI: you shouldn't see those updates. Then reenable the update and, probably quicker than you can switch between the two, you'll notice that the updates have magically propagated across!