CoffeeScript – SQLite ”; Previous Next 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