Database integration

Adding the capability to connect databases to Express apps is just a matter of loading an appropriate Node.js driver for the database in your app. This document briefly explains how to add and use some of the most popular Node.js modules for database systems in your Express app:

Cassandra
Couchbase
CouchDB
LevelDB
MySQL
MongoDB
Neo4j
Oracle
PostgreSQL
Redis
SQL Server
SQLite
Elasticsearch

Caution

These database drivers are among many that are available. For other options, search on the npm site.

Cassandra

Module: cassandra-driver

Installation

Terminal window

npminstallcassandra-driver

Example

index.cjs

constcassandra=require('cassandra-driver');
constclient=new cassandra.Client({ contactPoints: ['localhost'] });
client.execute('select key from system.local', (err, result) => {
if (err) throw err;
console.log(result.rows[0]);
});

index.mjs

import cassandra from'cassandra-driver';
constclient=new cassandra.Client({ contactPoints: ['localhost'] });
client.execute('select key from system.local', (err, result) => {
if (err) throw err;
console.log(result.rows[0]);
});

Couchbase

Module: couchnode

Installation

Terminal window

npminstallcouchbase

Example

index.cjs

constcouchbase=require('couchbase');
constbucket=new couchbase.Cluster('http://localhost:8091').openBucket('bucketName');
// add a document to a bucket
bucket.insert('document-key', { name: 'Matt', shoeSize: 13 }, (err, result) => {
if (err) {
console.log(err);
} else {
console.log(result);
}
});
// get all documents with shoe size 13
constn1ql='SELECT d.* FROM `bucketName` d WHERE shoeSize = 1ドル';
constquery= N1qlQuery.fromString(n1ql);
bucket.query(query, [13], (err, result) => {
if (err) {
console.log(err);
} else {
console.log(result);
}
});

index.mjs

import couchbase from'couchbase';
constbucket=new couchbase.Cluster('http://localhost:8091').openBucket('bucketName');
// add a document to a bucket
bucket.insert('document-key', { name: 'Matt', shoeSize: 13 }, (err, result) => {
if (err) {
console.log(err);
} else {
console.log(result);
}
});
// get all documents with shoe size 13
constn1ql='SELECT d.* FROM `bucketName` d WHERE shoeSize = 1ドル';
constquery= N1qlQuery.fromString(n1ql);
bucket.query(query, [13], (err, result) => {
if (err) {
console.log(err);
} else {
console.log(result);
}
});

CouchDB

Module: nano

Installation

Terminal window

npminstallnano

Example

index.js

constnano=require('nano')('http://localhost:5984');
nano.db.create('books');
constbooks= nano.db.use('books');
// Insert a book document in the books database
books.insert({ name: 'The Art of war' }, null, (err, body) => {
if (err) {
console.log(err);
} else {
console.log(body);
}
});
// Get a list of all books
books.list((err, body) => {
if (err) {
console.log(err);
} else {
console.log(body.rows);
}
});

LevelDB

Module: levelup

Installation

Terminal window

npminstalllevellevelupleveldown

Example

index.cjs

constlevelup=require('levelup');
constdb=levelup('./mydb');
db.put('name', 'LevelUP', (err) => {
if (err) return console.log('Ooops!', err);
db.get('name', (err, value) => {
if (err) return console.log('Ooops!', err);
console.log(`name=${value}`);
});
});

index.mjs

import levelup from'levelup';
constdb=levelup('./mydb');
db.put('name', 'LevelUP', (err) => {
if (err) return console.log('Ooops!', err);
db.get('name', (err, value) => {
if (err) return console.log('Ooops!', err);
console.log(`name=${value}`);
});
});

MySQL

Module: mysql

Installation

Terminal window

npminstallmysql

Example

index.cjs

constmysql=require('mysql');
constconnection= mysql.createConnection({
host: 'localhost',
user: 'dbuser',
password: 's3kreee7',
database: 'my_db',
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', (err, rows, fields) => {
if (err) throw err;
console.log('The solution is: ', rows[0].solution);
});
connection.end();

index.mjs

import mysql from'mysql';
constconnection= mysql.createConnection({
host: 'localhost',
user: 'dbuser',
password: 's3kreee7',
database: 'my_db',
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', (err, rows, fields) => {
if (err) throw err;
console.log('The solution is: ', rows[0].solution);
});
connection.end();

MongoDB

Module: mongodb

Installation

Terminal window

npminstallmongodb

Example (v2.*)

index.js

constMongoClient=require('mongodb').MongoClient;
MongoClient.connect('mongodb://localhost:27017/animals', (err, db) => {
if (err) throw err;
db.collection('mammals')
.find()
.toArray((err, result) => {
if (err) throw err;
console.log(result);
});
});

Example (v3.*)

index.js

constMongoClient=require('mongodb').MongoClient;
MongoClient.connect('mongodb://localhost:27017/animals', (err, client) => {
if (err) throw err;
constdb= client.db('animals');
db.collection('mammals')
.find()
.toArray((err, result) => {
if (err) throw err;
console.log(result);
});
});

If you want an object model driver for MongoDB, look at Mongoose.

Neo4j

Module: neo4j-driver

Installation

Terminal window

npminstallneo4j-driver

Example

index.cjs

constneo4j=require('neo4j-driver');
constdriver= neo4j.driver('neo4j://localhost:7687', neo4j.auth.basic('neo4j', 'letmein'));
constsession= driver.session();
session.readTransaction((tx) => {
return tx
.run('MATCH (n) RETURN count(n) AS count')
.then((res) => {
console.log(res.records[0].get('count'));
})
.catch((error) => {
console.log(error);
});
});

index.mjs

import neo4j from'neo4j-driver';
constdriver= neo4j.driver('neo4j://localhost:7687', neo4j.auth.basic('neo4j', 'letmein'));
constsession= driver.session();
session.readTransaction((tx) => {
return tx
.run('MATCH (n) RETURN count(n) AS count')
.then((res) => {
console.log(res.records[0].get('count'));
})
.catch((error) => {
console.log(error);
});
});

Oracle

Module: oracledb

Installation

NOTE: See installation prerequisites.

Terminal window

npminstalloracledb

Example

index.cjs

constoracledb=require('oracledb');
constconfig= {
user: '<your db user>',
password: '<your db password>',
connectString: 'localhost:1521/orcl',
};
asyncfunctiongetEmployee(empId) {
let conn;
try {
conn =await oracledb.getConnection(config);
constresult=await conn.execute('select * from employees where employee_id = :id', [empId]);
console.log(result.rows[0]);
} catch (err) {
console.log('Ouch!', err);
} finally {
if (conn) {
// conn assignment worked, need to close
await conn.close();
}
}
}
getEmployee(101);

index.mjs

import oracledb from'oracledb';
constconfig= {
user: '<your db user>',
password: '<your db password>',
connectString: 'localhost:1521/orcl',
};
asyncfunctiongetEmployee(empId) {
let conn;
try {
conn =await oracledb.getConnection(config);
constresult=await conn.execute('select * from employees where employee_id = :id', [empId]);
console.log(result.rows[0]);
} catch (err) {
console.log('Ouch!', err);
} finally {
if (conn) {
// conn assignment worked, need to close
await conn.close();
}
}
}
getEmployee(101);

PostgreSQL

Module: pg-promise

Installation

Terminal window

npminstallpg-promise

Example

index.js

constpgp=require('pg-promise')(/* options */);
constdb=pgp('postgres://username:password@host:port/database');
db.one('SELECT 1ドル AS value', 123)
.then((data) => {
console.log('DATA:', data.value);
})
.catch((error) => {
console.log('ERROR:', error);
});

Redis

Module: redis

Installation

Terminal window

npminstallredis

Example

index.cjs

constredis=require('redis');
constclient= redis.createClient();
client.on('error', (err) => {
console.log(`Error ${err}`);
});
client.set('string key', 'string val', redis.print);
client.hset('hash key', 'hashtest 1', 'some value', redis.print);
client.hset(['hash key', 'hashtest 2', 'some other value'], redis.print);
client.hkeys('hash key', (err, replies) => {
console.log(`${replies.length} replies:`);
replies.forEach((reply, i) => {
console.log(` ${i}: ${reply}`);
});
client.quit();
});

index.mjs

import redis from'redis';
constclient= redis.createClient();
client.on('error', (err) => {
console.log(`Error ${err}`);
});
client.set('string key', 'string val', redis.print);
client.hset('hash key', 'hashtest 1', 'some value', redis.print);
client.hset(['hash key', 'hashtest 2', 'some other value'], redis.print);
client.hkeys('hash key', (err, replies) => {
console.log(`${replies.length} replies:`);
replies.forEach((reply, i) => {
console.log(` ${i}: ${reply}`);
});
client.quit();
});

SQL Server

Module: tedious

Installation

Terminal window

npminstalltedious

Example

index.js

constConnection=require('tedious').Connection;
constRequest=require('tedious').Request;
constconfig= {
server: 'localhost',
authentication: {
type: 'default',
options: {
userName: 'your_username', // update me
password: 'your_password', // update me
},
},
};
constconnection=newConnection(config);
connection.on('connect', (err) => {
if (err) {
console.log(err);
} else {
executeStatement();
}
});
functionexecuteStatement() {
request =newRequest("select 123, 'hello world'", (err, rowCount) => {
if (err) {
console.log(err);
} else {
console.log(`${rowCount} rows`);
}
connection.close();
});
request.on('row', (columns) => {
columns.forEach((column) => {
if (column.value ===null) {
console.log('NULL');
} else {
console.log(column.value);
}
});
});
connection.execSql(request);
}

SQLite

Module: sqlite3

Installation

Terminal window

npminstallsqlite3

Example

index.js

constsqlite3=require('sqlite3').verbose();
constdb=new sqlite3.Database(':memory:');
db.serialize(() => {
db.run('CREATE TABLE lorem (info TEXT)');
conststmt= db.prepare('INSERT INTO lorem VALUES (?)');
for (let i =0; i <10; i++) {
stmt.run(`Ipsum ${i}`);
}
stmt.finalize();
db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
console.log(`${row.id}: ${row.info}`);
});
});
db.close();

Elasticsearch

Module: elasticsearch

Installation

Terminal window

npminstallelasticsearch

Example

index.cjs

constelasticsearch=require('elasticsearch');
constclient= elasticsearch.Client({
host: 'localhost:9200',
});
client
.search({
index: 'books',
type: 'book',
body: {
query: {
multi_match: {
query: 'express js',
fields: ['title', 'description'],
},
},
},
})
.then(
(response) => {
consthits= response.hits.hits;
},
(error) => {
console.trace(error.message);
}
);

index.mjs

import elasticsearch from'elasticsearch';
constclient= elasticsearch.Client({
host: 'localhost:9200',
});
client
.search({
index: 'books',
type: 'book',
body: {
query: {
multi_match: {
query: 'express js',
fields: ['title', 'description'],
},
},
},
})
.then(
(response) => {
consthits= response.hits.hits;
},
(error) => {
console.trace(error.message);
}
);