In-Process, In-Memory & File-Based Relational Data Processing with SQLite, BetterSQLite3
Table of Contents generated with DocToc
better-sqlite3 (B3 in the below)
to provide the interface between NodeJS (JavaScript) and SQLite.open() call.
disk: true|false configuration) although IMO there's little reason to
not use optional HD support.'' to get disk support (in ase of RAM shortage) or the pseudo-path ':memory:' to get one
without disk support. In ICQL-DBA, you use the boolean settings ram and disk instead which is much
clearer. This frees the path argument from doing double duty, so one can use it to specify a default
file path to be used implicitly for the save() command.the transfer() API method may be used
transfer { schema, ram: true, [ path: 'path/to/file.db' ], } switches from file-based Continuous
Persistency to memory-based Eventual Persistency. This is a no-op in case the DB is already memory-based.
Likewise, transfer { schema, ram: false, [ path: 'path/to/file.db' ], } switches
path
setting is optional. If path is given, the DB will now be associated with that (possibly new) on-disk
locationopen()ed (from a file) with setting ram: false, every change to its structure or its
business data will be immediately reflected to disk; this, of course, is the regular mode of operation
for SQLite and most all RDBMSes and is, hence, known as Continuous Persistency.
create: false has been
specified.save() command has completed
and before any new changes have been executed.vacuum $schema into $path (no
schema-copying is involved in this step). The API method to do so is save(), a method that does nothing
in case a given schema is disk-based (and therefore writes all changes to disk, continuously); therefore,
one can make it so that the same code with strategically placed save() statements works for both
RAM-based and disk-based DBs without any further changes.
export() API. The export() method will allow
to write, for example, an SQL dump or the SQLite binary format to a given file.open() method doesexport() (Ad Hoc Persistency) is import() (Ad Hoc Acquisition).sql) may be valid when empty and result in an empty DB.| Acquisition | Persistency | |
|---|---|---|
| Regular and Eventual | open() | save()¹ |
| Ad Hoc | import() | export() |
¹ save() calls are optional no-ops for Continuous Persistency
main and temp.better-sqlite3
object,
that data will be put into the main schema.main schema in SQLite/B3; to do so, call either new Database ':memory:' which will create an empty main schema; then, you can execute an SQL attach
statement like attach $path as $schema; to open a file- or
RAM-based DB under another schema of your choosing.open() in ICQL-DBA.create temp table t; or create temporary table t;, then t
will be available as temp.t (but not temporary.t).DBA = require 'icl-dba'
dba = new DBA.Dba()
dba is constructed with an empty path argument to B3, i.e. require('better-sqlite3')('').main schema.dba is not yet connected to any file-based DB; the only meaningful next operation is open().dba and open an existing DB in a single step.open()open() uses the SQLite SQL attach extension. In many SQLite3 installations, the maximum number of
attachable DBs (schemas) is limited to 10; however, the SQLite adapter that is shipped with ICQL-DBA allows
to open up to 125 schemas. This upgraded number is intended to allow for a DB design where a data
application is composed out of a fair number of schemas that each reside in a separate file.
path must be a string that is a valid file system path (up to the parent directory); its final component
must either point to an existing SQLite DB file or be non-existant. (Write permissions are required in
case modifications are intended.)path does not exist, a new SQLite DB file will be created. To prevent
autocreation, specify create: false, which will cause an error to be thrown.dba.open { path: 'path/to/my.db', schema: 'myschema', }
open()ing an SQLite file with ram: true.
This will copy the DB's structure and its data to RAM.ram defaults to false; when no path is given (see
below), ram defaults to true.disk: false to keep SQLite from using temporary files to be used in case of RAM shortage (but why
should you).save() is called later.save() with path when DB was opened with path.dba.open { path: 'path/to/my.db', schema: 'myschema', ram: true, }
open() a RAM DB that has no inherent link to a file, omit the path setting (or set it to null).import().path is missing, ram defaults to true, so in this case it may be omitted or set to
null.dba.open { schema: 'myschema', }
dba.import() is asynchronous. This is mostly due to
the relative scarcity of synchronous parsing (and, generally, file-handling) packages for the NodeJS
ecosystem.sqlite for the SQLite binary file format andsql for SQL dumps.open(), import() accepts SQL dumps (and, in the future, possibly other formats).format: 'sqlite' or format: 'sql' to explicitly specify the intended file format in case the
file extension does not match:
.dump, .sql are recognized as format: 'sql'.db, .sqlite are recognized as format: 'sqlite'import() may accept additional arguments:
save_as to specify a path for Continuous or Eventual Persistencyram will default to false when save_as is not given and to true otherwiseoverwrite to specify whether an existing file at the position indicated by save_as should be
overwritten.dba = new Dba()
schema = 'myschema'
dba.open { schema, }
await dba.import { path: 'path/to/some.db', schema, }
import { format: 'sql', }attach 'path/to/my.db' as myschema, so an SQL script may define structures and data in multiple schemas that may also reference
each other.dba.execute() anyhow so no change is required), but also demand that valid SQL scripts either
main, explicitly or implicitly, ormain is intended, an explicit configuration setting like from_schema must
be included, and only that schema will be imported.imüport()ing SQL scripts/dumps will include:
dba (or B3) instance,vacuuming of the temporary DB to an SQLite binary DB file, andopen()ing that file from the original DBA instance.transfer_to_ram() allows to convert a file DB to a RAM DB.schema is a required setting that is the name of the file-based DB which will become the name of
the RAM DB.save() at appropriate points in time or else call
transfer_to_file() once RAM-based operation should be terminated and results saved.dba.transfer_to_ram { schema: 'myschema', }
transfer_to_file() allows to convert a RAM DB to a file DB.### TAINT which configuration?), then (2) call save_as() with the original pathpath setting then the schema's associated path will be used.transfer_to_file() is a no-op.export().dba.transfer_to_file { schema: 'myschema', path, }
save() to Save to Linked Filesave() (but no harm done, either).save()d manually in order to persist changes in structure or data.save() throws an error if path setting is given.save() throws an error if schema setting isn't given or schema is unknown.transfer_to_file { path, } (and save() after subsequent changes) to add or change the file path
linked to a RAM DB.export { path, overwrite: true, format: 'sqlite', } to repeatedly save a RAM DB as an
SQLite binary file DB.dba.save { schema: 'myschema', }
dba.export() with a few additional parameters:schema = 'myschema'
path = dba._schemas[ schema ].path
dba.export { schema, path, format: 'sqlite', overwrite: true, }
save() and export() is rather intentional (conceptual) than extensional (material):
save { schema, } to 'persist the state of a schema to its associated DB file', whereasexport { schema, path, } to 'make a durable copy of this RAM DB'.transfer_to_file().dba.export { schema: 'myschema', path, format, overwrite, }
transform: optional function, default: null_extra: optional object, default: null. This value will be passed to
csv-parser which does the hard part of parsing CSV so you
can use await dba.import { ..., format: 'csv', _extra: { ... }, ...} to directly talk to csv-parser.
Notice however that some settings may be overridden without notice by dba.import(). For a description
of options see csv-parser.null values, either
when all fields are null or when any field is null. Observe that this is only tested against the
columns that were selected with input_columns (where set explicitly):
skip_any_null optional boolean, default: falseskip_all_null optional boolean, default: falsetrim optional boolean, default: true. Whether to remove leading and trailing whitespace from
field values.default_value optional; van be any value, default: null. This value will be applied to all fields
that are found to be (the) empty (string) (after optional trimming). Observe that quoting a field value
will not prevent trimming.quote optionalinput_columns:
boolean or nonempty list of nonempty texts, default: nulltrue: first non-skipped row of source contains column names; rows are objectsfalse: rows are listsn names: only the first n columns will be kept; rows are objectstable_name: optional nonempty_text, defaults to maintable_columns:
null: columns are created as texts depending on the first row encountered; if it is a list,
columns will be named c1, c2, c3, ..., c${n}{ name: type, name: type, ..., }: columns are created with the names and types given[ name, name, ..., ]: all columns are created as textUser-Defined Functions (UDFs) is one feature that sets SQLite apart from other RDBMSes because unlike other databases, SQLite allows users to define functions in user code, on the connection. Therefore, NodeJS users can define and use UDFs that are written in JavaScript or WASM and that can access the current machine's environment (e.g. the file system). On the one hand, this is probably somewhat slower than e.g. using a compiled PostgreSQL extension written in C, but on the other hand, userspace functions are orders of magnitude easier to write than a Posgres C extension; also, such functions can take advantage of the existing NodeJS ecosystem which is a huge plus and any speed penalty incurred by using JavaScript for 'hot' UDFs might be offset by an re-implementation in, say, Rust.
One downside—or, shall we say, "characteristic aspect"—of defining UDFs on the client side (as opposed to writing them embedded in SQL) is that your DB or at least some aspects of it may become unusable without suitable initialization of the connection. It is to be expected, though, that in a complex application some parts are not bound to function properly without other parts being in place—the application code as such won't work when the database of a DB-based app is missing, and the DB may not fully work without the application code. (This, by the way, is exactly true for Fossil SCM-based repositories, which might be regarded as the poster child of an application built around an SQLite database.)
dba.create_function: ( cfg ) -> single-valued functionsdba.create_aggregate_function: ( cfg ) -> aggregate functionsdba.create_window_function: ( cfg ) -> window functionsdba.create_table_function: ( cfg ) -> table-valued functionsdba.create_virtual_table: ( cfg ) -> virtual tablesContext managers are well known from Python (e.g. with open( path ) as myfile: ...) where they are used to ensure that a given piece of code is always run with
certain pre- and post-conditions fulfilled. Typically the implementation of a context manager will use a
try / catch / finally clause to ensure some kind of cleanup action will be performed even in the
presence of exceptions.
While JavaScript does not have syntactic support for context managers, it's straightforward to implement
them as plain functions. Context managers in ICQL-DBA include dba.with_transaction(),
dba.with_unsafe_mode(), and dba.with_foreign_keys_deferred(). All three require as their last or only
argument a (named or anonymous) function which will be executed with the implemented pre- and
post-conditions.
Asynchronous functions are currently not allowed in of context handlers, though a future version may add support for them.
dba.with_transaction: ( cfg, f ) ->dba.with_transaction: ( f ) ->Given a function f, issue SQL begin transaction;, call the function, and, when it finishes successfully,
issue commit; to make data changes permanent. Should either the function call or the commit throw an
error, issue SQL rollback to undo changes (to the extent SQLite3 undoes DB changes). In contradistinction
to better-sqlite3's transaction() method, do not allow nested calls to dba.with_transaction(); an
attempt to do so will cause a Dba_no_nested_transactions error to be thrown.
Optionally, dba.with_transaction() may be called with an additional cfg object whose sole member mode
can be set to one of 'deferred' (the default), 'immediate', or 'exclusive' to set the behavior of the
transaction.
dba.with_unsafe_mode: ( f ) ->Given a function f, take note of the current status of unsafe mode, switch unsafe mode on, call the f(),
and, finally, set unsafe mode to its previous value.
Used judiciously, this allows e.g. to update rows in a table while iterating over a result set. To ensure
proper functioning with predictable results and avoiding endless loops (caused by new rows being added to
the result set), one could e.g. use a dedicated field (say, is_new) in the affected table that is false
for all pre-existing rows and true for all newly inserted ones.
dba.with_foreign_keys_deferred: ( f ) ->Given a function f, start a transaction, issue SQL pragma defer_foreign_keys=true, and call f(). While
f() is executing, rows may now be inserted, modified or deleted without foreign keys constraints being
checked. When f() has terminated successfully, commit the transaction, thereby implicitly switching
foreign keys deferral off and checking for foreign key integrity. Since dba.with_foreign_keys_deferred()
implicitly runs in a transaction, it can itself neither be called inside a transaction, nor can a
transaction be started by f(). Should f() throw an error, SQL rollback will be issued as described for
dba.with_transaction()
Right after a connection to an SQLite DB has been instantiated, an initialization method @initialize_sqlt
is called with the better-sqlite3 DB object as sole argument is called. By overriding this method in a
derived class, one can configure the connection e.g. by calling better-sqlite3's pragma() method. When
doing so, observe that the call happens before the dba.sqlt attribute is set, so avoid to access
the dba (this/@) instance. You're on the safe side if you restrict yourself to accessing the first
argument to initialize_sqlt(). The default implementation of the method looks like this:
initialize_sqlt: ( sqlt ) ->
sqlt.pragma "foreign_keys = true;"
return null
In your own implementation,
super sqlt to get the default configuration for the connection.@pragma() in the initializer as this will lead to infinite regress
b/c of the way the dynamic attribute @sqlt has been implemented.dba = new Dba()
{ SQL, I, L, X, } = dba.sql
table = 'mytable'
value = 'something'
sql = SQL"select * from #{I table} where x == #{L value};"
# == select * from "mytable" where x == 'something';
SQL: currently a no-op, but can be used (e.g. with
coffeeplus) to signal text highlighting the language
used in the string literal.I: format a text as an SQL identifier (using double quotes)L: format a value as an SQL literalX: format a flat list as an SQL row value (a.k.a. a vector)For the concept of using in-memory SQLite DBs (not specifically ICQL-DBA, which probably nobody uses):
We use SQLite in-memory databases for executing 100% of our business logic these days. Letting the business write all the rules in SQL is the biggest win of my career so far.
Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.:
https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions
The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite.
Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data.
The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution.
You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts.
Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.—https://news.ycombinator.com/item?id=27568537
CSV import
ram: true etc. configurable)skip_first means to skip the (physical) first line of imported file or the first
line that is not skipped because it was blank or emptynull instead of an empty string TSV import (differs only in configuration (delimiter, quotes) from CSV)
Consider to use B3 serialize() for export { format: 'sqlite', }
Allow to open() RAM DB without path
Re-implement Hollerith codec for int32 only, making it faster and smaller; add documentation along
the lines of "DBA: VNRs" (in hengist dev) how to efficiently sort VNRs
we (temporarily?) accept a path argument in new Dba { path, }; this is used to attach the main
schema. Alternatively, and to keep the API mosre consistent(?), we could remove that argument and
stipulate that the first dba.open() call implicitly creates the dba.sqlt object; if the schema given
in that call is not main, then main will be a RAM DB.
discuss project focus and non-goals
while ICQL-DBA may gain some support for generated SQL, building kind-of-an-ORM is not one of its
goals. Cf. Datasette allows
constructs à la for row in db["dogs"].rows_where(select='name, age'): ... which already shows one of
the general disadvantages of ORMs, namely, that one has to suddenly re-create parts of SQL in a more
awkward way. Instead of
select name, age from dogs where age > 1 order by age desc;
now you have to write
db[ 'dogs' ].rows_where( 'age > 1', select = 'name, age', order_by = 'age desc' )
which is considerably longer, more convoluted, and has an appreciably larger API surface than
dba.query().
Observe that all of the arguments are really SQL fragments so in reality you still have to write SQL.
Worse, now the equivalent to that one SQL string "select name, age from dogs where age > 1 order by age desc;" has been diluted into four micro strings: 'dogs', 'age > 1', 'name, age', and 'age desc', plus three Python identifiers: rows_where, select, and order_by.
Worse again, you still don't get column and table name parametrization (you can replace 'name, age' with an interpolated string and variables, but you'll have to provide proper escaping (quotes,
spaces, capitalization) and concatenation (commas) yourself).
re/define APIs for
dba.create_function() (dba.function()dba.create_aggregate_function() (dba.aggregate()dba.create_window_function()dba.create_table_function()dba.create_virtual_table() dba.interpolate(): add simple facilities to construct basic SQL clauses and statements such as
inserts, values clauses &c. Syntax could use dollar, format, colon, name ($X:name) for named
insertions and question mark, format, colon (?X:) "" for positional inseertions. These would have to
be processed before dba.prepare sql is called. The format parameter is optional and defaults to I for
'identifier', as constructing statements with parametrized table and column names is the expected primary
use case for interpolation. Other values for format are L (for 'literal') and V (for 'values', i.e.
round brackets around a comma-delimited list of literals).
Examples:
dba.query "select $:col_a, $:col_b where $:col_b in $V:choices", \
{ col_a: 'foo', col_b: 'bar', choices: [ 1, 2, 3, ], }
dba.query "select ?:, ?: where ?: in ?V:", \
[ 'foo', 'bar', 'bar', [ 1, 2, 3, ], ]
enable 'concurrent UDFs' (user-defined functions that execute SQL statements)
SQLITE_USE_URI set to 1 so concurrent UDFs are possible. make it so that RAM DBs may be opened with a name parameter in cfg that is then used to build a
file URL like file:#{name}?mode=memory&cache=shared where name becomes the identifier for shared
memory across all better-sqlite3 instances running in the same process.
name parameter in cfg, assign a randomly chosen name like
rnd_4f333589dc3ae799; this can be recovered from a dba instance so that a second conncetion can be
instantiated.paths like '' and ':memory:'. implement dba.initialize_sqlt()
remove dependency on hollerith-codec, replace with simpler, faster implementation, publish as
icql-dba-vnr.
consider to replace tempy with a leaner module:
npx howfat -r table icql-dba
icql-dba@7.2.0 (63 deps, 14.36mb, 687 files)
╭───────────────────────┬──────────────┬──────────┬───────╮
│ Name │ Dependencies │ Size │ Files │
├───────────────────────┼──────────────┼──────────┼───────┤
│ hollerith-codec@3.0.1 │ 3 │ 967.28kb │ 91 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ tempy@1.0.1 │ 50 │ 919.3kb │ 396 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ intertype@7.6.7 │ 1 │ 509.95kb │ 41 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ cnd@9.2.2 │ 1 │ 270.78kb │ 38 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ mysql-tokenizer@1.0.7 │ 0 │ 98.6kb │ 15 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ n-readlines@1.0.3 │ 0 │ 96.01kb │ 16 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ csv-parser@3.0.0 │ 1 │ 58.66kb │ 29 │
├───────────────────────┼──────────────┼──────────┼───────┤
│ letsfreezethat@3.1.0 │ 0 │ 40.27kb │ 8 │
╰───────────────────────┴──────────────┴──────────┴───────╯
add automatic deletion of tables, views, indexes
consider to always use dba.pragma SQL"journal_mode=memory", add cfg property
set the allowable number of attached DBs to 125 (SQLITE_MAX_ATTACHED=125 in build-sqlite3; also
see "Maximum Number Of Attached Databases")
enable to open directories that contain multiple file-based SQLite DBs; schemas could be named after (portions of) filenames
detect format of SQLite3 files with _is_sqlite3_db(): first 16 bytes should contain SQLite format 3\000
remove references to hollerith-codec, encode(), decode (replaced by icql-dba-hollerith)
consider to scrap RTAs in context handlers; these can always be accomplished by using a wrapper function or closures. Instead use all context manager arguments to configure the context manager itself.
use the above change to implement transaction flavors:
insertMany.deferred(cats); // uses "BEGIN DEFERRED"
insertMany.immediate(cats); // uses "BEGIN IMMEDIATE"
insertMany.exclusive(cats); // uses "BEGIN EXCLUSIVE"
implement dba.check_foreign_keys() using dba.pragma SQL"foreign_key_check;"
implement dba.check_integrity() using dba.pragma SQL"integrity_check;"
implement dba.check_quick() using dba.pragma SQL"quick_check;"
implement dba.get_foreign_keys_deferred() using dba.pragma SQL"defer_foreign_keys;"
implement dba.set_foreign_keys_deferred() using dba.pragma SQL"defer_foreign_keys;"
implement dba.with_transaction()
deferred, immediate, and exclusive modes; default is deferred.better-sqlite3 transaction() docs say: "The wrapped function will also have access to the same
this binding as the transaction function."—see whether that makes sense to re-implement for the other
context handlers. implement dba.within_transaction using dba.sqlt.inTransaction
implement dba.with_foreign_keys_off()
implement dba.with_foreign_keys_deferred() using pragma defer_foreign_keys
implement context manager with_ram_db: ( f ) -> that copies DB to RAM, calls f(), and finally
copies DB back to file.
consider to reserve the main schema for DBA and plugins; this could help to avoid the bespoke
treatment of main when open()ing RAM, file DBs; also, would obliterate the need for the one-off
treatment of dba.sqlt.
[R] consider to change the default DB's name using SQLITE_DBCONFIG_MAINDBNAME (sqlite3.c#2524) (maybe
to icql) and use it only for internal purposes. Users can still have a main schema but it's not the
default one anymore.
better-sqlite3create table x ( ... ); being applied to
the 'main' schema even if it's not called main. select from x ...; statements are another matter;
they'll pick the first relation named x from any schema, in order of creation. In short, one would
have to require users to always prefix all their object names with a schema, which is a no-go; adjusting
all names in all statements is similarly no way to go.try to circumvent the problem with UDFs that perform DB queries by
better-sqlite3sqlt2; this must always co-perform
attach statementsdettach statementsdba.open() cannot be
passed to the constructor; after the change, they must be assed to the constructor to open the main
schema. This is more in line how better-sqlite3 and SQLite work, so should overall simplify / clarify
things.
dba.open(), dba.close() to dba.attach(), dba.detach()FAQs
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.
Research
Security News
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.