Create and modify existing data¶
Insert, update, and delete data¶
To modify data, run a SQL INSERT, UPDATE, or DELETE statement using exec.
Illustrating with a local table:
await skdb.exec("INSERT INTO test (n, f, t) VALUES (1, 2.1, '3')");
console.log("After insert:");
console.table(await skdb.exec("SELECT * FROM test"));
await skdb.exec("UPDATE test SET n = n + 1");
console.log("After update:");
console.table(await skdb.exec("SELECT id, n FROM test"));
await skdb.exec("DELETE FROM test");
console.log("After delete:");
console.table(await skdb.exec("SELECT * FROM test"));
After insert:
┌─────────┬───────────────────────────────┬───┬─────┬─────┐
│ (index) │ id │ n │ f │ t │
├─────────┼───────────────────────────────┼───┼─────┼─────┤
│ 0 │ '3g477P96hiOtWi1JaIzF1tJ9570' │ 1 │ 2.1 │ '3' │
└─────────┴───────────────────────────────┴───┴─────┴─────┘
After update:
┌─────────┬───────────────────────────────┬───┐
│ (index) │ id │ n │
├─────────┼───────────────────────────────┼───┤
│ 0 │ '3g477P96hiOtWi1JaIzF1tJ9570' │ 2 │
└─────────┴───────────────────────────────┴───┘
After delete:
┌─────────┐
│ (index) │
├─────────┤
└─────────┘
Parameterize modification queries¶
Query parameters allow you to easily include application data in your queries. This is convenient but also ensures that the input data is not interpreted as SQL, avoiding SQL injection attacks.
Recommendation: Always use parameterized queries to avoid SQL injection vulnerabilities.
Continuing from our basic modification statements example, this illustrates the use of query parameters.
const count = 2;
const oneHalf = 0.5;
const interestingText = "foo";
await skdb.exec(
"INSERT INTO test (n, f, t) VALUES (@count, @oneHalf, @interestingText)",
{count, oneHalf, interestingText},
);
console.log("After insert:");
console.table(await skdb.exec("SELECT * FROM test"));
await skdb.exec("UPDATE test SET n = n + @count", {count});
console.log("After update:");
console.table(await skdb.exec("SELECT id, n FROM test"));
await skdb.exec(
"DELETE FROM test WHERE f = @val",
{val: oneHalf},
);
console.log("After delete:");
console.table(await skdb.exec("SELECT * FROM test"));
After insert:
┌─────────┬───────────────────────────────┬───┬─────┬───────┐
│ (index) │ id │ n │ f │ t │
├─────────┼───────────────────────────────┼───┼─────┼───────┤
│ 0 │ '3g475UTVHBoUepzvsurvYfucEJ1' │ 2 │ 0.5 │ 'foo' │
└─────────┴───────────────────────────────┴───┴─────┴───────┘
After update:
┌─────────┬───────────────────────────────┬───┐
│ (index) │ id │ n │
├─────────┼───────────────────────────────┼───┤
│ 0 │ '3g475UTVHBoUepzvsurvYfucEJ1' │ 4 │
└─────────┴───────────────────────────────┴───┘
After delete:
┌─────────┐
│ (index) │
├─────────┤
└─────────┘
Parameters are indicated with the '@' symbol in the SQL query. They
are replaced with values from the application as per the map that is
passed to exec.
Write atomically with transactions¶
SKDB supports transactions. You can use transactions to atomically modify one or more tables, using one or more SQL statements.
Let's demonstrate with a local table.
We atomically insert two values:
await skdb.exec(`
BEGIN TRANSACTION;
INSERT INTO test (n) VALUES (0);
INSERT INTO test (n) VALUES (1);
COMMIT;
`);
These rows will either both be inserted or neither. This is true for the synchronisation with the server too. If this table was a mirrored table, both rows will be successfully replicated to the server, or neither will be.
The id() function generates a unique id. Within a transaction you
can SELECT it to retrieve the ID just generated. This is a very useful
pattern. If you need multiple ids, you can pass a string argument to
distinguish them.
const table = await skdb.exec(`
BEGIN TRANSACTION;
INSERT INTO
test
VALUES
(id('new_row'), 1, 2.1, '3');
SELECT id('new_row');
COMMIT;
`);
const id = table.scalarValue();
console.log(await skdb.exec("SELECT * FROM test WHERE id = @id", {id}));
Build transactions in code using SKDBTransaction¶
Building a transaction in code can be more convenient with the SKDBTransaction class. Replicating the previous example:
import { SKDBTransaction } from 'skdb';
const rows = await new SKDBTransaction(skdb)
.add("INSERT INTO test (n) VALUES (@a);")
.addParams({a: 98})
.add("INSERT INTO test (n) VALUES (@b);")
.addParams({b: 99})
.add("SELECT * FROM test WHERE n IN (@a, @b)")
.commit();
console.table(rows);
┌─────────┬───────────────────────────────┬────┬──────┬──────┐
│ (index) │ id │ n │ f │ t │
├─────────┼───────────────────────────────┼────┼──────┼──────┤
│ 0 │ '3g476ISm8-tB8CQYmrXT5C_2c2B' │ 99 │ null │ null │
│ 1 │ '3g476UPJx_maCxAAyLTuFGuEDc' │ 98 │ null │ null │
└─────────┴───────────────────────────────┴────┴──────┴──────┘
Write to the skdb_author column¶
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:
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)
}