Create sqlite db using bookshelf.js

Question

How to create a new sqlite3 database file using bookshelf.js? If db file exists, connect to it; If db file doesn't exists, create a new db file.

Thanks

Answer 1

Under the hood, bookshelf uses knex (which in the current version you have to explicitly use to instantiate).

If you set the client to be sqlite3 in the options, and you specify a valid path in the connection object (property filename) then it will connect or create as required (caveat: assuming you have read/write to the file destination).

The reason a database file isn't just created is that you have to do a write first - so try creating a schema (knex schema builder). Then you should see the file is created.

To be a little more explicit - here's kind of what you are looking for:

var path = require('path')
    , fs = require('fs')
    , knex = require('knex')
    , bookshelf = require('bookshelf')
    , dbFile = path.join(__dirname, 'app.db')
    , db = null // bookshelf db instance

// init db
db = bookshelf(knex({
    client: 'sqlite3'
    , connection: { filename: dbFile }
}))

// create a schema if no db found
fs.exists(dbFile, function(exists) {
    if (!exists) {
        db.knex.schema.createTable('test_table', function(table) {
            table.increments()
            table.string('some_col')
        })
    }
})

// ... do other stuff here ...