Readable schema creation in SQL/Knex/Bookshelf

[madbernard]

SQL: the godzilla of database languages! Knex/Bookshelf: a SQL query builder and a JavaScript ORM that works with it, built by Tim Griesser, supported through the issue tracker for each project at https://github.com/tgriesser/ . The Knex/Bookshelf pair make setting up and querying most of the SQL flavors of databases (Postgres, MySQL, MariaDB, SQLite3, and Oracle) more simple and readable, but I'm going to show the table creation mechanism below that I think is the best combination of the two:

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : 'localhost',
    user: 'the_user',
    password: 'the_password',
    database: 'the_db-name',
    charset: 'UTF8'
  }
});

var db = require('bookshelf')(knex);

db.knex.schema.hasTable('users').then(function(exists) {
  if (!exists) {
    db.knex.schema.createTable('users', function (users) {
      users.increments('id').primary();
      users.timestamps();
      users.string('username', 255);
      users.text('artist_info'); // maximum length is 64 K
      users.specificType('userimage', 'mediumblob'); // mediumblob is for binaries up to 16 M
      users.date('banned_until'); // YYYY-MM-DD
      users.boolean('over17');
    })
    .then(function (table) {
      console.log('Created Table', table);
    });
  }
});

db.knex.schema.hasTable('tags').then(function(exists) {
  if (!exists) {
    db.knex.schema.createTable('tags', function (tags) {
      tags.increments('id').primary();
      tags.timestamps();
      tags.string('tagname', 255);
      tags.integer('user_id').unsigned().references('users.id'); // user who created this tag
    })
    .then(function (table) {
      console.log('Created Table', table);
    });
  }
});

module.exports = db;

Let me run through this: the require knex chunk has a good density of data. It sets up the connection pool, tells knex what flavor of database you will be working with, where and how you will connect to what specifically-named database, and what character set your stuff is encoded in. Be sure to use exactly 'UTF8', capitalization and all (unless you're encoding Chinese or some non-UTF8 thing, of course)!

With the var db bit, we set up bookshelf to work with that knex pool. But now, the nice bit: right here in this database config file we can lay out what the database looks like, so any future coder can see it and not be baffled!

A couple of specific notes: "users.timestamps()" sets things up to automatically, if you verify this in the Bookshelf model below (aka, the "hasTimestamps: true"), make columns 'created_at' and 'updated_at' for you.

The foreign key example in the tags schema has some extra trimmings: ".unsigned()" to indicate that the column will be full of only positive (no - sign) integers, which ensures its data type doesn't clash with the primary key data type from "users.increments('id').primary();". As for where it's getting 'user_id':

Naming: Bookshelf is similar to Backbone.js, and it works better when you follow the naming pattern above: 'users' and then in another file you'll have the model for each table row, which is named 'User', and then when you set up foreign keys (references to other tables) it will automatically come up with the singular of the table name appended with 'id', and all will be coordinated (assuming you also stuck with 'id' for the primary key of each table).

var db = require('../config');
var Tag = require('./tag');

var User = db.Model.extend({
  tableName: 'users',
  hasTimestamps: true,
  defaults: {
    banned_until: '1000-01-01', // earliest allowable MySQL date
    over17: true
  },
  tags: function() {
    return this.hasMany(Tag);
  },
  initialize: function(){
    this.on('creating', function(model, attrs, options){
      // fill in various table cells with the data that you're getting, 
      // manipulated by functions here
    });
  }
});

module.exports = User;

Here's the other side of the foreign key model setup:

var db = require('../config');
var User = require('./user');

var Tag = db.Model.extend({
  tableName: 'tags',
  hasTimestamps: true,
  user: function() {
    return this.belongsTo(User);
  }
});

module.exports = Tag;

The Backbone flavor really comes through in the last bit of the setup (the tags collection is just like this, so I'm saving the screen space by not including it):

var db = require('../config');
var User = require('../models/user');

var Users = new db.Collection();

Users.model = User;

module.exports = Users;

And then you bring it all to where the webserver can use it:

var db = require('./db/config');
var Users = require('./db/collections/users');
var User = require('./db/models/user');
var Tags = require('./db/collections/tags');
var Tag = require('./db/models/tag');

Et voila! When the db require brings it in, it finds the localhost MySQL, logs in to 'the_db-name', looks for tables, and creates them if necessary.

Comments

(heart) bookshelf

[pfooti]

I use bookshelf and knex in my daily work stuff. I ended up rolling my own relationships code on top of bookshelf, because I have some arcane hasMany queries and bookshelf (at the time at least) only really supports straightforward joins.

Another nice thing is that you can interop es6 classes with the bookshelf.extend notation:

class Model extends bookshelf.Model {
  constructor(...args) {
    super(...args);
  }
}

class Profile extends Model {
  constructor(...args) {
    super(...args);
  }
}

you just have to do that funny call of super in the constructor to link together the constructor calls manually (which is, IIRC, not necessary in plain-old es6 classes).

But I do love knex in particular - chaining together query syntax in javascript means I can do some interesting tricks with creating queries, and if I can't figure out the actual knex.js syntax, I can always do a raw sql call and get help with bound variables.

Also, hi Mad! It's EricE!

[madbernard]

Niiice! Hi Eric! It's always cool to see that stuff I know lives in the work world as well! Also I may ask you Bookshelf/Knex stuff now if I run into problems. :D