Not logged in


DbQuery is a query engine for loading and storing C-struct data to/from Sqlite tables, with:

DbQuery features include:

Traditional Sqlite-C

The standard Sqlite has an easy to use C-API with two groups of functions:

which are employed as follows:

stmt = sqlite3_prepare(db, "SELECT a,b FROM mytbl WHERE a=?1 AND b=?2", ...)
sqlite_bind_int(stmt, 1, mydata.b);
mydata.a = sqlite3_column_int(stmt, 2);

While this approach is trivial with small schemas, code validation gets to be an issue as database complexity increases because:

For example, here is an except from real world code:

stmt = sqlite3_prepare(db, "INSERT INTO descriptions VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17,?18,?19,?20);");

Handling bindings reliably for such queries becomes increasingly tedious.

DbQuery Sqlite-C

The Jsi_DbQuery() function uses an abstraction layer based on the use of a specification:

static Jsi_OptionSpec MyOptions[] = {
    JSI_OPT(INT,        MyData, id,     .help="Int id", .userData="DEFAULT 0 CHECK(id>0)"),
    JSI_OPT(DOUBLE,     MyData, max,    .help="Max value"),

This spec describes a struct:

typedef struct { int id; double max; ... };

And together these are supplied as inputs to the engine:

int Jsi_DbQuery(Jsi_Db *jdb, Jsi_OptionSpec *spec, void *data, int numData, const char *query, Jsi_DbOpts *opts);

Jsi_DbQuery() compiles, binds, executes and retrieve results for queries and in a single statement, transferring data to/from C structs:

Jsi_DbQuery(jdb, spec, &mydata, 1, "SELECT %s FROM mytbl WHERE id = :id", NULL)
Jsi_DbQuery(jdb, spec, &mydata, 1, "INSERT INTO mytbl %s", NULL)


Incorporating DbQuery into your application is quite simple: Download Jsi, and then:

#define JSI__SQLITE 1
#include <sqlite3.h>
#include "jsi.c"

int main(int argc, char *argv) {
  Jsi_Db *jdb = Jsi_DbNew(...);
  Jsi_DbQuery(jdb, ... );

Detailed Example

Given the following database-table:

CREATE TABLE mytable (
    id INT,
    max FLOAT,
    myTime TIMEINT,

and a corresponding struct definition:

typedef struct {
    char name[16];
    int id;
    double max;
    int64 myTime;
    int mark;
    int markSet;
    Jsi_DString desc;
    int64 rowid;
    char dirty;
} MyData;

Define a descriptor array of type Jsi_OptionSpec:

static const char *markStrs[] = {"","A","B","C","D","F",NULL};

static Jsi_OptionSpec MyOptions[] = {
    JSI_OPT(STRBUF,     MyData, name,   .help="Fixed size char buf", .userData="DEFAULT ''" ),
    JSI_OPT(DSTRING,    MyData, desc,   .help="Description field of arbitrary length"),
    JSI_OPT(INT,        MyData, id,     .help="Int id", .userData="DEFAULT 0 CHECK(id>0)"),
    JSI_OPT(DOUBLE,     MyData, max,    .help="Max value"),
    JSI_OPT(DATETIME,   MyData, myTime, .help="A unix/javascript time field in milliseconds (64 bits)", .userData="DEFAULT" ),
    JSI_OPT(CUSTOM,     MyData, mark,   .help="Marks", .custom=Jsi_Opt_SwitchEnum,   .data=markStrs ),
    JSI_OPT(CUSTOM,     MyData, markSet,.help="A set", .custom=Jsi_Opt_SwitchBitset, .data=markStrs ),
    JSI_OPT(WIDE,       MyData, rowid,  .help="DB rowid for update/insert; not stored in db", .flags=JSI_OPT_DB_ROWID),
    JSI_OPT(BOOL,       MyData, isdirty,.help="Dirty flag: not stored in db", .flags=JSI_OPT_DB_DIRTY),
    JSI_OPT_END(        MyData, .help="This is a struct for dbdemo")

Which we can then use to store/load data by calling Jsi_DbQuery():

Jsi_Db *jdb = Jsi_DbNew("~/mytables.db", 0);
MyData d = {"myname", 99, 9.0};
Jsi_DbQuery(jdb, MyOptions, &d, 1, "INSERT INTO mytable %s", NULL);
Jsi_DbQuery(jdb, MyOptions, &d, 1, "SELECT %s FROM mytable", NULL);
Jsi_DbQuery(jdb, MyOptions, &d, 1, "UPDATE mytable SET %s", NULL);
Jsi_DbQuery(jdb, MyOptions, &d, 1, "SELECT id,name FROM mytable WHERE rowid=:rowid", NULL);

The return value is either the number of rows loaded, modified or stored, or:

Ideally, a function wrapper is used to improve readability and type-checking:

int db_MyData(MyData *data, const char *query) {
    return Jsi_DbQuery(jdb, MyOptions, data, 1, query, NULL);

db_MyData(&d, "INSERT INTO mytable %s")
db_MyData(&d, "UPDATE mytable SET %s")


The above example operated on a single row. Multiple rows of data can be handled in a couple of ways.

Single Struct

The brute-force way to process a table of data is to iterate over a single struct:

MyData mydata = {.id=99, .max=100.0, .mark=MARK_A, .markSet=6};
mydata.myTime = time(NULL)*1000LL;
strcpy(mydata.name, "maryjane");
Jsi_DSSet(&mydata.desc, "Some stuff");

Jsi_DbQuery(jdb, 0, 0, 0, ";BEGIN", NULL);
for (i=0, n=1; i<10 && n==1; i++) {
    n = Jsi_DbQuery(jdb, MyOptions, &mydata, 1, "INSERT INTO mytable %s", NULL);
Jsi_DbQuery(jdb, 0, 0, 0, ";COMMIT", NULL);

While this works, it requires more C-code.

Arrays of Structs

A simpler way to process a table is via an array of structs, with a single call. Three forms of array are supported:

Static Arrays

A static array has all storage pre-allocated by the user:

MyData mydatas[10];
int cnt = Jsi_DbQuery(jdb, MyOptions, mydatas, 10, "SELECT %s FROM mytable", NULL);

for (i=0; i<cnt; i++)
    mydatas[i].id += i;
n = Jsi_DbQuery(jdb, MyOptions, mydatas, cnt, "UPDATE mytable SET %s WHERE rowid = :rowid", NULL);

This example simply uses:

Note that a BEGIN/COMMIT is implicitly performed for updates and inserts.

Jsi_DbOpts Flags

Following are the option-flags for Jsi_Db commands:

typedef struct {
    Jsi_Bool ptrs:1;        /* Data is a fixed length array of pointers to (allocated) structs. */
    Jsi_Bool ptr_to_ptrs:1; /* Address of data array is passed, and this is resized to fit results. */
    Jsi_Bool mem_clear:1;   /* Before a query previously used data is reset to empty (eg. DStrings). */
    Jsi_Bool mem_free:1;    /* Reset as per mem_clear, then free data items (query may be empty). */
    Jsi_Bool dirty_only:1;  /* Used by sqlite UPDATE/INSERT/REPLACE. */
    Jsi_Bool no_begin_commit:1;/* Do not wrap UPDATE in BEGIN/COMMIT. */
    Jsi_Bool no_cache:1;    /* Do not cache statement. */
    Jsi_Bool no_static:1;   /* Do not bind text with SQLITE_STATIC. */
    uint reserved:24;       /* Reserved for future use. */
} Jsi_DbOpts;

These are used in the following calls.

Static Array of Pointers

To use an array of pointers we pass the ptrs flag.

static MyData *mdPtr[10] = {};
Jsi_DbOpts opts = {.ptrs=1};
n = Jsi_DbQuery(jdb, MyOptions, mdPtr, 10, "SELECT %s FROM mytable", &opts);

In this mode, memory is allocated on demand (up to the maximum size).

Note the data argument is now an array of pointers to structs (void **).

Dynamic Array of Pointers

For fully dynamic allocation, both of the array and the struct pointers, we pass the ptr_to_ptrs flag:

MyData **dynPtr = NULL;
Jsi_DbOpts opts = {.ptr_to_ptrs=1};
n = Jsi_DbQuery(jdb, MyOptions, &dynPtr, 0, "SELECT %s FROM mytable WHERE rowid < 5", &opts);
n = Jsi_DbQuery(jdb, MyOptions, &dynPtr, n, "SELECT %s FROM mytable LIMIT 1000", &opts);

This mode will manage an extra NULL pointer at the end of the array to make the current length detectable (ie. when the num parameter is 0).

Note the data argument has changed again; it is now a pointer to an array of pointers (void***).

Function Wrappers

The above examples have several drawbacks:

  1. the data argument is not type-checked.
  2. calls to Jsi_DbQuery takes a lot of arguments (6).

The first problem is easily demonstrated:

Jsi_DbQuery(jdb, MyOptions, "oops, not a struct", n, "SELECT %s FROM mytable", NULL);  // Invalid, but no compiler warning!

The simplest solution defines wrapper functions:

int My_Stat(MyData *data, int num, const char *query, Jsi_DbOpts *opts) {
    return Jsi_DbQuery(jdbPtr, MyOptions, data, num, query, opts);

static int My_Semi(MyData **data, int num, const char *query, Jsi_DbOpts *opts) {
    Jsi_DbOpts o = {};
    if (opts)
        o = *opts;
    o.ptrs = 1;
    return Jsi_DbQuery(jdbPtr, MyOptions, data, 0, query, &o);

static int My_Dyn(MyData ***data, int num, const char *query, Jsi_DbOpts *opts) {
    Jsi_DbOpts o = {};
    if (opts)
        o = *opts;
    o.ptr_to_ptrs = 1;
    return Jsi_DbQuery(jdbPtr, MyOptions, data, 0, query, &o);

My_Stat(mydatas, n, "SELECT %s FROM mytable;", NULL);
My_Semi(mdPtr,   n, "SELECT %s FROM mytable;", NULL);
My_Dyn (&dynPtr, n, "SELECT %s FROM mytable;", NULL);

In addition to the adding type checking, the resulting code is simpler.

Multi-Struct Bind

Multiple structs can be bound to, using a different bind character for each:

int My_Bind(MyData *data1, int num, MyData *data2, const char *query)
    Jsi_DbMultipleBind binds[] = {
        { ':', MyOptions, data1, num }, // Input/output array.
        { '$', MyOptions, data2, 1 },   // Input single struct.
    Jsi_DbOpts opts = {.ptrs=1};
    n = Jsi_DbQuery(jdb, NULL /*multi-bind*/, binds, 0, query, &opts);

mydata.max = -1;
n = My_Bind(mdPtr, num, mydata, "SELECT %s FROM mytable WHERE rowid=:rowid AND max=$max");

This causes a single bind of $max to mydata, then repeated array-binds to :rowid, allowing us to avoid input/output data collisions.

Spec Fields

A spec is an array used to describe all or part of an struct using options.

Supported Types

For database access, spec option types are limited to:

BOOLintBoolean (uses a "char" variable).
INTintAn integer.
WIDEJsi_WideA 64-bit integer (Jsi_Wide).
DOUBLEJsi_NumberDouble floating point.
DSTRINGJsi_DStringA Jsi_DString value.
STRKEYchar*A char* string key.
STRBUFchar[]A fixed size char string buffer.
STRINGJsi_StringA Jsi_Value referring to a string (when not using JSI_LITE_ONLY)
DATETIMEJsi_WideA date variable, milliseconds since 1970 stored in a 64 bit integer.
CUSTOMCustom types, including Enum and Bitmap.


The custom type supports defining parametrized handlers using the .custom and .data fields. Several predefined handlers are available:

Custom Enum

It is a common database practice to store numeric values in one table, and then use a foreign key to reference the string value from another table. This is certainly a useful abstraction, but it does add complexity to the schema. It also typically results in overhead from the use of joins, views and sub-selects.

The alternative is to store the string in the table. But this requires conversion code when we wish to use an enum in C.

Now consider the marks field from above:

typedef enum { MARK_NONE, MARK_A, MARK_B, MARK_C, MARK_D, MARK_F } MarkType;
static const char *markStrs[] = {"","A","B","C","D","F",NULL};
    JSI_OPT(CUSTOM,     MyData, mark,   .help="Marks", .custom=Jsi_Opt_SwitchEnum, .data=markStrs ),

The definition ensures that the marks value is stored as integer in memory, and as string in the database: No manual conversion is required.

The JSI_OPT_NOCASE flag can be used ignore case in the database string value.

Custom Bitset

The Jsi_Opt_SwitchBitset option provides access multiple bits in one integer field. This works similar to the above enum, except that the C stored values are bits set in an integer:

JSI_OPT(CUSTOM,     MyData, markSet,   .help="Marks set", .custom=Jsi_Opt_SwitchBitset, .data=markStrs ),

But in the database they are stored as a list of string attributes:

# SELECT markSet FROM mytable;
"A B"
"B C D"

Like enum, Jsi automatically provides the translation to/from strings.

The JSI_OPT_NOCASE flag can be used ignore case in the database string value.

Field/Column Mapping

The .extName field is used to map a C field name to a different database column name:

JSI_OPT(STRBUF,     MyData, name,   .extName="struct" ),

ie. in this example, "struct" is a reserved word in C.

NULL Values

For SELECT, the following Sqlite C-API rules apply for NULL sqlite_column values:

Dirty Field

A dirty field is used to limit which rows get stored, which is substantially faster than updating every row in the table. It is either a BOOL or INT field using the dirty_only option flag.

JSI_OPT(BOOL,       MyData, isdirty, .help="Dirty flag: not stored in db", .flags=JSI_OPT_DB_DIRTY),

The call must be made with the dirty_only flag:

for (i=1; i<=3; i++) {
    mydatas[i].isdirty = 1;
    mydatas[i].id += 100*i;
Jsi_DbOpts opts = {.dirty_only=1};
n = QueryMyStat(mydatas, cnt, "UPDATE mytable SET %s WHERE rowid = :rowid", &opts);

Note: unless an error occurs, dirty fields are cleared by the call.

Rowid Field

A field for storing the rowid is indicated by a JSI_OPT_DB_ROWID option flag.

JSI_OPT(WIDE,       MyData, rowid,  .help="DB rowid for update/insert; not stored in db", .flags=JSI_OPT_DB_ROWID),

The field will not be stored back to the database, but will be loaded during a SELECT, for use in query bindings to enforce a 1-1 mapping with the database, eg.

QueryMyStat(mydatas, cnt, "UPDATE mytable SET %s WHERE rowid == :rowid", 0);


Javascript is available when "jsi.c" is not compiled with JSI_LITE_ONLY, and the appropriate initialization used.

Javascript Initialization

The following shows how to initialize Jsi to have full access to Javascript and the database scripting API.

Jsi_EvalString(interp, "var mydb = new Sqlite('~/mytest.db');", 0);
Jsi_Db *jdb = Jsi_UserObjDataFromVar(interp, "mydb");
sqlite3 *db = Jsi_DbHandle(interp, jdb);

The "CData" Command

Javascript can access data arrays created in C-code using the "CData" command.

The first step is to use Jsi_CDataRegister() making mdPtr available as "mydata".

Jsi_DbOpts opts = {.ptrs=1};
Jsi_CDataRegister(interp, "mydata", MyOptions, mdPtr, num, &opts);

Then we can index data array elements from Javascript with:

CData.get( 'mydata', 0, 'max' ); 
CData.set( 'mydata', 1, {'max':99} );

Queries With "CData"

Javascript queries can use CData targets to load and store data:

db = new Sqlite('~/mytable.db');

size = db.query('SELECT %s FROM mytable', {CData:'mydata'});
for (i = 0; i<size; i++) {
    max = CData.get('mydata', i, 'max');
    max += i*100;
    CData.set('mydata', i, {max:max});
db.query('UPDATE %s FROM mytable', {CData:'mydata'});

CData.get('mydata', 0);
CData.size('mydata');      // Get array allocated size.
CData.info('mydata');      // Struct info.

Schema Generation

The Javascript command CData.schema() returns the schema for a CData definition, for example to create a table:

db.query("CREATE TABLE newtable(" + CData.schema('mydata') + ")";

which can then be used to access data:

db.query('INSERT %s INTO newtable', {CData:'mydata'});
db.query('SELECT %s FROM newtable', {CData:'mydata'});

The schema for the above would look something like:

CREATE TABLE newtable(
-- 'MyData': This is a struct for dbdemo
  name TEXT DEFAULT '' -- Fixed size char buf
 ,desc TEXT -- Description field of arbitrary length
 ,id INT DEFAULT 0 CHECK(id>0) -- Int id
 ,max FLOAT -- Max value
 ,myTime TIMEINT DEFAULT(round((julianday('now') - 2440587.5)*86400.0)) -- A unix/javascript time field in milliseconds (64 bits)
 ,mark TEXT -- Marks
 ,markSet TEXT -- A set
-- MD5: bc2a7cfc68725e60396dd2a2a4113f75

The schema is generated by appending:

The schema is completed using fields from JSI_OPT_END:

Schema Checking

One advantage of using generated schemas is that it provides a way to deal with data changes.

The calculated MD5 value (which ignores .help data) can be used in tracking database compatibility as in the following example:

var md5 = CData.schemaMd5('mydata')
var schema = db.onecolumn("SELECT sql FROM sqlite_master WHERE name='newtable' AND type=='table'");
if (!schema.match('MD5: '+md5))
    FixUpData('mydata','newtable'); // Fixup function: eg. export, recreate table and import data.


Option configuration is available thus:

var mydb = new Sqlite('~/mytest.db', {maxStmts:100});


Performance of Jsi_DbQuery() should be similar to that of hand generated C-code. Heap memory requests are avoided where possible. Except for Sqlite internal memory requests, heap allocation occurs only when:

  1. A JSI_DString field exceeds 200 bytes.
  2. A query string exceeds 2000 bytes (including generated argument lists).
  3. A query is first added to the cache.
  4. ptr* flags are used.

Some overhead arises from using sqlite3_bind_parameter_name(), and string concatenation which is used in binding list creation. However, being stack orientated together with caching compiled queries gives reasonably good performance.

Following is output from the million row benchmark included in the dbdemo program:

user@host:~/src/jsi/jsi/c-demos$ ./dbdemo -benchmark
INIT C:    0.198 secs
   6.704 sec,   149164 rows/sec    INSERT 1000000 ROWS
   2.883 sec,   346860 rows/sec    SELECT 1000000 ROWS 
  10.029 sec,    99710 rows/sec    UPDATE 1000000 ROWS, 1 FIELD
  10.754 sec,    92988 rows/sec    UPDATE 1000000 ROWS, ALL FIELDS
   4.381 sec,    22825 rows/sec    UPDATE 100000 DIRTY ROWS
   1.412 sec,      708 rows/sec    UPDATE 1000 DIRTY ROWS
   0.272 sec,       36 rows/sec    UPDATE 10 DIRTY ROWS

The last 3 use dirty row updates to demonstrate keeping a database in sync with minimal overhead.



Normally, any write query with size greater than one will implicilty add BEGIN/COMMITs. The JSI_DB_NO_BEGINCOMMIT flag can be used to disable this behaviour.

Also, the "BEGIN" or "COMMIT" string can be overridden at compile time like so:

#include "jsi.c"

Error Handling

A custom error handler can be invoked upon error using:

#include "jsi.c"

int MyErrHandler(Jsi_Db *jdb, Jsi_OptionSpec *specs, void *data, int numData, const char *query, int flags, int rc) {
   printf("Error in query: %s\n", query);
   return rc;


The following related references are available: