In-Process, In-Memory & File-Based Relational Data Processing with SQLite, BetterSQLite3
Table of Contents generated with DocToc
DBay is built on better-sqlite3, which is a NodeJS adapter
for SQLite. It provides convenient access to in-process, on-file and in-memory
relational databases.
DBay is the successor to and a re-write of ICQL-DBA. It is under development and nearing feature-parity with its predecessor while already providing some significant improvements in terms of ease of use and simplicity of implementation.
DBay provides
better-sqlite3,In order to construct (instantiate) a DBay object, you can call the constructor without any arguments:
{ DBay } = require 'dbay'
db = new DBay()
The db object will then have two properties db.sqlt1 and db.sqlt2 that are better-sqlite3
connections to the same temporary DB in the 'automatic location'.
The so-called 'automatic location' is either
/dev/shm on Linux systems that support SHared Memory (a.k.a a RAM disk)os.tmpdir()In either case, a file with a random name will be created in that location.
Format dbay-NNNNNNNNNN.sqlite, where N is a digit [0-9].
You can also call the constructor with a configuration object that may have one or more of the following fields:
cfg.path (?non-empty text): Specifies which file system path to save the DB to; if the path given
is relative, it will be resolved in reference to the current directory (process.cwd()). When not
specified, cfg.path will be derived from DBay.C.autolocation and a randomly
chosen filename.
cfg.temporary (?boolean): Specifies whether DB file is to be removed when process exits or
db.destry() is called explicitly. cfg.temporary defaults to false if cfg.path is given, and true
otherwise (when a random filename is chosen).
db.open cfg: Attach a new or existing DB to the db's
connections (db.sqlt1, db.sqlt1).
cfg:
schema (non-empty string): Required property that specifies the name under which the newly attached
DB's objects can be accessed as; having attached a DB as, say, db.open { schema: 'foo', path: 'path/to/my.db', }, one can then run queries like db "select * from foo.main;" against it. Observe
that
db = new DBay()) always has the implicit name main, and
schema temp is reserved for temporary databases.path (string): FS path to existing or to-be-created DB file; for compatibility, this may also be set
to one of the special values that indicates a in-memory
DB, although that is not recommended.temporary (boolean): Defaults to false when a path is given, and to true otherwise.The custom SQLite library that is compiled when installing DBay has its SQLITE_LIMIT_ATTACHED
compilation parameter set to the maximum allowed value of 125 (instead of the default 10). This allows
developers to assemble a DB application from dozens of smaller pieces when desired.
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
One thing that sets DBay apart from other database adapters is the fact that the object returned from new DBay() is both the representative of the database opened and a callable function. This makes executing
statements and running queries very concise. This is an excerpt from the DBay test suite:
{ DBay } = require H.dbay_path
db = new DBay()
db ->
db SQL"drop table if exists texts;"
db SQL"create table texts ( nr integer not null primary key, text text );"
db SQL"insert into texts values ( 3, 'third' );"
db SQL"insert into texts values ( 1, 'first' );"
db SQL"insert into texts values ( ?, ? );", [ 2, 'second', ]
#.......................................................................................................
T?.throws /cannot start a transaction within a transaction/, ->
db ->
#.........................................................................................................
T?.throws /UNIQUE constraint failed: texts\.nr/, ->
db ->
db SQL"insert into texts values ( 3, 'third' );"
#.........................................................................................................
rows = db SQL"select * from texts order by nr;"
rows = [ rows..., ]
T?.eq rows, [ { nr: 1, text: 'first' }, { nr: 2, text: 'second' }, { nr: 3, text: 'third' } ]
Note In the above
SQLhas been set toString.rawand has no further effect on the string it precedes; it is just used as a syntax marker (cool because then you can have nested syntax hiliting).
As shown by benchmarks, a crucial factor for getting maximum performance out of
using SQLite is strategically placed transactions. SQLite will not ever execute a DB query outside of a
transaction; when no transaction has been explicitly opened with begin transaction, the DB engine will
precede each query implicitly with (the equivalent of) begin transaction and follow it with either
commit or rollback. This means when a thousand insert statements are run, a thousand transactions will
be started and committed, leavin performance pretty much in the dust.
To avoid that performance hit, users are advised to always start and commit transactions when doing many
consecutive queries. DBay's callable db object makes that easy: just write db -> many; inserts; here;
(JS: db( () -> { many; inserts; here; })), i.e. pass a function as the sole argument to db, and DBay
will wrap that function with a transaction. In case an error should occur, DBay guarantees to call
rollback (in a try ... finally ... clause). Those who like to make things more explicit can also use
db.with_transaction ->. Both formats allow to pass in a configuration object with an attribute mode that
may be set to one of 'deferred', 'immediate', or
'exclusive', the default being 'deferred'.
Another slight performance hit may be caused by the logic DBay uses to (look up an SQL text in a cache or)
prepare a statement and then decide whether to call better-sqlite3's' Database::execute(),
Statement::run() or Statement::iterate(); in order to circumvent that extra work, users may choose to
fall back on to better-sqlite3 explicitly:
insert = db.prepare SQL"insert into texts values ( ?, ? );" # returns a `better-sqlite3` `Statement` instance
db ->
insert.run [ 2, 'second', ]
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
DBay::prepare(),
DBay::query() and so, except when
values list, as in select employee from employees where department in ( 'sales', 'HR' );.db.sql.I: ( name ): ->: returns a properly quoted and escaped SQL Identifier.db.sql.L: ( x ): ->: returns a properly quoted and escaped SQL Value. Note that booleans
(true, false) will be converted to 1 and 0, respectively.db.sql.V: ( x ): ->: returns a bracketed SQL list of values (using db.sql.V() for each list
element).db.interpolate( sql, values ): -> accepts a template (a string with placeholder formulas) and a list
or object of values. It returns a string with the placeholder formulas replaced with the escaped values.
# using named placeholders
sql = SQL"select $:col_a, $:col_b where $:col_b in $V:choices"
d = { col_a: 'foo', col_b: 'bar', choices: [ 1, 2, 3, ], }
result = db.sql.interpolate sql, d
# > """select "foo", "bar" where "bar" in ( 1, 2, 3 )"""
# using positional placeholders
sql = SQL"select ?:, ?: where ?: in ?V:"
d = [ 'foo', 'bar', 'bar', [ 1, 2, 3, ], ]
result = db.sql.interpolate sql, d
# > """select "foo", "bar" where "bar" in ( 1, 2, 3 )"""
# using an unknown format
sql = SQL"select ?:, ?X: where ?: in ?V:"
d = [ 'foo', 'bar', 'bar', [ 1, 2, 3, ], ]
result = db.sql.interpolate sql, d
# throws "unknown interpolation format 'X'"
DBay offers limited support for the declarative generation of a small number of recurring classes of SQL statements. These facilities are in no way intended to constitute or grow into a full-blown Object-Relational Mapper (ORM); instead, they are meant to make working with relational data less of a repetitive chore.
To pick one case in point, SQL insert statements when called from a procedural language have a nasty habit
of demanding not two, but three copies of a table's column names:
db SQL"""
create table xy (
a integer not null primary key,
b text not null,
c boolean not null );"""
db SQL"insert into xy ( b, c ) values ( $b, $c )", { b, c, }
# ^^^^^^^^ ^^^^^^^^^^ ^^^^^^^^^
insert statements, one would still have to implement
almost all of SQL, as is evidenced by the screenshot of the SQLite insert Statement Railroad Diagram that will
be displayed when clicking/tapping on this paragraph.
Instead, we implement facilities to cover the most frequent use cases and offer opportunities to insert SQL fragments at strategic points.
Often, when an insert statement is being called for, one wants to insert full rows (minus generated
columns, for which see below) into tables. This is the default that DBay makes easy: A call to
db.prepare_insert() with the insertion target identified with into will return a prepared statement that
can then be used as first argument to the db callable:
insert_into_xy = db.prepare_insert { into: 'xy', }
db insert_into_xy, { a, b, c, }
Observe that named parameters (as opposed to positional ones) are used, so values must be passed as an object (as opposed to a list).
In case the actual SQL text of the statement is needed, call db.create_insert() instead:
insert_sql = db.create_insert { into: 'xy', }
# 'insert into "main"."xy" ( "a", "b", "c" ) values ( $a, $b, $c );'
When one or more columns in a table are autoincremented or have a
default value, then those columns are often intended not to be set explicitly. What's more, columns with
generated values must not be set explicitly. For this reason, db.create_insert() (and, by
extension, db.prepare_insert()) will skip generated columns and allow to explicitly specify either
included columns (as fields) or else excluded columns (as exclude):
db SQL"""
create table t1(
a integer primary key,
b integer,
c text,
d integer generated always as (a*abs(b)) virtual,
e text generated always as (substr(c,b,b+1)) stored );"""
insert_into_t1 = db.create_insert { into: 't1', }
### Observe `d` and `e` are left out because they're generated, but `a` is present: ###
# 'insert into "main"."t1" ( "a", "b", "c" ) values ( $a, $b, $c );'
### You probably want either this: ###
insert_into_t1 = db.create_insert { into: 't1', fields: [ 'b', 'c', ], }
# 'insert into "main"."t1" ( "b", "c" ) values ( $b, $c );'
### Or this: ###
insert_into_t1 = db.create_insert { into: 't1', exclude: [ 'a', ], }
# 'insert into "main"."t1" ( "b", "c" ) values ( $b, $c );'
There's a subtle yet important semantic difference in how the
fieldsandexcludesettings are handled: Whenfieldsare explicitly given, the table does not have to exist when generating the SQL; however, whenfieldsis not given, the table must already exist at the time of callingcreate_insert().In either case,
prepare_insert()can only succeed when all referenced object in an SQL statement have already been created.
The next important thing one often wants in inserts is resolving conflicts. DBay create_insert() supports
setting on_conflict to either (1) an arbitrary string that should spell out a syntactically valid SQL
on conflict clause, or (2) an object { update: true, } to generate SQL that updates the explicitly
or implicitly selected columns. This form has been chosen to leave the door open to future expansions of
supported features.
When choosing the first option, observe that whatever string is passed in, create_insert() will prepend
'on conflict ' to it; therefore, to create an insert statement that ignores insert conflicts, and
according to the upsert syntax railroad diagram: —
— the right thing to do is to call db.create_insert { into: table_name, on_conflict: 'do nothing', }.
Assuming table t1 has been declared as above, calling
db.create_insert { into: 't1', exclude: [ 'a', ], on_conflict: "do nothing", }
will generate the (unformatted but properly escaped) equivalent to:
insert into main.t1 ( b, c )
values ( $b, $c )
on conflict do nothing;
-- |<------>|
-- inserted string
while calling
db.create_insert { into: 't1', exclude: [ 'a', ], on_conflict: { update: true, }, }
wiil generate the (unformatted but properly escaped) equivalent to:
insert into main.t1 ( b, c )
values ( $b, $c )
on conflict do update set --| conflict resolution clause
b = excluded.b, --| mandated by { update: true, }
c = excluded.c; --| containing same fields as above
returning ClauseIt is sometimes handy to have insert statements that return a useful value. Here's a toy example
that demonstrates how one can have a table with generated columns:
db SQL"""
create table xy (
a integer not null primary key,
b text not null,
c text generated always as ( '+' || b || '+' ) );"""
insert_into_xy_sql = db.create_insert { into: 'xy', on_conflict: SQL"do nothing", returning: '*', }
# -> 'insert into "main"."xy" ( "a", "b" ) values ( $a, $b ) on conflict do nothing returning *;'
db.single_row insert_into_xy_sql, { a: 1, b: 'any', } # -> { a: 1, b: 'any', c: '+any+' }
db.single_row insert_into_xy_sql, { a: 2, b: 'duh', } # -> { a: 2, b: 'duh', c: '+duh+' }
db.single_row insert_into_xy_sql, { a: 3, b: 'foo', } # -> { a: 3, b: 'foo', c: '+foo+' }
Generally, the returning clause must be defined by a non-empty string that is valid SQL for the position
after returning and the end of the statement. A star * will return the entire row that has been
inserted; we here use db.single_row() to eschew the result iterator that would be returned by default.
▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊ ▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊▌▊
better-sqlite3 an 'Unsaved' DependencySince DBay depends on better-sqlite3 with a
custom-configured build of the SQLite C
engine, it is (for whatever
reason) important that better-sqlite3 must not be listed under package.json#dependencies; otherwise,
compilation will not work properly. The build script will run npm install better-sqlite3@'^7.4.3' but with an added --no-save flag.
Also, at the time of this writing (2021-09), while the project compiles fine using npm v7.21.1 (on NodeJS
v16.9.1 on Linux Mint), but it fails using pnpm v6.14.6 with Unknown options: 'build-from-source', 'sqlite3'. Yarn has not been tried.
Note—These considerations only concern those who wish to fork/clone DBay to work on the code. Those who
just want to use DBay as a dependency of their project can both either run npm install dbay or pnpm add dbay, both package managers work fine.
dbnick when path is given and ram is falsedb.save(). See
https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/threads.mdselect * from foo( x ) as d; to get select * from ( select a, b, c from blah order by 1 ) as d (i.e. inline expansion)selects from inside UDFs, so maybe there's a teeny, fixable difference between how both are
implemented that leads to the undesirable behaviortmpfs-based in-memory DBs (b/c the latter allow
pragma journal_mode = WAL for better concurrent access). Cons include: tmpfs-based RAM DBs necessitate
mounting a RAM disk which needs sudo rights, so might as well just instruct users to mount RAM disk,
then use that path? Still, it would be preferrable to have some automatic copy-to-durable in place.DBay::do() as a method that unifies all of better-sqlite3's Statement::run(),
Statement::iterate(), and Database::execute().DBay::do -> ... with a synchronous function with the same semantics as
DBay::with_transaction -> ....DBay::do { mode: 'deferred', }, -> ....DBay::do -> ... with an asynchronous functiondb = new DBay() an instance of Function that, when called, runs DBay::do()
Database::execute().
statement = DBay::prepare.insert_into.<table> [ 'field1', 'field2', ..., ]Dbay to DBay to avoid spelling variant proliferationDBay::open(), DBay::close()random from a mixin to functions in helpers.db.truncate() / db.delete(); allow to retrieve SQL.DBay::insert_into.<table> [ 'field1', 'field2', ..., ], { field1, field2, ..., };
allow to retrieve SQL.null b/c it looks like they
don't get called which would be unfortunatedb.do() accept prepared statement objects.create_insert()).db.commit()db.rollback()sql.V()first_row(), all_rows() etc accept statements and stringsFAQs
In-Process, In-Memory & File-Based Relational Data Processing with SQLite, BetterSQLite3
The npm package dbay receives a total of 74 weekly downloads. As such, dbay popularity was classified as not popular.
We found that dbay demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Socket's package search now displays weekly downloads for npm packages, helping developers quickly assess popularity and make more informed decisions.
Security News
A Stanford study reveals 9.5% of engineers contribute almost nothing, costing tech $90B annually, with remote work fueling the rise of "ghost engineers."
Research
Security News
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.