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 db object will then have a (non-enumerable) property db.sqlt1 which is a better-sqlite3 connection
to a 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).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.
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
alternative Connection to Avoid Connection Busy ErrorsSQLite imposes certain restrictions on what one can and cannot do in a concurrent fashion, one restriction
being that within the same connection, one cannot at the same time iterate over query results and insert
valuesβnot even into a completely unrelated table. There are ways to get around that limitation. For one,
the standard recommendation of the maker of better-sqlite3 is to just fetch all the needed rows from the
DB and then iterate over the list of values. This is totally doable and the simplest and most transparent
solution, but of course a nagging thought remainsβwhat if the dataset gets really huge? In reality, this may
turn out never to be a problem, realistically, but that consideration won't make that nagging thought
vanish.
There's a (seemingly) better way. Commit 57e062a: make alt an on-demand clone of present
instance
introduces the new (non-enumerable) property db.alt which represents a clone of the db object. Previous
versions had two underlying DB connections sqlt1 and sqlt2 which could be used for the purposes
described in this section, but their drawback was that one falls back to the underlying better-sqlite3 API
which can be a little confusing.
Let's have a look at a toy DB and see how to use db.alt. This is the definition, two tables with one
integer field each:
#.................................................................................
db SQL"""
create table foo ( n integer );
create table bar ( n integer );"""
for n in [ 10 .. 12 ]
db SQL"insert into foo ( n ) values ( $n );", { n, }
And here's what we want to accomplish: read data from one table and, based on that data, insert records into another one. NaΓ―vely one would perhaps write it this way (the transaction being added because we need it later anyway):
db.with_transaction =>
for row from db SQL"select * from foo order by n;"
info '^806-2^', row
db SQL"insert into bar values ( $n );", { n: n ** 2, }
return null
This will not run, however, but fail with TypeError: This database connection is busy executing a query.
This is where db.alt comes in: we have to use one connection for the iteration and another one for the
insertion; this works:
#.................................................................................
# (1)
db.with_transaction =>
for { n, } from db.alt SQL"select * from foo order by n;"
# ^^^^^^
db SQL"insert into bar values ( $n ) returning *;", { n: n ** 2, }
return null
The following points should be kept in mind: Explicit transactions and explicit prepared statements are
they key factors for speedy inserts. Since explicit transactions are crucial for concurrent inserts, it's
recommended to do all inserts within explicit transactions.
Therefore, because it's good practice to use explicit transactions and explicit prepared statements when
doing inserts, most of the time inserts should take on the form shown in snippets (2) or (4):
#.................................................................................
# (2)
insert_into_bar = db.prepare SQL"insert into bar values ( $n ) returning *;"
db.with_transaction =>
for { n, } from db.alt SQL"select * from foo order by n;"
insert_into_bar.run { n: n ** 2, }
return null
Observe that we have to explicitly exhaust the iterator that is returned from insert ... returning
statements; to do so, either use db.first_row() or call a prepared statement's .get() (instead of
.run()) method:
#.................................................................................
# (3)
db.with_transaction =>
for { n, } from db.alt SQL"select * from foo order by n;"
new_row = db.first_row SQL"insert into bar values ( $n ) returning *;", { n: n ** 2, }
return null
#.................................................................................
# (4)
insert_into_bar = db.prepare SQL"insert into bar values ( $n ) returning *;"
db.with_transaction =>
for { n, } from db.alt SQL"select * from foo order by n;"
new_row = insert_into_bar.get { n: n ** 2, }
return null
SQL Tag Function for Better Embedded SyntaxMixing SQL and application code has the drawback that instead of editing SQL
in your SQL-aware text editor, now you are editing bland string literals in
your SQL-aware editor. If there only was a way to tell the editor that some
strings contain SQL and should be treated as such!βWell, now there is. The
combined power of [JavaScript Tagged Templates]
(https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_template_literals)
and an (exprimental proof-of-concept level) [set of Sublime Text syntax
definitions called coffeeplus]
(https://github.com/loveencounterflow/coffeeplus) makes it possible to embed
SQL into JavaScript (and CoffeeScript) source code. The way this works is by
providing a 'tag function' that can be prepended to string literals. The name
of the function together with the ensuing quotes can be recognized by the editor's
hiliter so that constructs like SQL"...", SQL"""...""" and so will trigger
switching languages. The tag function does next to nothing; here is its definition:
class DBay
@SQL: ( parts, expressions... ) ->
R = parts[ 0 ]
for expression, idx in expressions
R += expression.toString() + parts[ idx + 1 ]
return R
It can be used like this:
{ DBay } = require 'dbay'
{ SQL } = DBay
db = new DBay { path: 'path/to/db.sqlite', }
for row from db SQL"select id, name, price from products order by 1;"
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# imagine proper embedded hiliting etc here
console.log row.id, row.name, row.price
Be aware that coffeeplus is more of an MVP than a polished package. As such, not
even reckognizing backticks has been implemented yet so is probably best used
with CoffeeScript.
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', ]
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Strings
std_str_reverse()std_str_join()std_str_split()std_str_split_re()std_str_split_first()std_re_matches()XXX
std_generate_series()Output
std_echo()std_debug()std_info()std_warn()Exceptions and Assertions
std_raise( message )βunconditionally throw an error with message given.std_raise_json( facets_json )βunconditionally throw an error with informational properties encoded
as a JSON string.std_assert( test, message )βthrow an error with message if test is falsy.std_warn_if( test, message )βprint an error message if test is truthy.std_warn_unless()βprint an error message if test is falsy.Variables
std_getv()std_variables()Dates, Time, Durations, Timestamps
dt_dbayts_from_isots
dt_parse
dt_format
dt_isots_from_dbayts
std_assert: ( test, message ) -> throws error if test is false(y)std_warn_unless: ( test, message ) -> prints warning if test is false(y)count(*).limit 1 where appropriate.select
*,
std_assert(
count(*) > 0,
'^2734-1^ expected one or more rows, got ' || count(*) ) as _message
from nnt
where true
and ( n != 0 );
select
*,
std_assert(
count(*) > 0, -- using `count(*)` will cause the function to be called
-- even in case there are no matching rows
'^2734-2^ expected one or more rows, got ' || count(*) ) as _message
from nnt
where true
and ( n != 0 )
and ( t = 'nonexistant' ); -- this condition is never fulfilled
std_getv( name ) to retrieve variable values which must have been set
immediately prior to accessing the view.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.
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
with_deferred_write: ( f ) ->βwill call the function as f write, where write may be used like
the db object. Each call to write() will put any arguments into a cache; typically, this will be calls
of the form write my_prepared_statement, my_data. When f() has finished, the items in cache will be
used to call the db object as in db whatever... for whatever in buffer. These repeated calls will
happen inside an implicit transaction in case no transaction is already open.
db.altβthe db.alt property, when first retrieved, opens a second better-sqlite3 connection to
the same database file. Using pragma journal_mode = wal (which is the default as of DBay v14.16), this
(or any other secondary) connection can be used for concurrent writes.
observe that insert statements generated with db.prepare_insert() are now implicitly bound to db.alt
to improve DBay's concurrency story.
Observe that rows inserted in the same transaction are not visible to the alternative connection until
those rows have been committed. Generally, one will want to insert data in one transaction, finish it,
and then begin a new (explicit or implicit) transaction in order to iterate over existing and possibly
inserting new data. It is obvious that one does not want this newly inserted data to be visible just yet
because that could cause an infinite loop (just like appending to an array while stepping over its
elements would create an infinite loop).
Because User-Defined Functions have several shortcomings in SQLite (as discussed under Notes on User
Defined Functions (UDFs), below), an alternative mechanism named
dbay-sql-macros has been conceived to work around
those issues. It has been integrated into DBay and is accessible via two methods, declare() and
resolve(), under the macros key of a db instance. Furthermore, when constructing the DBay instance,
one can pass in { macros: true, } to get implicit macro resolution. An example:
{ DBay } = require 'dbay'
{ SQL } = DBay
db = new DBay { macros: true, }
#.........................................................................................................
### NOTE exact syntax subject to change; for now, this works: ###
db.macros.declare SQL"""@secret_power( @a, @b ) = power( @a, @b ) / @b;"""
#.........................................................................................................
result = db.all_rows SQL"""select @secret_power( 3, 2 ) as p;"""
# [ { p: 4.5 } ]
For more details, head over to the documentation for
dbay-sql-macros.
SQLite principally differs from client/server RDBMSes in that it allows User Defined Functions (UDFs) only on the DB connection of the host application
these UDFs are written in the language and run in the environment of the host application
I believe UDFs are, as such, a huge boon because they help to push many chores to the DB and allow for such nifty things as having generated columns whose contents are not stored but indexed and that can call into existing code of the hosting app without code duplication and without network roundtrips (both of which are hallmarks of client/server architectures)
but the downside of connection-defined UDFs is that SQLite DBs created with UDFs will break when the
environment changes (e.g. when openening a second connection to the same DB without recreating all UDFs or
openening the DB with other tools such as the sqlite3 command line tool); an abortive error will occur
as soon as any statement (such as selecting from a view whose definition includes a call to a UDF) is
encountered. While one may be able to perform some operations that do not cause a function to be called,
it is not a hallmark of a safe operations regime when things work sometimes without any warning only to
break under certain conditions. I'm not aware of a way to conveniently check an SQLite DB for the use or
lack of use of UDFs so apparently the best thing one can do is proofread the DDL statements and/or do
selects from each relation.
this means that a DB created with UDFs will not be amenable for any of the helpful tools that exist (like ER diagrammers and so on)
one can also not pass a DB file around for other people to have a gander into the dataβusing UDFs means your host application (or all the relevant parts of it) has to be reproduced on the other machine, and even then, only the host application can provide access to the dataβagain, no external tooling here
because UDFs are so useful, it's probably worthwhile to think about how to work around the limitations. Possible solutions include:
CREATE FUNCTION; the body of such a function could be formulated in much the same way as the already existing
syntax for CREATE TRIGGER which likewise allows to define a block with a sequence of SQL statements.
In its simplest form, CREATE FUNCTION would allow for parametrized views, which would be incredibly
useful. Such a feature request has been issued before
(not listed on the Open Feature Requests page) in 2021 and
lead to an extended and informative discussion (see triggers, below), but so far, nothing has come of
it. Rating: +0 because while everyone is encouraged to do it, hopes are not high IMHO; however see
below for a draft that I think could have
some chances.sqlite3 command line tool, but even then, you must ship the extension alongside with the DB file, and
the receiving partner will have to do a bit more work to open the DB (and be willing to use the command
line). They must also be on a compatible system or your *.so/*.dll will not work, or else you must
compile the extension for multiple systems. Rating: -1 because who wants to do the authoring and
testing and compiling stuff when so little universal usability comes out of it.All of these solutions suck in one or the other way.
CREATE FUNCTION statement; its body would be
much like the existing syntax for triggers. The default (and, initially, only) language to be used is SQL.Example:
CREATE FUNCTION product( a number, b number ) RETURNS number LANGUAGE SQL
BEGIN
SELECT a * b; /* or RETURN a * b; */
END;
END;
Notes:
- Type annotations and the
RETURNSclause should be optional as inCREATE TABLEstatements, whereasLANGUAGE SQLshould initially be made mandatory to avoid premature fixation of a bad default.- Initially at least, functions should not be multi-dispatch (i.e. a name can only appear at most once).
- Nice-to-have:
CREATE OR REPLACE,IF NOT EXISTS,DROP FUNCTION.
This form is already useful because now you can bundle and name recurrent expressionsβanything that can
appear in a scalar (single-values) SELECT statement can be named and collected into libraries.
Extension Add to this RETURNS SETOF $TYPE, RETURNS SETOF ROW, and now you can have table-valued
functions a.k.a. parametrized views!
Extension Add to this statement sequences.
Extension Add to this branching (IF/THEN and/or CASE/WHEN). This extension would cross the line
where language inside a function declaration is significantly different from that outside. OTOH branching
could conceivably work outside of functions, much like C's preprocessor directives.
Extension Add to this LOOP/BREAK/BREAK IF loops.
Extension Add to this YIELD for use in table-valued functions.
Extension Add to this (function-local) variables. These are in principle already implemented in the form of function parameters.
Extension Some support for dynamic SQL that could potentially be much less clunky than what PostgreSQL
offers; at first one would only need a syntax to signify safe interpolation as identifier, e.g. SELECT * FROM @table_name; or similar.
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.
## Use npm, Not pnpm
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.
[β] port foundational code from hengist &c
[β] at construction time, allow dbnick when path is given and ram is false
[β] to solve the table-UDF-with-DB-access conundrum, consider
db.save(). See
https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/threads.mdselects from inside UDFs, so maybe there's a teeny, fixable difference between how both are
implemented that leads to the undesirable behavior[β] let users choose between SQLite-only RAM DBs and tmpfs-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.
[β] implement context handler for discardable / temporary file
[β] allow to call DBay::do -> ... with an asynchronous function
[β] implement DBay::open(), DBay::close()
[β] ensure how cross-schema foreign keys work when re-attaching DBs / schemas one by one
[β] demote random from a mixin to functions in helpers.
[β] implement db.truncate() / db.delete(); allow to retrieve SQL.
[β] implement DBay::insert_into.<table> [ 'field1', 'field2', ..., ], { field1, field2, ..., };
allow to retrieve SQL.
[β] clarify whether UDFs get called at all when any argument is null b/c it looks like they
don't get called which would be unfortunate
[β] add schematic to clarify terms like database, schema, connection; hilite that UDFs are defined on connections (not schemas or databases as would be the case in e.g. PostgreSQL).
[β] allow to transparently treat key/value tables as caches
[β] implement escaping of dollar-prefixed SQL placeholders (needed by create_insert()).
[β] implement
db.commit()db.rollback()[β] allow to use sets with sql.V()
[β] implement export/snapshot function that generates a DB with a simplified structure:
strict and similar newer attributessqlite3 command line,
visualize-sqlite[+] consider to implement trash functionality now moved to
DeSQLtrash() as trash_to_sql() (path optional), trash_to_sqlite()
(path optional)
[β] rewrite all uses of plain E to @E
[β] limit support for schemas, especially in plugins; require a separate instance of DBay for each DB
file (so that all DB objects are in the default main namespace and the SQL"#{schema}.xxx" constructs
can become SQL"xxx"). Complex DBs can still be assembled with db.open(), but one must keep in mind
that in SQLite, foreign keys do not work across schemas, only joins so, so that limits the usefulness
of multi-schema connections.
[β] consider to change call argument in UDFs to callee
[β] add fields to std_re_matches():
[β] consider to change construction method of DBay instances to returning a proxy over a function
(as done in guy.obj.Strict_proprietor.get())
db.create_table_function
name: prefix + '_re_matches'
columns: [ 'match', 'capture', 'start', 'stop', ]
parameters: [ 'text', 'pattern', ]
rows: ( text, pattern ) ->
regex = new RegExp pattern, 'g'
while ( match = regex.exec text )?
[ m, c, ] = match
yield [ m, ( c ? null ), start: match.index, stop: match.index + m.length, ]
return null
[β] update to an SQLite version that includes
#9430ead7ba433cbf to fix an issue with window
functions
[β] write a chapter about application architecture best practices, including:
sqlite3 CLI will not work)on insert trigger that
performs inserts on another table using select from t; ahould an error occur in the last step, SQLite
will still attribute it to the original insert statement (without giving it any kind of locality or
mentioning t's role))db SQL"do this;", db SQL"do that;" over db SQL"do this; do that;")[β] implement select * from t SQL generation
[β] could the SQL string annotation / tagged literal function be syntactically extended to allow
simpler interpolation of escaped names? Could we instantiate it with a dictionary of values (implement in
Guy)
[β] would it be possible to keep the application code in its own tables? one could then ship the application by sending a single DB file and the instruction to run it using a standard DBay installation
[β] provide API for pragma journal_mode; make wal the default
[β] use GUY.datetime for dt features in stdlib
[β] see whether we can support libSQL
[β] review BEGIN CONCURRENT allows multiple writers
[β] review the below, adjust default settings accordingly:
[β] see whether could support libSQL, as described in WebAssembly functions
for your SQLite-compatible
database
[β] ensure reasonable defaults:
pragma journal_mode=WAL;: always use WAL modeIMMEDIATE transactions (needed so busy_timeout is always honored)pragma busy_timeout = 60000; (1min waiting time for writers if a writer is busy)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', }, -> ....db = 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 proliferationdb.do() accept prepared statement objects.first_row(), all_rows() etc accept statements and stringscfg.prefix for (inherently schema-less) UDF names (and require a trailing
underscore to be part of the prefix), and cfg.schema for plugin-in-specific DB tables and views; in the
future, we should use a single parameter for both (and make the underscore implicit). In addition, it
should be possible to choose whether a plugin will create its objects with a prefix (in the same schema as
the main DB) or within another schema.insert statements without explicit fieldsE attribute to instance giving access to error classes (mainly for plugin use)as_object: ( key, sql, P... ) ->YYYYMMDD-HHmmssZ, e.g. 20220426-171916Z is the time of this writingZ
instead of numerical offsetbuild-sqlite3: Permission denied bug
pnpm version minor && pnpm publish --access public && git pushnpm version minor && npm publish --access public && git pushselect * from foo( x ) as d; to get select * from ( select a, b, c from blah order by 1 ) as d (i.e. inline expansion)
dbay-sql-macros@ as in @f := ( a, b ) -> g( a, @h( b ) ); (define function f() with two
parameters, calls g(), @h(), where g() is a built-in SQLite function and @h() is another macro)create table d ( name @nonempty_text, email @email ); where data type annotations are replaced with their basic types (both text here) and
the statement is amended with check clauses.
dbay-sql projectdb.altdb.sqlt1) and secondary (db.alt.sqlt1)
connections to avoid surprising messages like no such table: f when doing concurrent writes.FAQs
In-Process, In-Memory & File-Based Relational Data Processing with SQLite, BetterSQLite3
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
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.
Research
Security News
A threat actor's playbook for exploiting the npm ecosystem was exposed on the dark web, detailing how to build a blockchain-powered botnet.
Security News
NVDβs backlog surpasses 20,000 CVEs as analysis slows and NIST announces new system updates to address ongoing delays.