SQLite is a lightweight, schema-based relational database engine. It is a popular choice as embedded database software for local storage in web browsers.

Unlike many other database management systems, SQLite is not a client–server database engine. For more information read our SQLite Tutorial

In this chapter you will learn how to communicate with SQLite database using CoffeeScript.

Installation

The SQLite3 database can be integrated with CoffeeScript using node-sqlite3 module. This module works with Node.js v0.10.x, v0.12.x, v4.x, and v5.x. This module caters various functions to communicate with SQLite3 using CoffeeScript, in addition to this it also provides an Straightforward query and parameter binding interface, and an Query serialization API.

You can install the node-sqlite3 module using npm as shown below.

npm install sqlite3

To use sqlite3 module, you must first create a connection object that represents the database and this object will help you in executing all the SQL statements.

Connecting to Database

In order to connect to SQLite database first of all create its package by invoking the require() function of the node-sqlite3 module and pass the string sqlite3 as a parameter to it. Then connect to a database by passing the name of the database to sqlite3.Database() construct.

Following CoffeeScript code shows how to connect to an existing database. If database does not exist, then it will be created with the given name test.db, opened and finally the database object will be returned.

#Creating sqlite3 package
sqlite3 = require(''sqlite3'')

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Database opened successfully."

We can also supply :memory: to create an anonymous in-memory database and, an empty string to create anonymous disk-based database, instead of test.db. Save the above code in a file with name create_db.coffee and execute it as shown below. If the database is successfully created, then it will produce the following message −

c:> coffee create_db.coffee
Successfully connected

Creating a Table

You can create a table in SQLite database through CoffeeScript using the run() function. Pass the query to create a table to this function in String format.

The following CoffeeScript program will be used to create a table in previously test.db database −

#Creating sqlite3 package
sqlite3 = require(''sqlite3'')

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Successfully connected"

db.serialize ->
  db.run ''CREATE TABLE STUDENT (name TEXT, age INTEGER, city TEXT)''
  console.log "Table created successfully"  
  return
db.close()

The serialize() function sets the database in serialized mode. In this mode when ever a callback encounters, it will be called immediately. The queries in that callback are executes serially. Soon the function returns The database will be set to normal mode again. After completing the transaction we need to close the connection using close() function.

Save the above code in a file with name create_table.coffee and execute it as shown below. This will create a table named STUDENT in the database test.db displaying the following messages.

C:> coffee create_table.coffee
Successfully connected
Table created successfully

Inserting / Creating Data

You can insert data into SQLite database through CoffeeScript code by executing the insert statement. To do so we can use the prepare() function which prepares SQL statements.

It also accepts query with bind variables (?), values to these variables can be attached using run() function. You can insert multiple records using prepared statement, and after inserting all the records, you need to finalize the prepared statement using finalize() function.

The following CoffeeScript program shows how to insert records in the table named STUDENT created in previous example.

#Creating sqlite3 package
sqlite3 = require(''sqlite3'').verbose()

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Successfully connected"

db.serialize ->
  stmt = db.prepare(''INSERT INTO STUDENT VALUES (?,?,?)'')
  stmt.run ''Ram'',24,''Hyderabad''
  stmt.run ''Robert'',25,''Mumbai''
  stmt.run ''Rahim'',26,''Bangalore''
  stmt.finalize()
  console.log "Data inserted successfully"
  return
db.close()

Save the above code in a file with name insert_data.coffee and execute it as shown below. This will populate the table named STUDENT displaying the following messages.

C:> coffee insert_data.coffee
Successfully connected
Data inserted successfully

Reading / Retrieving Data

You can get the data from an SQLite table using the each() function. This function accepts an optional callback function which will be called on each row.

The following CoffeeScript program shows how we can fetch and display records from the table named STUDENT created in the previous example

#Creating sqlite3 package
sqlite3 = require(''sqlite3'').verbose()

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Successfully connected"

db.serialize ->
  console.log "The contents of the table STUDENT are ::"
  db.each ''SELECT rowid AS id, name,age,city FROM STUDENT'', (err, row) ->
    console.log row.id + '': '' +row.name+'', ''+ row.age+'', ''+ row.city
    return
  return
db.close()

Save the above code in a file with name retrive_data.coffee and execute it as shown below. This retrieves all the records in the table named STUDENT and displays on the console as follows.

C:> coffee retrive_data.coffee
Successfully connected
The contents of the table STUDENT are ::
1: Ram, 24, Hyderabad
2: Robert, 25, Mumbai
3: Rahim, 26, Bangalore

Updating Data

The following CoffeeScript code shows how we can use UPDATE statement to update any record and then fetch and display updated records in the table named STUDENT

#Creating sqlite3 package
sqlite3 = require(''sqlite3'').verbose()

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Successfully connected"

db.serialize ->
  #Updating data
  stmt = db.prepare(''UPDATE STUDENT SET city = ? where name = ?'')
  stmt.run ''Delhi'',''Ram''
  console.log "Table updated"
  stmt.finalize()

  #Retrieving data after update operation
  console.log "The contents of the table STUDENT after update operation are ::"
  db.each ''SELECT rowid AS id, name, city FROM STUDENT'', (err, row) ->
    console.log row.id + '': '' +row.name+'', ''+ row.city
    return
  return
db.close()

Save the above code in a file with name update_data.coffee and execute it as shown below. This updates the city of the student named Ram and displays all the records in the table after update operation as follows.

C:> coffee update_data.coffee
Successfully connected
Table updated
The contents of the table STUDENT after update operation are ::
1: Ram, Delhi
2: Robert, Mumbai
3: Rahim, Bangalore

Deleting Data

The following CoffeeScript code shows how we can use DELETE statement to delete any record and then fetch and display remaining records from the table named STUDENT.

#Creating sqlite3 package
sqlite3 = require(''sqlite3'').verbose()

#Creating a Database instance
db = new (sqlite3.Database)(''test.db'')
console.log "Successfully connected"

db.serialize ->
  #Deleting data
  stmt = db.prepare(''DELETE FROM STUDENT WHERE name = ?'')
  stmt.run ''Ram''
  console.log "Record deleted"
  stmt.finalize()

  #Retrieving data after delete operation
  console.log "The contents of the table STUDENT after delete operation are ::"
  db.each ''SELECT rowid AS id, name, city FROM STUDENT'', (err, row) ->
    console.log row.id + '': '' +row.name+'', ''+ row.city
    return
  return
db.close()

Save the above code in a file with name delete_data.coffee and execute it as shown below. It deletes the record of the student named Ram and displays all the remaining in the table after delete operation as follows.

Successfully connected
Record deleted
The contents of the table STUDENT after delete operation are ::
2: Robert, Mumbai
3: Rahim, Bangalore