Database

The database is the central component of the snip project. It stores all the data that is used to render the pages. This includes the books, pages, snips, and all the metadata associated with them. By default the database is a MariaDB database.

Migration scripts

Migrating the database is a common task when developing a project. We use a custom python script which is run on each startup of the server. The script checks the current version of the database and runs all migration scripts that are newer than the current version. The migration scripts are stored in the database directory. The script is run as follows:

python database/mariaDB_migrate.py

This scripts looks for sql files in the migration sub directory and runs them in order. The script also stores the current version in an additional info table in the database. This way the script can check if a migration has already been run and only run the new ones.

Interaction with the database

We wrote a small package @snip/database to interact with the database and provide common functions. This package is located in the packages/database directory. It allows to interact with most of the tables in the database and provides a number of helper functions.

To have full type safety we automatically parse the database schema and generate typescript types from it. This way we can ensure that the types in the database and the types in the code are always in sync. The types are generated by the prep script in the packages/scripts directory and are run before starting the fullstack app.

Custom errors raised in the Database

When working with databases, it is common to define procedures that way raise custom errors. This is useful to provide more context to the caller about what went wrong. See the following table for a list of custom errors that are raised by the database.

If adding a new custom error, make sure to add it to the table below and also not reuse an existing error code. All errors are raised as follows:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'My custom error message', MYSQL_ERRNO = ?;

Example

For instance the procedure to update an invite status looks as follows:

CREATE OR REPLACE PROCEDURE update_invite_status(IN invite_id INT)
BEGIN
    DECLARE invite_status SMALLINT;

    -- Check if the invite is expired or already accepted
    SELECT 
        CASE
            WHEN expire_at <= NOW() THEN 2 -- expired
            WHEN accepted_at IS NOT NULL THEN 1 -- accepted
            ELSE 0 -- valid
        END INTO invite_status
    FROM collaboration_invites WHERE id = invite_id;

    -- If the invite does not exist, set invite_status to -1
    IF invite_status IS NULL THEN
        SET invite_status = -1;
    END IF;


    -- Update invite status
    CASE invite_status
        WHEN 0 THEN
            UPDATE collaboration_invites SET accepted_at = CURRENT_TIMESTAMP WHERE id = invite_id;
        WHEN 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invite has already been accepted', MYSQL_ERRNO = 3401;
        WHEN 2 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invite has expired', MYSQL_ERRNO = 3402;
        WHEN -1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invite does not exist', MYSQL_ERRNO = 3403;
    END CASE;
END;