WebAssembly triggers in libSQL
User-defined WebAssembly functions in libSQL can be used to create powerful triggers, making it easy to automate your workflows. The first official release of libSQL, our open-contribution fork of…
Piotr Sarna
The first official release of libSQL, our open-contribution fork of SQLite, brought the ability to dynamically create WebAssembly-powered user-defined functions. That's an exciting feature on its own, but combined with database triggers, they become a powerful building block for automating your workflows.
#Database triggers
Database triggers are an ancient feature in the database world, implemented in most products, and libSQL is no exception. A trigger is simply a stored procedure, which fires automatically when a specific event happens in the database. In libSQL (and SQLite), the following actions can activate a trigger:
- INSERT
- UPDATE
- DELETE
Moreover, the trigger can be set up to execute before, after, or instead of the original action, with a few restrictions.
The syntax of the CREATE TRIGGER statement is very expressive:
CREATE [TEMP|TEMPORARY] TRIGGER [IF NOT EXISTS] trigger
[BEFORE|AFTER|INSTEAD OF]
[INSERT|UPDATE OF [column_name*]|DELETE]
ON table_name
[FOR EACH ROW] [WHEN condition]
BEGIN
[statement*];
END;
But worry not, we'll go through a few simple examples.
#WebAssembly user-defined functions
libSQL 0.1.0 allows users to create user-defined functions not only programmatically, but also with the CREATE FUNCTION statement, well known in the world of SQL.
WebAssembly was chosen due to its robustness, portability and security, as well as performance. As was explained in our previous article, WebAssembly user-defined functions can be easily implemented in Rust with the help of libsql_bindgen library. To make it even more ergonomic, we host an interactive libSQL bindgen app — you can also deploy your own mirror from our repository.
#Use case: users management
In this example, we'll implement a database flow for creating new user records, making sure that their passwords are not stored in plaintext. We'll rely on database triggers powered by WebAssembly user-defined functions to automate the work, offloading the complexity to the database layer.
Later, we'll also introduce email verification based on sending single-use tokens sent to the users.
Here's a diagram of the flow implemented in this post:
The users table is defined with the following schema:
CREATE TABLE users(
user TEXT PRIMARY KEY,
email TEXT,
pass TEXT
);
#Encrypting user passwords
The first part of the workflow is to save passwords in an encrypted form instead of plaintext. Let's create a table with secret values, used to encrypt the passwords of newly created users:
CREATE TABLE secrets(secret text);
INSERT INTO secrets VALUES ('s3cretk3y');
Next, we need an encryption function. Here's where WebAssembly user-defined functions become useful. With libSQL bindgen, the following Rust snippet can be compiled to Wasm and registered as a user-defined function called encrypt:
pub fn encrypt(data: String, key: String) -> String {
use magic_crypt::MagicCryptTrait;
let mc = magic_crypt::new_magic_crypt!(key, 256);
mc.encrypt_str_to_base64(data)
}
Save the generated SQL to a local file and load it in libsql shell by calling .read <path-to-your-file> and voilà, the function is registered! You can also try copy-pasting the SQL right into libsql shell.
With the secrets and the encrypt() function available, our first trigger can be created like this:
CREATE TRIGGER encrypt_pass AFTER INSERT ON users
BEGIN
UPDATE users
SET pass = encrypt(
new.pass,
(SELECT secret FROM secrets LIMIT 1)
)
WHERE user = new.user;
END;
Immediately after a new row is inserted into users, the provided value for pass is replaced with the encrypted value using the result of the function. The encryption function takes two parameters: the password, and the value of today's secret key, selected from the secrets table we previously created.
#Verifying the trigger
In order to check if encryption works, a corresponding function to perform description would be quite handy. Here's it source code, which you again send to bindgen to generate the code for a UDF:
pub fn decrypt(data: String, key: String) -> String {
use magic_crypt::MagicCryptTrait;
let mc = magic_crypt::new_magic_crypt!(key, 256);
mc.decrypt_base64_to_string(data)
.unwrap_or_else(|_| "[ACCESS DENIED]".to_string())
}
Let's check if the trigger works by adding a few entries to the table and inspecting its contents:
INSERT INTO users VALUES (
'peter',
'peter@chiselstrike.com',
'roe-deer'
);
INSERT INTO users VALUES (
'iku',
'iku@chiselstrike.com',
'turso'
);
SELECT user, pass, decrypt(
pass,
(SELECT secret FROM secrets LIMIT 1)
) AS decrypted
FROM users;
user pass decrypted
----- ------------------------ ---------
peter 1mXVhOvX0YWGHcfEYCvqgg== roe-deer
iku GeJmXZujKUJbRai+S/4cBA== turso
SELECT user, pass, decrypt(
pass,
'incorrect-pass'
) AS decrypted
FROM users;
user pass decrypted
----- ------------------------ ---------------
peter 1mXVhOvX0YWGHcfEYCvqgg== [ACCESS DENIED]
iku GeJmXZujKUJbRai+S/4cBA== [ACCESS DENIED]
Perfect — the passwords are stored in their encrypted form, but they can still be decoded with the proper key.
#Email verification
But we're not done exploring the capabilities of Wasm triggers, far from it! In the next step, we'll harden the user management flow by generating a unique single-use token for each user and putting it in a separate table. Later, entries from this table can be processed by an external program which sends a message to each newly created user, asking them to confirm their email by submitting the token generated just for them.
The table prepared for storing single-use tokens has the following schema:
CREATE TABLE tokens(
user text PRIMARY KEY,
date text,
token text
);
Here's a Rust implementation of the single_use_token function, which accepts a username and current time as arguments, and returns the generated token:
pub fn single_use_token(user: String, seed: u64) -> String {
use rand::{Rng, SeedableRng};
use std::hash::{Hash, Hasher};
let mut hasher = std::collections::hash_map::DefaultHasher::new();
let hash = user.hash(&mut hasher);
let token = rand::rngs::StdRng::seed_from_u64(seed + hasher.finish())
.gen::<u64>();
format!("{:x}", token)
}
And now, the password-encrypting trigger we created in the previous paragraph can be boosted with another action — notice that multiple operations can be performed by a single trigger, so we can just drop the previous one and replace it with a new one:
DROP TRIGGER IF EXISTS encrypt_pass;
CREATE TRIGGER encrypt_pass_and_generate_token
AFTER INSERT ON users
BEGIN
UPDATE users
SET pass = encrypt(
new.pass,
(SELECT secret FROM secrets LIMIT 1)
)
WHERE user = new.user;
INSERT INTO tokens VALUES (
new.user,
date(),
single_use_token(new.user, unixepoch())
);
END;
#Verification in practice
Finally, let's verify that a single-use token was indeed generated and inserted into the tokens table.
INSERT INTO users VALUES (
'wojtek',
'wojtek@ii_corps.pl',
'the bear'
);
SELECT * FROM tokens WHERE user = 'wojtek';
user date token
------ ---------- ----------------
wojtek 2022-12-16 48e2873b1c5d861b
#Summary
WebAssembly integration brought portable and secure user-defined functions to libSQL in its first official release. They are useful not only for queries, but also as building blocks for database triggers. With the help of both features, more business logic can be pushed to the database layer, with computations performed closer to the data. This improves performance, security, and level of control over your data.
Read more about WebAssembly user-defined functions in our previous blog post. If you want to get started working with libSQL, you can jump into our GitHub repo.
