CoffeeScript SQLite

Sqlite

Problem

You need .js interface to the SQLite database internally.

Solution

Use the SQLite module.

sqlite = require 'sqlite'

db = new sqlite.Database

# The module uses asynchronous methods,
# so we chain the calls the db.execute
exampleCreate = ->
    db.execute "CREATE TABLE snacks (name TEXT(25), flavor TEXT(25))",
        (exeErr, rows) ->
            throw exeErr if exeErr
            exampleInsert()

exampleInsert = ->
    db.execute "INSERT INTO snacks (name, flavor) VALUES ($name, $flavor)",
        { $name: "Potato Chips", $flavor: "BBQ" },
        (exeErr, rows) ->
            throw exeErr if exeErr
            exampleSelect()

exampleSelect = ->
    db.execute "SELECT name, flavor FROM snacks",
        (exeErr, rows) ->
            throw exeErr if exeErr
            console.log rows[0] # => { name: 'Potato Chips', flavor: 'BBQ' }

# :memory: creates a DB in RAM
# You can supply a filepath (like './example.sqlite') to create/open one on disk
db.open ":memory:", (openErr) ->
    throw openErr if openErr
    exampleCreate()

Discuss

You can also prepare your SQL query statements in advance.

sqlite = require 'sqlite'
async = require 'async' # Not required but added to make the example more concise

db = new sqlite.Database

createSQL = "CREATE TABLE drinks (name TEXT(25), price NUM)"

insertSQL = "INSERT INTO drinks (name, price) VALUES (?, ?)"

selectSQL = "SELECT name, price FROM drinks WHERE price < ?"

create = (onFinish) ->
    db.execute createSQL, (exeErr) ->
        throw exeErr if exeErr
        onFinish()

prepareInsert = (name, price, onFinish) ->
    db.prepare insertSQL, (prepErr, statement) ->
        statement.bindArray [name, price], (bindErr) ->
            statement.fetchAll (fetchErr, rows) -> # Called so that it executes the insert
                onFinish()

prepareSelect = (onFinish) ->
    db.prepare selectSQL, (prepErr, statement) ->
        statement.bindArray [1.00], (bindErr) ->
            statement.fetchAll (fetchErr, rows) ->
                console.log rows[0] # => { name: "Mia's Root Beer", price: 0.75 }
                onFinish()

db.open ":memory:", (openErr) ->
    async.series([
        (onFinish) -> create onFinish,
        (onFinish) -> prepareInsert "LunaSqueeze", 7.95, onFinish,
        (onFinish) -> prepareInsert "Viking Sparkling Grog", 4.00, onFinish,
        (onFinish) -> prepareInsert "Mia's Root Beer", 0.75, onFinish,
        (onFinish) -> prepareSelect onFinish
    ])

Sql's version of SQLite and the node-SQLite module documentation provide more complete information.