@radically-straightforward/sqlite
v1.1.7
Published
🗃️ SQLite with tagged templates and migrations
Downloads
583
Maintainers
Readme
Radically Straightforward · SQLite
🗃️ SQLite with tagged templates and migrations
Installation
$ npm install @radically-straightforward/sqlite
Note: We recommend the ES6 String HTML Visual Studio Code extension to syntax highlight SQL in tagged templates.
Note: We recommend DBeaver to interact with the database, including visualizing the current schema (including an automatically generated entity–relationship diagram), testing queries, and so forth.
Usage
import sql, { Database } from "@radically-straightforward/sqlite";
Database
export class Database extends BetterSQLite3Database;
An extension of better-sqlite3
’s Database
which includes:
A simpler way to run queries using tagged templates instead of managing prepared statements by hand.
A migration system.
Better defaults for running SQLite on the server, avoiding the
SQLITE_BUSY
error.Automatic resource management (close the database before process exit).
A background job mechanism.
A caching mechanism.
To appreciate the difference in ergonomics between better-sqlite3
and @radically-straightforward/sqlite
, consider the following example:
better-sqlite3
import Database from "better-sqlite3";
const database = new Database("example.db");
database.exec(
`
create table "users" (
"id" integer primary key autoincrement,
"name" text not null
) strict;
`,
);
const insertStatement = database.prepare(
`insert into "users" ("name") values (?);`,
);
insertStatement.run("Leandro Facchinetti");
const selectStatement = database.prepare(
`select "id", "name" from "users" where "name" = ?;`,
);
console.log(selectStatement.get("Leandro Facchinetti")); // => { id: 1, name: 'Leandro Facchinetti' }
database.close();
You must manage the prepared statements yourself, making sure to reuse them as much as possible. You could choose to not do that and create a new prepared statement every time instead, but that would be much slower.
The queries and their corresponding binding parameters are specified separately. In this simple example they’re just one line apart, but in general they could be far from each other, which makes the program more difficult to maintain.
When you run the program above for the second time, it fails because the
users
table already exists. In this simple example you could work around that by usingcreate table if not exists
, but for anything more complicated you need a migration system.You must remember to call
close()
or some temporary files may be left behind even after a graceful termination.
@radically-straightforward/sqlite
import sql, { Database } from "@radically-straightforward/sqlite";
const database = await new Database("example.db").migrate(
sql`
create table "users" (
"id" integer primary key autoincrement,
"name" text not null
) strict;
`,
);
database.run(
sql`
insert into "users" ("name") values (${"Leandro Facchinetti"});
`,
);
console.log(
database.get(
sql`
select "id", "name" from "users" where "name" = ${"Leandro Facchinetti"};
`,
),
); // => { id: 1, name: 'Leandro Facchinetti' }
@radically-straightforward/sqlite
manages the prepared statements for you, and makes sure to reuse them as much as possible.The queries and their corresponding binding parameters are specified together, using interpolation in the
sql`___`
tagged template.Note:
@radically-straightforward/sqlite
does not do simple string interpolation, which would lead to SQL injection vulnerabilities. Under the hood@radically-straightforward/sqlite
uses bind parameters similar to thebetter-sqlite3
example.Note: In Visual Studio Code you may install the ES6 String HTML extension to add syntax highlighting to
sql`___`
tagged templates.You may run the program above many times and it will not fail, because it’s using
@radically-straightforward/sqlite
’s migration system.If you don’t call
close()
explicitly, it’s called for you before process exit.
Database.migrate()
async migrate(
...migrations: (Query | ((database: this) => void | Promise<void>))[]
): Promise<this>;
A migration system based on the steps for general schema changes in SQLite. The migration system implements steps 1–2, 11–12, and you must implement steps 3–10 in the migrations that you define.
A migration may be:
A SQL query, for example:
sql` create table "users" ( "id" integer primary key autoincrement, "name" text not null ) strict; `;
A function, which may be asynchronous:
async () => { database.execute( sql` insert into "users" ("name") values (${"Leandro Facchinetti"}); `, ); };
Note: For convenience, a migration function may receive the database as a parameter. This can be useful if you want to define migrations in separate files.
Guidelines
As your application evolves, append migrations to the call to
migrate()
but don’t edit or remove existing migrations. Think of the call tomigrate()
as an immutable record of the history of your database schema.Run
migrate()
as your application starts, so that the database schema is always up-to-date.Don’t call
migrate()
multiple times in your application.The migration system guarantees that each migration will run successfully at most once. A migration is run in a database transaction, and if it fails (for example, if it throws an exception), then the transaction is rolled back.
Note: A migration that fails in the middle may still have had side-effects up to the point of failure (for example, having had written a file to the filesystem), and that could cause issues. Make migrations as free of side-effects as possible.
The migration system doesn’t include a way to roll back a migration that has already run successfully. Instead, when necessary, you must create a new migration that undoes the work of the problematic migration.
Why? This makes managing migrations more straightforward, and in any non-trivial case rollback is impossible anyway (for example, if a migration involves dropping a table, then rolling it back would involve bringing back data that has been deleted).
You may consult the status of your database schema with the
pragma user_version
, which holds the number of migrations that have been run successfully.The migration system sets several
pragma
s that make SQLite better suited for running on the server, avoiding theSQLITE_BUSY
error. See https://kerkour.com/sqlite-for-servers.
Implementation Notes
migrate()
must be its own separate method instead of being part of the constructor because migrations may be asynchronous.We manage transactions by hand with
begin immediate
instead of usingexecuteTransaction()
because migrations are the one exception in which it makes sense to have an asynchronous function in the middle of a transaction, given that migrations don’t run in parallel.
Database.execute()
execute(query: Query): this;
Execute DDL statements, for example, create table
, drop index
, and so forth. Multiple statements may be included in the same query.
Database.run()
run(query: Query): BetterSQLite3Database.RunResult;
Run a DML statement, for example, insert
, update
, delete
, and so forth.
Database.get()
get<Type>(query: Query): Type | undefined;
Run a select
statement that returns a single result.
Note: If the
select
statement returns multiple results, only the first result is returned, so it’s better to write statements that return a single result (for example, usinglimit
).
Note: You may also use
get()
to run aninsert ___ returning ___
statement, but you probably shouldn’t usereturning
, because it runs into issues in edge cases. Instead, you should userun()
, get thelastInsertRowid
, and perform a follow-upselect
. See https://github.com/WiseLibs/better-sqlite3/issues/654 and https://github.com/WiseLibs/better-sqlite3/issues/657.
Note: The
Type
parameter is an assertion. If you’d like to make sure that the values returned from the database are of a certain type, you must implement a runtime check instead. See https://github.com/DefinitelyTyped/DefinitelyTyped/issues/50794, https://github.com/DefinitelyTyped/DefinitelyTyped/discussions/62205, and https://github.com/DefinitelyTyped/DefinitelyTyped/pull/65035. Note that theget() as ___
pattern also works because by defaultType
isunknown
.
Database.all()
all<Type>(query: Query): Type[];
Run a select
statement that returns multiple results as an Array.
Note: We recommend including an explicit
order by
clause to specify the order of the results.
Note: If the results are big and you don’t want to load them all at once, then use
iterate()
instead.
Database.iterate()
iterate<Type>(query: Query): IterableIterator<Type>;
Run a select
statement that returns multiple results as an iterator.
Note: If the results are small and you may load them all at once, then use
all()
instead.
Database.pragma()
pragma<Type>(
source: string,
options?: BetterSQLite3Database.PragmaOptions,
): Type;
Run a pragma
. Similar to better-sqlite3
’s pragma()
, but includes the Type
assertion similar to other methods.
Database.executeTransaction()
executeTransaction<Type>(fn: () => Type): Type;
Execute a function in a transaction. All the caveats about better-sqlite3
’s transactions still apply. Transactions are immediate
to avoid SQLITE_BUSY
errors. See https://kerkour.com/sqlite-for-servers.
Database.backgroundJob()
backgroundJob<Type>(
{
type,
timeout = 5 * 60 * 1000,
retryIn = 5 * 60 * 1000,
retries = 10,
}: {
type: string;
timeout?: number;
retryIn?: number;
retries?: number;
},
job: (parameters: Type) => void | Promise<void>,
): ReturnType<typeof node.backgroundJob>;
A background job system that builds upon @radically-straightforward/node
’s backgroundJob()
to provide the following features:
Persist background jobs in the database so that they are preserved to run later even if the process crashes.
Allow jobs to be worked on by multiple Node.js processes.
Impose a timeout on jobs.
Retry jobs that failed.
Schedule jobs to run in the future.
Log the progress of a job throughout the system.
Note: You may use the same database for application data and background jobs, which is simpler to manage, or separate databases for application data for background jobs, which may be faster because background jobs write to the database often and SQLite locks the database on writes.
You may schedule a background job by insert
ing it into the _backgroundJobs
table that’s created by migrate()
, for example:
database.run(
sql`
insert into "_backgroundJobs" (
"type",
"startAt",
"parameters"
)
values (
${"email"},
${new Date(Date.now() + 5 * 60 * 1000).toISOString()},
${JSON.stringify({
from: "[email protected]",
to: "[email protected]",
text: "This was sent from a background job.",
})}
);
`,
);
Note: A job that times out may actually end up running to completion, despite being marked for retrying in the future. This is a consequence of using
@radically-straightforward/utilities
’stimeout()
.
Note: A job may be found in the database with a starting date that is too old. This may happen because a process crashed while working on the job without the opportunity to clean things up. This job is logged as
EXTERNAL TIMEOUT
and scheduled for retry.
References
- https://github.com/collectiveidea/delayed_job
- https://github.com/betterment/delayed
- https://github.com/bensheldon/good_job
- https://github.com/litements/litequeue
- https://github.com/diamondio/better-queue-sqlite
Database.cache()
async cache(
key: string,
valueGenerator: () => string | Promise<string>,
): Promise<string>;
A simple cache mechanism backed by the SQLite database.
If the key
is not found, then the valueGenerator()
is called and its result is stored. If the key
is found, then the stored value
is returned and valueGenerator()
is not called.
The cache holds at most this.cacheSize
items (by default 10_000
). As new items are added, the least recently used (LRU) items are deleted.
The key
must contain all the information that identifies the value
, for example, `messages/${message.id}/updatedAt/${message.updatedAt}`
. As the message
is updated, old cache entries aren’t expired explicitly, but fall out of the cache as new items are added.
This cache is appropriate for storing server-side HTML that’s expensive to compute, memoized values in dynamic programming, and so forth.
The advantages of using SQLite instead of something like a Map
in the JavaScript process itself are that the cache persists across application restarts, and that the cache may be shared across multiple processes of the same application.
The advantage of using SQLite instead of something like Redis or Memcached is that it’s less infrastructure to maintain.
You may want to have the cache in the same database as the application, because it’s simpler. Or you may prefer to have the cache in a dedicated database, because the cache involves a lot of writes, which could slow down other parts of the application.
References
- https://guides.rubyonrails.org/caching_with_rails.html#low-level-caching
- https://signalvnoise.com/posts/3113-how-key-based-cache-expiration-works
Implementation Notes
- We don’t use a transaction between consulting the cache and updating the cache so that things are as fast as possible: a transaction would lock writes to the database for longer—not to mention that
valueGenerator()
may be asynchronous, and it runs between these two steps. As a consequence, in case of a race condition, thekey
may appear multiple times in the cache. But that isn’t an issue, because thekey
isn’tunique
in the schema, so no uniqueness constraint violation happens, and if the cache is being used correctly andvalueGenerator()
returns the same value every time, then bothkey
s will have the samevalue
, and one of them will not be used and naturally fall out of the cache at some point.
Database.getStatement()
getStatement(query: Query): BetterSQLite3Database.Statement;
An internal method that returns a better-sqlite3
prepared statement for a given query. Normally you don’t have to use this, but it’s available for advanced use-cases in which you’d like to manipulate a prepared statement (for example, to set safeIntegers()
).
Query
export type Query = {
sourceParts: string[];
parameters: any[];
};
An auxiliary type that represents a database query. This is what’s generated by the sql`___`
tagged template.
sql()
export default function sql(
templateStrings: TemplateStringsArray,
...substitutions: (
| number
| string
| bigint
| Buffer
| null
| undefined
| Array<number | string | bigint | Buffer | null | undefined>
| Set<number | string | bigint | Buffer | null | undefined>
| Query
)[]
): Query;
A tagged template to generate a database query.
Interpolation is turned into binding parameters to protect from SQL injection, for example:
sql`insert into "users" ("name") values (${"Leandro Facchinetti"});`;
Arrays and Sets may be interpolated for in
clauses, for example:
sql`select "id", "name" from "users" where "name" in ${[
"Leandro Facchinetti",
"David Adler",
]};`;
You may use the pattern $${___}
(note the two $
) to interpolate a clause within a query, for example:
sql`select "id", "name" from "users" where "name" = ${"Leandro Facchinetti"}$${sql` and "age" = ${33}`};`;
Note: This is useful, for example, to build queries for advanced search forms by conditionally including clauses for fields that have been filled in.