Okapi is better then an ORM, it isn't one!


Okapi is not an ORM, it is better!

Okapi aims at making SQL easier to use within Node.JS but isn't an ORM.


  • Doesn't do anything magical, it simply makes it easier to write SQL statements
  • Provides a single interface for Postgres, MySQL and SQLite
  • It is super easy to support other databases with Okapi, look at lib/pg.js, lib/mysql.js, lib/sqlite.js to see how to implement a dialect
  • Works with regular JavaScript Objects - it purposefully doesn't provide any magical relationship mapping
  • Attempts to make it easy to write SQL when needed
  • Designed to work with async
  • Has an integrated assertion framework
  • Is well unit tested (click on the build status above for details)

To get started with Okapi simply:


  npm install mysql


  npm install pg


  npm install sqlite3
  npm install okapi
var Okapi = require('okapi');
//For MySQL 
var dialect = new Okapi.MySQLDialect({  host:"localhost", user:"root", database:"dao" });
//For Postgres 
var dialect = new Okapi.PGSQLDialect({  host:"localhost", user:"postgres", database:"okapi", password:"" });
//For SQLite 
var dialect = new Okapi.SQLiteDialect(new sqlite.Database(":memory:"));

To use Okapi first we need to define the schema for the object we want to use:

  //Let's create a new person table, called 'person' 
  Person = new Okapi.Object(dialect,"person");
  //Now let's add some columns 
  Person.column("id",{type: Okapi.ID });
  Person.column("name",{type: Okapi.String, unique: true});
  Person.column("email",{type: Okapi.String });
  Profile = new Okapi.Object(dialect,"profile");
  //This column refers to the id column defined by Person above 
  Profile.column("userId",{type:Okapi.IDRef, ref: { dao: Person, column: "id" }});
  Person.insert({ name:"Bob", email:"bob@bob.com"}).done(function(err,insertedPerson){
    console.log("I inserted this person",insertedPerson);
    Profile.insert({ userId: insertedPerson.id, gender:"male"}).done(function(err,profile){
        console.log("I found these people",results);
  • Okapi.ID - denotes that the column is the primary id for the table
  • Okapu.IDRef - denotes a column that references another id in another table (may be null)
  • Okapi.String - a string column (short in length)
  • Okapi.Text - a text column (long in length)
  • Okapi.Number - a number column
  • Okapi.Float - a floating point column
  • Okapi.Boolean - a boolean column (1 or 0)
  • Okapi.Date - a date column
  • type - one of the Okapi types defined above
  • pk (boolean) - this column is a primary key
  • unique (boolean) - this column is unique
  • notNull (boolean) - this column may not be null
  • default - the default value for this column
  • ref - the information associated with this reference
  • ref.dao - the day this reference referes to
  • ref.column - the column in the refered dao that joins to this one

Okapi supports single and multi key indexes:

  //And create an index on name 
  //Create a unique index on name and email 
  Person.index("name_email",["name","email"], { unique: true });

Each statement within Okapi is chainable, and is designed to be utilized with the standard callback method for node or with the popular async npm module.

Here is now it would look if your not using async and works the same way all the other callbacks in NodeJS work.

  //This will return all people in the database 
    //Returns an array of people 
  // .last() and .each() are also available 
    //Returns the first result 
  //This will return the first 5 people 
  Person.insert({ name: "Bob"}).done(function(err,result){
    //Returns the person object with the id of it in the object 
  Person.update({ id: 1, name: "Bob"}).done(function(err,result){
    //Returns the number of changed rows 
  Person.update({ email:"email@email.com"}).where({name:"Bob"}).done(function(err,result){
    //Returns the number of rows 
  Person.delete({ id:1 }).done(function(err,result){

To make it easier to use with the async npm module you can alternatively return a call back so that statements can be executed easily in series by using the .async() method. This method will return a callback that works well with async.

    //Each one of these statements will be called in sequence 
    Person.insert({ name: "Bob"}).async(),
    Person.update({ id: 1, name: "Sally"}).async(),
    Person.delete({ id: 1 }).async()
    //This will be called when were done! 

Lastly you can also use Okapis native assertion framework, which was designed to work well with async. Too see how to use the assertion framework in detail checkout the /test directory.

    //Each one of these statements will be called in sequence and tested 
    // against the assertions 
    Person.insert({ name: "Bob"}).assert("A user was created",function(a){
      a.contains({ name: "Bob", id: 1 });
    Person.find().assert("The person we just created is in the database",function(a){
      a.containsRow({ name: "Bob", id: 1 });
    //This will be called when were done! 

First you can simply use SQL via your dialect, simply put '?' around the variables you want to substitute in the SQL statement, and then include the data in the second argument.

The third argument tells Okapi to to prepare the data, simply specify 'select','update','insert' here.

  dialect.sqlQuery("select * from person where name=?name?",{name:"bob"},"select",function(err,results){
  person.sqlQuery("select average(height) from person",{},function(err,result){
  //You can also specify different SQL to use for different databases: 
  dialect.sqlQuery({ sqlite: "select date('now')", mysql: "select now()", pg: "select now" },...);

Okapi crafts SQL statements by using a small template language, if you check out lib/dialect.js you'll see this list of templates.

So for example the update statement looks like so:

    update <%tableName()%> set <%sets()%> <%setExp()%> <? where <%where()%> <%whereExp()%> ?>

Okapi uses this statement to generate the update call and it can be overriden by the various database dialects (see lib/sqlite.js for an example), but it also makes it easy to customize a statement in a way that is exteremly flexible. Anywhere within the SQL templates where something ends in 'Exp' such as whereExp() raw SQL can be inserted here.

So for example, would insert the SQL statement below in the update.

  // update person set namesdx=soundex(name) 

While this is nice you can also use it to support multiple different databases at once, in the example below we've inserted custom SQL for each one of the different databases we want to support.

                            mysql:",TIMESTAMPDIFF(YEAR,birthdate,now()) as age",
                            pg:",date_part('year',age(now(),birthdate)) as age",
                            sqlite:",(?now?-birthdate)/(1000*3600*24*365) as age",
                          },{ now: Date.now() }

Anywhere a where block is specified a query can be specified as:

  • An object which implys that each column must match the suplied value
  • A lambda which can be used to construct a complex query
  • A string which will be matched against the primary key column
  // name="bob" and email="bob@bob.com" 
  Person.find().where({ name:"bob", email:"bob@bob.com"})...
  // name="bob" or email="bob@bob.com" 
  Person.update({ name: "Elvis"}).where(function(q){
        q.like("name","The King");
  // id = 1 

Okapi provides two functions for creating and deleting tables:

  //We can drop and create a table like so: 
  //Or like so: 
    //Or like so: 

Create Table template:

    create table if not exists <%tableName()%> (
      <% eachColumn(function(columnName,column){ return '\\t'+columnName+' '+columnType(columnName)+' '+columnModifiers(columnName); },',\\n')%>
      <?,\n<%eachColumn(function(name,column){ var c = columnUniqueConstraint(name); if(c) return '\\t'+c; },',\\n')%>?>
      <?,\n<%eachColumn(function(name,column){ var c = columnFKConstraint(name); if(c) return '\\t'+c; },',\\n')%>?>

This makes it easy to deal with MySQL specific table types!


To insert data simply provide a javascript object with variables matching the names of the columns specified in the table definition. A new object with the id associated with the object will be returned

  Person.insert({name: "bob", email:"bob@bob.com").done(function(err,res){
    console.log("The person was inserted!",res);

Insert template:

  insert into <%tableName()%> (<%columns()%> <%columnExp()%>) values(<%values()%> <%valueExp()%>)

The update function will use the provided data to update the rows selected via the where query

  //This will update the suplied data by using the primary key for the object, in this case it will use 'id' 
  // update person set name='bob' where id=3 
  Person.update({ name:"Bob", id:3 }).done(...)
  //You can also use a 'where' query to specify what to update 
  // update person set name='bob' where email='bob@bob.com'  
  Person.update({name:"bob"}).where({email: 'bob@bob.com'}).done(err,res){

Update template:

    update <%tableName()%> set <%sets()%> <%setExp()%> <? where <%where()%> <%whereExp()%> ?>

Upserts are essentially statements that insert or update the row depending upon a primary key conflict. Essentially create or update a row, which is more efficient then doing and individual insert and update.

  Person.upsert({ id: 1, name:"Bob"}).done(....);

Upsert templates:

  insert into <%tableName()%> (<%columns()%> <%columnExp()%>) values(<%values()%> <%valueExp()%>) on duplicate key update <%sets({ noPK: true })%> <%setExp()%>
  insert or replace into <%tableName()%> (<%columns()%> <%columnExp()%>) values(<%values()%> <%valueExp()%>)
  //pg - update followed by insert
  //Delete everything! 
  // delete from person 
  //Delete only some people 
  // delete from person where name='bob' 

Delete template:

    delete from <%tableName()%> <? where (<%where()%><%whereExp()%>) ?>
  //Return everything that has a name of celer 
  //Return the item with an primary key of 1 (id=1) 
  //Find an individual using a more advanced query 
  //This will perform a find and only return the specified columns 
  //This will join across user and profile 
  //Or we could join from user: 
  //Get the first page of data 
  //Order the results   

Select template:

    select <%columns()%><%columnExp()%> from <%tableName()%> <%joins()%> <? where <%where()%><%whereExp()%>?><?order by <%orderBy()%> <%orderByExp()%>?><?limit <%limit()%> ?> <? offset <%offset()%> ?>

Okapi doesn't have an explicit understanding of relationships, hence why it is not an ORM! Instead it lets you join things:

  //If it can figure out how to join things it will just do it (as an inner join) 
  //You can give it more details if it gets confused, the second paramter after the DAO being the column to use.  
  //You can tell also give it a query to use for your join: 
  //You can even tell it what type of join to use: 
  Person.find().join(Profile,"userId",null,{ type:"left"}).done(...)
  //Or you can even tell it what columns to return: 
  Person.find().join(Profile,"userId",null,{ columns:["age","gender"]}).done(...)
  //And suppose you have multiple joins on the same table: 
  Vehicle.find().join(Person,"ownerId", null,{ type: "left, as:"owner"}).join(Person,"driverId",null,{type:"left",as:"driver"}).done(...)
    //Or you want to do a join across linked tables
  Vehicle.find().join("ownerID",person,{name:"b"},{ as:"owner"},profile,{as:"ownerProfile"}).join("driverID",person,{as:"driver"},profile, { as: "driverProfile"}).done(...)

The join function expects a sequence like so:


Where the only required parameter is the DAO, and everything else is optional - Okapi will figure out the rest if it can. With the first DAO being implied by what it was chained upon, so for example:

    Vehicle.find().join("ownerID",person,{name:"b"},{ as:"owner"},profile,{as:"ownerProfile"})

is interpreted as:

    //inner join Vehicle.ownerId to 
    //inner join person (Okapi will resolve the correct id column) where name=="B" as "owner" to  
    //inner join profile (Okapi will resolve the correct id column) as "ownerProfile" 

The options have two possible values:

  • options
  • as - what to name the resulting object
  • type - the type of join to do (left, inner, etc)

Joins may also use filtered DAOs as described below

      var v = tx.use(vehicle);  
        v.insert({ make:"make", model:"model"}).async(),
        v.update({ make:"make2", id: 0}).async(),
        } else {

So to make it easy for you to filter a DAO, for example to restrict a view you can simply clone an existing DAO an apply a filter, and these filtered DAOs will even retain their filtering in joins!

  var vehicle = new Okapi.Object(dialect,"vehicle");
  vehicle.column("id",{type: Okapi.ID });
  vehicle.column("make",{ type:Okapi.String });
  vehicle.column("model",{ type:Okapi.String });
    We will clone the object and make a new one
    that is limited to dealing only with mazda
  var mazdaOnly = vehicle.clone();
    mazdaOnly.find().assert("Only contains mazda",function(q){
        q.containsRow({ make:"Mazda", model:"Miata"});

For the most part Okapi constructs the statement or query at the time it is executed, but is possible to ask it to pre-construct a query that you want to run a bunch, allowing you to overwrite variables as needed.

    //This will create a prepared insert statement with a variable 'year' that can be overridden as needed: 
    var insert = vehicle.insert({ make:"mazda", model:"Miata",year:Okapi.$("year") }).prepare();
    insert.exec({year:1997},function(err,result){  ... }), 
    var find = vehicle.find(function(q){
    find.exec({ make:"mazda"},function(err,result){ ... }),

The exec statements will return an async function if no call back is provided for use with the async module.

We don't expect Okapi to solve every SQL problem, hence why we endorse dropping to SQL when you need it.