sqlite-wrapper

Helper wrapper around SQLite

Features

Database creation and schema management:

• Autocreates and initializes the database if the .sqlite file doesn't exist yet.
• Understands how to do database migrations when the schema changes.
• Automatically does non-destructive migrations.
• Has functions (not automatically called) to do destructive migrations and table rebuilds.

Database usage:

• Has convenient wrappers for some typical SQL operations.

Implementation

Uses the better-sqlite3 library as the underlying layer to access SQL. This means that all database access operations are syncronous instead of async (check their project to read more about the motivations for this).

Usage

Create a new database:

let _db = new DatabaseLoader({
    filename: './something.sqlite',
    schema: {
        name: 'SomethingDatabase',
        statements: [
            `create table some_table(
                ...
            )`,
        ]
    }
});

export function getDatabase(): SqliteDatabase {
    return _db.load();
}

Use the database:

    const db = getDatabase();
    const user = db.get(`select * from user_table where user_id = ?`, [user_id]);

API

DatabaseLoader

Class that defines the filename and schema.

Will load the SQLite database as soon as you call DatabaseLoader.get()

When the table is first loaded, the library will automatically do non-destructive migration to the latest schema.

DatabaseLoader.get()

Returns a SqliteDatabase instead.

SqliteDatabase

Wrapper object over a better-sqlite3 instance.

SqliteDatabase.get(sql, params)

Calls .get() on better-sqlite3.

This runs the select command and returns the first row that matches, or null.

SqliteDatabase.list(sql, params)

Calls .all() on better-sqlite3.

This runs the select command and returns all rows that match as an array.

SqliteDatabase.each(sql, params)

Calls .iterate() on better-sqlite3.

This runs the select command and returns an iterator for all matching rows.

SqliteDatabase.exists(sql, params)

Convenience function that runs SQL: select exists(select 1 ${sql}).

Returns a boolean if any rows matched the condition.

The sql string should look like from <table> where ...

SqliteDatabase.count(sql, params)

Convenience function that runs SQL: select count(*) ${sql} and returns the count(*) result.

The sql string should look like from <table> where ...

SqliteDatabase.insert(tableName, row: Record<string,any>)

Convenience function that builds an insert statement.

The SQL statement will look like insert into <tableName> (...) values (...)

The columns & values will be built using the row object, which is plain object where each field is a column name.

SqliteDatabase.update(tableName, where, whereValues, row)

Convenience function that builds an update statement.

Parameters:

• tableName (string): The name of the table to update
• where (string): The WHERE clause condition (e.g., 'id = ?' or 'name = ? AND active = ?')
• whereValues (array): Array of values to substitute for the ? placeholders in the WHERE clause
• row (object): Object containing the columns to update, where each key is a column name and value is the new value

Example:

// Update a single record by ID
db.update('users', 'id = ?', [123], { 
  name: 'John Doe', 
  email: '[email protected]' 
});

// Update multiple records with complex conditions
db.update('products', 'category = ? AND price < ?', ['electronics', 100], {
  discount: 0.1,
  updated_at: Date.now()
});

The generated SQL will look like: UPDATE <tableName> SET column1 = ?, column2 = ? WHERE <where condition>

SqliteDatabase.upsert(tableName, where: Record<string,any>, row: Record<string, any>)

Convenience function that either does an update or insert.

First the function will try to run an update (with the same handling as SqliteDatabase.update).

If zero rows were changed by the update, then upsert() will next run an insert (with the same handling as SqliteDatabase.insert);