Sign Up for Free

RunKit +

Try any Node.js package right in your browser

This is a playground to test code. It runs a full Node.js environment and already has all of npm’s 1,000,000+ packages pre-installed, including icql with all npm packages installed. Try it out:

var icql = require("icql")

This service is provided by RunKit and is not affiliated with npm, Inc or the package authors.

icql v5.1.4

YeSQL meets SQLite: A SQLite Adapter built with InterCourse and BetterSQLite

ICQL

YeSQL meets SQLite: A SQLite Adapter built with InterCourse and BetterSQLite

ICQL is a module written in the spirit of YeSQL. For those readers who are unaware of YeSQL, there is a short Intro to YeSQL; others may want to dive right into the sections on ICQL Installation and ICQL Usage.

ICQL is implemented on top of InterCourse which is an SQL-agnostic library that does the parsing and cataloguing of 'functionality hunks' (i.e. named blocks of code that define how to accomplish tasks).

ICQL takes three pieces: (1) a database adapter (which currently must be better-sqlite3 or something with a compatible API), (2) a path to an SQLite DB file, and (3) a path to an ICQL source file with statement definitions; it then binds together these three pieces to produce an object where the statement definitions have been turned into methods that perform queries against the DB.

ICQL Installation

npm install icql

ICQL Usage

Instantiation

ICQL is specifically geared towards using (1) the SQLite Relational DB by way of (2) the better-sqlite3 library for NodeJS. While it should be not too difficult to (fork and) adapt ICQL to work with other DB engines such as PostgreSQL, no concrete plans exist at the time of this writing. Understand that ICQL is still in its inceptive stage and, as such, may lack important features, contain bugs and experience breaking changes in the future.

FTTB all code examples below will be given in CoffeeScript. JavaScript users will have to mentally supply some parentheses and semicolons.

To use ICQL in your code, import the library and instantiate a db object:

ICQL = require 'icql'

settings = {
  connector:    require 'better-sqlite3'  # must give a `better-sqlite3`-compatible object
  db_path:      'path/to/my.sqlitedb'     # must indicate where your database file is / will be created
  icql_path:    'path/to/my.icql' }       # must indicate where your SQL statements file is

db = ICQL.bind settings                   # create an object with methods to query against your SQLite DB

Querying

After doing db = ICQL.bind settings the new db object contains all the methods you defined in your *icql file. Each method will be either a procedure or a query, the difference being that

  • procedures consists of any number of SQL statements that do not produce any output; these may be used to create, modify and drop tables and views, insert and delete data and so on; on the other hand,

  • queries consist of a single SQL select statement with any number of resulting records.

Here are two simple ICQL definitions:

procedure drop_tables:
  drop table if exists foo;
  drop table if exists bar;

query fetch_products( price_max ):
  select * from products where price <= $price_max;

query fetch_products( price_min, price_max ):
  select * from products where price between price_min and $price_max;

Owing to the synchronous nature of BetterSQLite, all procedures and queries are synchronous; that means you can simply write stuff like

db = ...
db.drop_tables()
db.create_table_bar()
db.populate_table_bar()

without promises / callbacks / whatever async. That's great (and works out fine because SQLite is a single-thread, in-process DB engine, so asynchronicity doesn't buy you anything within a single-threaded event-based VM like NodeJS).

Queries return an iterator, so you can use a for/of loop in JavaScript or a for/from loop in CoffeeScript to iterate over all results:

// JS
for ( row of db.fetch_products { price_max: 400, } ) {
  do_something_with( row ); }
# CS
for row from db.fetch_products { price_max: 400, }
  do_something_with row

Under the hood, the equivalent of the following is performed:

query_entry = db.$.sql.fetch_products[ 'price_max' ]
query_text  = query_entry.text
#............................................................
# In case of statements without results:
db.$.execute query_text
#............................................................
# In case of statements with results:
statement   = db.$.prepare query_text
iterator    = statement.iterate { price_max: 400, }
for row from iterator: ...

db.$, the 'Special' Attribute

The db object as constructed above will have a an attribute, db.$, called 'special', which in turn contains a number of members that are used internally and may be occasionally be useful for the user:

  • db.$.limit(), db.$.single_row(), db.$.first_row(), db.$.single_value(), db.$.first_value(), db.$.all_rows(), db.$.all_first_values() and db.$.first_values() are discussed in Query Modifiers, below.

  • db.$.load path—load an extension.

  • db.$.read path—execute SQL statements in a file.

  • db.$.prepare sql—prepare a statement. Returns a better-sqlite3 statement instance.

  • db.$.execute sql—execute any number of SQL statements.

  • db.$.query sql, P...—perform a single select statement. Returns an iterator over the result set's rows. When the sql text has placeholders, accepts additional values.

  • db.$.settings—the settings that the db object was instantiated with.

  • db.$.db—the underlying better-sqlite3 object that is used to implement all functionality.

  • db.$.sql—an object with metadata that describes the result of parsing the definition source file.

  • db.$.as_identifier text—format a string so that it can be used as an identifier in an SQL statement (even when it contains spaces or quotes).

  • db.$.catalog()—return an iterator over all entries in sqlite_master; allows to inspect the database for all tables, views, and indexes.

  • db.$.clear()—drop all tables, views and indexes from the database.

  • db.$.escape_text x—turn text x into an SQL string literal.

  • db.$.list_as_json x—turn list x into a JSON array literal.

  • db.$.as_sql x—express value x as SQL literal.

  • db.$.interpolate sql, Q—interpolate values found in object Q into string sql.

Writing ICQL Statements

TBW; see the demo and the InterCourse docs.

SQL Fragments

Possible to define fragments, i.e. possibly incomplete SQL snippets that may contain placeholders. For each fragment, a namesake method will be created that accepts an object with named values where applicable; when called, method does not execute a statement but returns that SQL snippet with values filled out as literals. Observe that the SQL interpolation routine differs a little from what better-sqlite3 offers; in particular, booleans true, false will be turned into integers 0, 1, and lists will be expressed as JSON array literals. In the future, we will try to align ICQL and Sqlite3 value interpolation and allow to define custom conversions.

Definition Types

  • procedure—does not return anything and may contain any number of SQL statements.

  • query—returns a JS iterator (to be used in a JS for/of or CS for/from loop). These can be used with any kind of select statement (trivially including those statements that return no rows at all).

RunKit is a free, in-browser JavaScript dev environment for prototyping Node.js code, with every npm package installed. Sign up to share your code.
Sign Up for Free