jsish
Sqlite
Not logged in

The Sqlite driver provides three basic commands for executing sql:

new Sqlite()

A simple session might look like:

var db  = new Sqlite('testsql.db');
db.eval('CREATE TABLE players(name TEXT,age INTEGER);');
db.query('INSERT INTO players VALUES(?,?);', {values:["Barry",44]});
var age = db.onecolumn('SELECT age FROM players WHERE name = "Barry";');

A more complete example is here: ../tests/sqlite.js.

Options passed in the object argument to new Sqlite(), may specify any of the following:

Option Type Description Default
bindWarnBOOLTreat failed variable binds as a warning.false
debugCUSTOMEnable debug trace for various operations.
queryOptsCUSTOMDefault options for exec.
forceIntCUSTOMBind float as int if possible.
maxStmtsINTMax cache size for compiled statements.
mutexCUSTOMMutex type to use.
nameDSTRINGName for this db handle.
nocreateBOOLDatabase is must already exist.false
readonlyBOOLDatabase is readonly.false
vfsVALUEVFS to use.

Some of these options can later be changed using the conf() method, eg.

db.conf({maxStmts:100});

Refer to the Reference for details.


eval()

The eval() method is used to execute simple Sql. It takes no options, and returns no values.

It can also be used to execute multiple semicolon-separated statements:

db.exec('CREATE TABLE foo(a,b);'+
'INSERT INTO foo VALUES(1,2);'+
'INSERT INTO foo VALUES("X","Y")');

This makes it useful for bulk loading.


oncolumn()

onecolumn() provides no inputs or outputs. It simply returns the first column of the first row. The mode and other options are ignored.

var maxid = db.oncolumn('SELECT max(id) FROM foo');

query()

The workhorse method is query() which:

Here is an example:

var x = db.query('SELECT * FROM foo');

Query Options

Query options can be controlled either of two ways. Per query, as in:

db.query('SELECT * FROM test1', {mode:'json'}); 

or we can change the defaults (for the connection) like so:

db.conf({queryOpts:{mode:'json'}});
db.query('SELECT * FROM test1'); 
db.query('SELECT * FROM test2'); 

Here is a list of the available query() options:

Option Type Description Default
callbackFUNCFunction to call with each row result.
headersBOOLFirst row returned contains column labels.
limitINTMaximum number of returned values.
mapundefBOOLIn variable bind, map an 'undefined' var to null.
modeCUSTOMSet output mode of returned data.
nocacheBOOLQuery is not to be cached.
nullvalueSTRKEYNull string output (for non js/json mode).
separatorSTRKEYSeparator string (for csv and text mode).
CDataSTRKEYName of CData object to use.
typeCheckCUSTOMType check mode.warn
tableSTRKEYTable name for mode=insert.
valuesARRAYValues for ? bind parameters.
varNameSTRBUFArray var for ? bind parameters.
widthCUSTOMIn column mode, set column widths.

Outputs

The returned value from a query is determined by the chosen output mode.

The default mode (rows) just returns an array of objects, which looks like this:

[ { a:1, b:2 }, { a:"X", b:"Y" } ]

The choices for mode are a superset of those available in the sqlite3 command-line tool, namely:

ModeDescriptionPurpose
array1dFlat array of valuesscript
arraysAn array of row-arraysscript
columnColumn aligned texttext
csvComma (or separator) separated valuesexport
htmlHtml table rowsbrowser
insertSql insert statementsexport
jsonJSON string as an array of objectsbrowser
json2JSON string with names/values in separate arraysbrowser
lineOne value per line in name=value formexport
listThe default sqlite3 outputtext
noneNo output
rowsAn array of row-objects (the default)script
tabsTab separator delineated valuesscript

We can change the output mode for a query() using:

db.query('SELECT * FROM foo', {mode:'list'});

to return:

1|2|X
Y|3|Z

Note: output for some modes is affected by the headers and separator options.

JSON

The json modes are useful when data is destined to be sent to a web browser, eg. via websockets.

db.exec('DROP TABLE IF EXISTS foo; CREATE TABLE foo(a,b);');
var n = 0, x = 99;
while (n++ < 3) {
    db.query('INSERT INTO foo VALUES(@x,@n)');
    x -= 4;
}
x=db.query('SELECT * FROM foo',{mode:'json'});

Which produces:

[ {"a":99, "b":1}, {"a":95, "b":2}, {"a":91, "b":3} ]

Where large amounts of data are involved, the headers option can be used to reduce size:

db.query('SELECT * FROM foo',{mode:'json', headers:true});

to output:

[ ["a", "b"], [99, 1], [95, 2], [91, 3] ]

The "json2" mode is used to split headers and values out into separate members:

db.query('SELECT * FROM foo',{mode:'json2'});

Which produces:

{ "names": [ "a", "b" ], "values": [ [99, 1 ], [95, 2 ], [91, 3 ] ] }

Callback Function

Normally, query() will execute an entire query before returning the result. There are two ways to change this:

Either way this results in invocation of the callback for each row result:

function myfunc(n) { puts("a=" + n.a + ", b=" + n.b); }
db.query('SELECT * FROM foo',myfunc);

If the callback function returns false, evaluation will terminate.

db.query('SELECT * FROM foo', function (n) {
    puts("a=" + n.a + ", b=" + n.b);
    if (a>1) return false;
  });

Inputs

Sql inputs can be easily formatted using strings:

var a=1, b='big';
db.query('INSERT INTO foo VALUES('+a+','+b+')');

However this raises issues of security and predictability. Fortunately variable binding is easy.

Bindings

Sqlite variable binding uses "?" placeholders to refer to array elements., eg:

db.query('INSERT INTO foo VALUES(?,?)', {values:[11,12]});

var vals = [9,10];
db.query('INSERT INTO foo VALUES(?,?)', {values:vals});

which for a small number of parameters is more than adequate.

Named-Binds

Sqlite named-bindings begin with the characters: :, @, and $.

Here is an example:

var x1=24.5, x2="Barry", x3="Box";
db.query('INSERT INTO test2 VALUES( :x1, @x2, $x3 );');

The $ bind may append round-brackets () to refer to compound variables.

This example binds to objects members:

var y = {a:4, b:"Perry", c:"Pack"};
db.query('INSERT INTO test2 VALUES( $y(a), $y(b), $y(c) );');

And this one to arrays:

var Z = 2;
var y = [9, 'Figgy', 'Fall'];
db.query('INSERT INTO test2 VALUES( $y(0), $y(1), $y([Z]) );'); 

Or more usefully:

var y = [
    {a:4, b:"Perry", c:"Pack"},
    {a:9, b:'Figgy', c:'Fall'}
];
for (var i=0; i<y.length; i++)
    db.query('INSERT INTO test2 VALUES($y([i].a), $y([i].b), $y([i].c);'); 

The contents of the round-brackets can contain multiple levels of dereference (but not expressions).

Here is a selection of bindings, and their variables:

BindingVariable Comment
:XX
@XX
$XX
$X(a)X.aImplicit object member
$X(9)X[9]Implicit array (leading digits)
$X([a])X[a]Explicit array
$X(a.b)X.a.bCompound object
$X([a].b)X[a].bCompound array + object, etc

Types

A type specifier may also be included in a $X(Y) binding, as in:

var y = {a:4, b:"Purry", c:"Pax"};
db.query('INSERT INTO test2 VALUES( $y(a:integer), $y(b:string), $y(c:string) );');

The type is the part after the colon ":", and just before the close round-brace.

By default, a type is used to convert data sent to MySql to the correct type.

Type specifiers are supported for all variants of $X(Y) binding, such as:

var Z = 0;
var x = ['Figgy'];
var y = {c:'Fall'};
db.query('INSERT INTO test3 VALUES( $x(0:string), $y(c:string), $x([Z]:string) );'); 

The supported type names are:

TypeDescription
boolA tiny/bit value
doubleA double value
integerA 64 bit wide integer
stringA string
blobA blob
dateA date value
datetimeA date+time value
timeA time value
timestampA unix timestamp

We can also change the type-checking behaviour via the typeCheck query option:

For example, we can instead cause an error to be kicked an error with:

var x = [ 'bad' ];
db.query('UPDATE test SET n = $x(0:number) );', {typeCheck:'error'}); 

The valid typeCheck modes are:

ValueDescription
convertCoerce value to the requested type (the default)
warnGenerate a warning
errorGenerate an error
disableIgnore type specifiers

Miscellaneous

Building

The Sqlite driver comes (by default) builtin to Jsi.

It can also be built the shared library can be built (for unix) with:

make libmysql

User Functions

SQL functions can be defined in javascript using func():

db.func('bar',function(n) { return n+'.000'; });
puts(db.onecolumn('SELECT bar(a) FROM foo WHERE b == 2;'));

Timestamps

Sqlite performs internal time calculations based on the Gregorian calendar.

But Javascript time functions use the unix epoch to store the number of milliseconds since Jan 1, 1970 UTC.

We can create a table with a DEFAULT date field as a number using:

CREATE TABLE mtable(
  name,
  mytime DATETIME DEFAULT(round((julianday('now') - 2440587.5)*86400000.0))
);

We can output this as a text time stamp using:

SELECT strftime('%Y-%m-%d %H:%M:%f',mytime/1000.0,'unixepoch') FROM mytable;
SELECT strftime('%Y-%m-%d %H:%M:%f',mytime/1000.0,'unixepoch','localtime') FROM mytable;

which outputs:

2015-01-12 13:46:40.252
2015-01-12 18:46:40.252

Caching

In the interest of efficiency, compiled queries are cached on a per connection basis. The size of the cache is controlled by the maxStmts option.

You can also disable caching for individual querys with nocache. And any query begining in ';' will not be cached.