pppp

Node.js can be used in database applications.

One of the most popular databases is MySQL.

MySQL Database

To be able to experiment with the code examples, you should have MySQL installed on your computer.

You can download a free MySQL database at https://www.mysql.com/downloads/.

Install MySQL Driver

Once you have MySQL up and running on your computer, you can access it by using Node.js.

To access a MySQL database with Node.js, you need a MySQL driver. This tutorial will use the "mysql" module, downloaded from NPM.

To download and install the "mysql" module, open the Command Terminal and execute the following:

C:\Users\Your Name>npm install mysql

Now you have downloaded and installed a mysql database driver.

Node.js can use this module to manipulate the MySQL database:

var mysql = require('mysql');

Create Connection

Start by creating a connection to the database.

Use the username and password from your MySQL database.

demo_db_connection.js

Run example »

Save the code above in a file called "demo_db_connection.js" and run the file:

Run "demo_db_connection.js"

C:\Users\Your Name>node demo_db_connection.js

Which will give you this result:

Connected!

Now you can start querying the database using SQL statements.

Query a Database

Use SQL statements to read from (or write to) a MySQL database. This is also called "to query" the database.

The connection object created in the example above, has a method for querying the database:

con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Result: " + result);
});
});

The query method takes an sql statements as a parameter and returns the result.

Learn how to read, write, delete, and update a database in the next chapters.

Read more about SQL statements in our SQL Tutorial.

Creating a Database

To create a database in MySQL, use the "CREATE DATABASE" statement:

Example

Create a database named "mydb":

Run example »

Save the code above in a file called "demo_create_db.js" and run the file:

Run "demo_create_db.js"

C:\Users\Your Name>node demo_create_db.js

Which will give you this result:

Connected!
Database created

Creating a Table

To create a table in MySQL, use the "CREATE TABLE" statement.

Make sure you define the name of the database when you create the connection:

Example

Create a table named "customers":

Run example »

Save the code above in a file called "demo_create_table.js" and run the file:

Run "demo_create_table.js"

C:\Users\Your Name>node demo_create_table.js

Which will give you this result:

Connected!
Table created

Primary Key

When creating a table, you should also create a column with a unique key for each record.

This can be done by defining a column as "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

Example

Create primary key when creating the table:

Run example »

If the table already exists, use the ALTER TABLE keyword:

Example

Create primary key on an existing table:

Run example »

Insert Into Table

To fill a table in MySQL, use the "INSERT INTO" statement.

Example

Insert a record in the "customers" table:

Run example »

Save the code above in a file called "demo_db_insert.js", and run the file:

Run "demo_db_insert.js"

C:\Users\Your Name>node demo_db_insert.js

Which will give you this result:

Connected!
1 record inserted

Insert Multiple Records

To insert more than one record, make an array containing the values, and insert a question mark in the sql, which will be replaced by the value array:
INSERT INTO customers (name, address) VALUES ?

Example

Fill the "customers" table with data:

Run example »

Save the code above in a file called "demo_db_insert_multple.js", and run the file:

Run "demo_db_insert_multiple.js"

C:\Users\Your Name>node demo_db_insert_multiple.js

Which will give you this result:

Connected!
Number of records inserted: 14

The Result Object

When executing a query, a result object is returned.

The result object contains information about how the query affected the table.

The result object returned from the example above looks like this:

{
fieldCount: 0,
affectedRows: 14,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '\'Records:14 Duplicated: 0 Warnings: 0',
protocol41: true,
changedRows: 0
}

The values of the properties can be displayed like this:

Example

Return the number of affected rows:

console.log(result.affectedRows)

Which will produce this result:

Get Inserted ID

For tables with an auto increment id field, you can get the id of the row you just inserted by asking the result object.

Note: To be able to get the inserted id, only one row can be inserted.

Example

Insert a record in the "customers" table, and return the ID:

Run example »

Save the code above in a file called "demo_db_insert_id.js", and run the file:

Run "demo_db_insert_id.js"

C:\Users\Your Name>node demo_db_insert_id.js

Which will give you something like this in return:

1 record inserted, ID: 15

Selecting From a Table

To select data from a table in MySQL, use the "SELECT" statement.

Example

Select all records from the "customers" table, and display the result object:

Run example »

SELECT * will return all columns

Save the code above in a file called "demo_db_select.js" and run the file:

Run "demo_db_select.js"

C:\Users\Your Name>node demo_db_select.js

Which will give you this result:

[
{ id: 1, name: 'John', address: 'Highway 71'},
{ id: 2, name: 'Peter', address: 'Lowstreet 4'},
{ id: 3, name: 'Amy', address: 'Apple st 652'},
{ id: 4, name: 'Hannah', address: 'Mountain 21'},
{ id: 5, name: 'Michael', address: 'Valley 345'},
{ id: 6, name: 'Sandy', address: 'Ocean blvd 2'},
{ id: 7, name: 'Betty', address: 'Green Grass 1'},
{ id: 8, name: 'Richard', address: 'Sky st 331'},
{ id: 9, name: 'Susan', address: 'One way 98'},
{ id: 10, name: 'Vicky', address: 'Yellow Garden 2'},
{ id: 11, name: 'Ben', address: 'Park Lane 38'},
{ id: 12, name: 'William', address: 'Central st 954'},
{ id: 13, name: 'Chuck', address: 'Main Road 989'},
{ id: 14, name: 'Viola', address: 'Sideway 1633'}
]

Selecting Columns

To select only some of the columns in a table, use the "SELECT" statement followed by the column name.

Example

Select name and address from the "customers" table, and display the return object:

Run example »

Save the code above in a file called "demo_db_select2.js" and run the file:

Run "demo_db_select2.js"

C:\Users\Your Name>node demo_db_select2.js

Which will give you this result:

[
{ name: 'John', address: 'Highway 71'},
{ name: 'Peter', address: 'Lowstreet 4'},
{ name: 'Amy', address: 'Apple st 652'},
{ name: 'Hannah', address: 'Mountain 21'},
{ name: 'Michael', address: 'Valley 345'},
{ name: 'Sandy', address: 'Ocean blvd 2'},
{ name: 'Betty', address: 'Green Grass 1'},
{ name: 'Richard', address: 'Sky st 331'},
{ name: 'Susan', address: 'One way 98'},
{ name: 'Vicky', address: 'Yellow Garden 2'},
{ name: 'Ben', address: 'Park Lane 38'},
{ name: 'William', address: 'Central st 954'},
{ name: 'Chuck', address: 'Main Road 989'},
{ name: 'Viola', address: 'Sideway 1633'}
]

The Result Object

As you can see from the result of the example above, the result object is an array containing each row as an object.

To return e.g. the address of the third record, just refer to the third array object's address property:

Example

Return the address of the third record:

console.log(result[2].address);

Which will produce this result:

Apple st 652

The Fields Object

The third parameter of the callback function is an array containing information about each field in the result.

Example

Select all records from the "customers" table, and display the fields object:

Run example »

Save the code above in a file called "demo_db_select_fields.js" and run the file:

Run "demo_db_select_fields.js"

C:\Users\Your Name>node demo_db_select_fields.js

Which will give you this result:

[
{
    catalog: 'def',
    db: 'mydb',
    table: 'customers',
    orgTable: 'customers',
    name: 'name',
    orgName: 'address',
    charsetNr: 33,
    length: 765,
    type: 253,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true
},
{
    catalog: 'def',
    db: 'mydb',
    table: 'customers',
    orgTable: 'customers',
    name: 'address',
    orgName: 'address',
    charsetNr: 33,
    length: 765,
    type: 253,
    flags: 0,
    decimals: 0,
    default: undefined,
    zeroFill: false,
    protocol41: true
{
]

As you can see from the result of the example above, the fields object is an array containing information about each field as an object.

To return e.g. the name of the second field, just refer to the second array item's name property:

Example

Return the name of the second field:

console.log(fields[1].name);

Which will produce this result:

address

Select With a Filter

When selecting records from a table, you can filter the selection by using the "WHERE" statement:

Example

Select record(s) with the address "Park Lane 38":

Run example »

Save the code above in a file called "demo_db_where.js" and run the file:

Run "demo_db_where.js"

C:\Users\Your Name>node demo_db_where.js

Which will give you this result:

[
{ id: 11, name: 'Ben', address: 'Park Lane 38'}
]

Wildcard Characters

You can also select the records that starts, includes, or ends with a given letter or phrase.

Use the '%' wildcard to represent zero, one or multiple characters:

Example

Select records where the address starts with the letter 'S':

Run example »

Save the code above in a file called "demo_db_where_s.js" and run the file:

Run "demo_db_where_s.js"

C:\Users\Your Name>node demo_db_where_s.js

Which will give you this result:

[
{ id: 8, name: 'Richard', address: 'Sky st 331'},
{ id: 14, name: 'Viola', address: 'Sideway 1633'}
]

Escaping Query Values

When query values are variables provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

The MySQL module has methods to escape query values:

Example

Escape query values by using the mysql.escape() method:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});

Run example »

You can also use a ? as a placeholder for the values you want to escape.

In this case, the variable is sent as the second parameter in the query() method:

Example

Escape query values by using the placeholder ? method:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result) {
if (err) throw err;
console.log(result);
});

Run example »

If you have multiple placeholders, the array contains multiple values, in that order:

Example

Multiple placeholders:

var name = 'Amy';
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE name = ? OR address = ?';
con.query(sql, [name, adr], function (err, result) {
if (err) throw err;
console.log(result);
});

Run example »

Sort the Result

Use the ORDER BY statement to sort the result in ascending or descending order.

The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.

Example

Sort the result alphabetically by name:

Run example »

Save the code above in a file called "demo_db_orderby.js" and run the file:

Run "demo_db_orderby.js"

C:\Users\Your Name>node demo_db_orderby.js

Which will give you this result:

[
{ id: 3, name: 'Amy', address: 'Apple st 652'},
{ id: 11, name: 'Ben', address: 'Park Lane 38'},
{ id: 7, name: 'Betty', address: 'Green Grass 1'},
{ id: 13, name: 'Chuck', address: 'Main Road 989'},
{ id: 4, name: 'Hannah', address: 'Mountain 21'},
{ id: 1, name: 'John', address: 'Higheay 71'},
{ id: 5, name: 'Michael', address: 'Valley 345'},
{ id: 2, name: 'Peter', address: 'Lowstreet 4'},
{ id: 8, name: 'Richard', address: 'Sky st 331'},
{ id: 6, name: 'Sandy', address: 'Ocean blvd 2'},
{ id: 9, name: 'Susan', address: 'One way 98'},
{ id: 10, name: 'Vicky', address: 'Yellow Garden 2'},
{ id: 14, name: 'Viola', address: 'Sideway 1633'},
{ id: 12, name: 'William', address: 'Central st 954'}
]

ORDER BY DESC

Use the DESC keyword to sort the result in a descending order.

Example

Sort the result reverse alphabetically by name:

Run example »

Save the code above in a file called "demo_db_orderby_desc.js" and run the file:

Run "demo_db_orderby_desc.js"

C:\Users\Your Name>node demo_db_orderby_desc.js

Which will give you this result:

[
{ id: 12, name: 'William', address: 'Central st 954'},
{ id: 14, name: 'Viola', address: 'Sideway 1633'},
{ id: 10, name: 'Vicky', address: 'Yellow Garden 2'},
{ id: 9, name: 'Susan', address: 'One way 98'},
{ id: 6, name: 'Sandy', address: 'Ocean blvd 2'},
{ id: 8, name: 'Richard', address: 'Sky st 331'},
{ id: 2, name: 'Peter', address: 'Lowstreet 4'},
{ id: 5, name: 'Michael', address: 'Valley 345'},
{ id: 1, name: 'John', address: 'Higheay 71'},
{ id: 4, name: 'Hannah', address: 'Mountain 21'},
{ id: 13, name: 'Chuck', address: 'Main Road 989'},
{ id: 7, name: 'Betty', address: 'Green Grass 1'},
{ id: 11, name: 'Ben', address: 'Park Lane 38'},
{ id: 3, name: 'Amy', address: 'Apple st 652'}
]

Delete Record

You can delete records from an existing table by using the "DELETE FROM" statement:

Example

Delete any record with the address "Mountain 21":

Run example »

Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Save the code above in a file called "demo_db_delete.js" and run the file:

Run "demo_db_delete.js"

C:\Users\Your Name>node demo_db_delete.js

Which will give you this result:

Number of records deleted: 1

The Result Object

When executing a query, a result object is returned.

The result object contains information about how the query affected the table.

The result object returned from the example above looks like this:

{
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 34,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}

The values of the properties can be displayed like this:

Example

Return the number of affected rows:

console.log(result.affectedRows)

Which will produce this result:

Delete a Table

You can delete an existing table by using the "DROP TABLE" statement:

Example

Delete the table "customers":

Run example »

Save the code above in a file called "demo_db_drop_table.js" and run the file:

Run "demo_db_drop_table.js"

C:\Users\Your Name>node demo_db_drop_table.js

Which will give you this result:

Table deleted

Drop Only if Exist

If the the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error.

Example

Delete the table "customers" if it exists:

Run example »

Save the code above in a file called "demo_db_drop_table_if.js" and run the file:

Run "demo_db_drop_table_if.js"

C:\Users\Your Name>node demo_db_drop_table_if.js

If the table exist, the result object will look like this:

{
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverstatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}

If the table does not exist, the result object will look like this:

{
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverstatus: 2,
warningCount: 1,
message: '',
protocol41: true,
changedRows: 0
}

As you can see the only differnce is that the warningCount property is set to 1 if the table does not exist.

Update Table

You can update existing records in a table by using the "UPDATE" statement:

Example

Overwrite the address column from "Valley 345" to "Canyon 123":

Run example »

Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Save the code above in a file called "demo_db_update.js" and run the file:

Run "demo_db_update.js"

C:\Users\Your Name>node demo_db_update.js

Which will give you this result:

1 record(s) updated

The Result Object

When executing a query, a result object is returned.

The result object contains information about how the query affected the table.

The result object returned from the example above looks like this:

{
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 34,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1
}

The values of the properties can be displayed like this:

Example

Return the number of affected rows:

console.log(result.affectedRows)

Which will produce this result:

Limit the Result

You can limit the number of records returned from the query, by using the "LIMIT" statement:

Example

Select the 5 first records in the "customers" table:

Run example »

Save the code above in a file called "demo_db_limit.js" and run the file:

Run "demo_db_limit.js"

C:\Users\Your Name>node demo_db_limit.js

Which will give you this result:

Start From Another Position

If you want to return five records, starting from the third record, you can use the "OFFSET" keyword:

Example

Start from position 3, and return the next 5 records:

Run example »

Note: "OFFSET 2", means starting from the third position, not the second!

Save the code above in a file called "demo_db_offset.js" and run the file:

Run "demo_db_offset.js"

C:\Users\Your Name>node demo_db_offset.js

Which will give you this result:

[
{ id: 3, name: 'Amy', address: 'Apple st 652'},
{ id: 4, name: 'Hannah', address: 'Mountain 21'},
{ id: 5, name: 'Michael', address: 'Valley 345'},
{ id: 6, name: 'Sandy', address: 'Ocean blvd 2'},
{ id: 7, name: 'Betty', address: 'Green Grass 1'}
]

Shorter Syntax

You can also write your SQL statement like this "LIMIT 2, 5" which returns the same as the offset example above:

Example

Start from position 3, and return the next 5 records:

Run example »

Note: The numbers are reversed: "LIMIT 2, 5" is the same as "LIMIT 5 OFFSET 2"

Join Two or More Tables

You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.

Consider you have a "users" table and a "products" table:

users

[
{ id: 1, name: 'John', favorite_product: 154},
{ id: 2, name: 'Peter', favorite_product: 154},
{ id: 3, name: 'Amy', favorite_product: 155},
{ id: 4, name: 'Hannah', favorite_product:},
{ id: 5, name: 'Michael', favorite_product:}
]

products

[
{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }
]

These two tables can be combined by using users' favorite_product field and products' id field.

Example

Select records with a match in both tables:

Run example »

Note: You can use INNER JOIN instead of JOIN. They will both give you the same result.

Save the code above in a file called "demo_db_join.js" and run the file:

Run "demo_db_join.js"

C:\Users\Your Name>node demo_db_join.js

Which will give you this result:

[
{ user: 'John', favorite: 'Chocolate Heaven' },
{ user: 'Peter', favorite: 'Chocolate Heaven' },
{ user: 'Amy', favorite: 'Tasty Lemons' }
]

As you can see from the result above, only the records with a match in both tables are returned.

Left Join

If you want to return all users, no matter if they have a favorite product or not, use the LEFT JOIN statement:

Example

Select all users and their favorite product:

SELECT users.name AS user,
products.name AS favorite
FROM users
LEFT JOIN products ON users.favorite_product = products.id

Run example »

Which will give you this result:

[
{ user: 'John', favorite: 'Chocolate Heaven' },
{ user: 'Peter', favorite: 'Chocolate Heaven' },
{ user: 'Amy', favorite: 'Tasty Lemons' },
{ user: 'Hannah', favorite: null },
{ user: 'Michael', favorite: null }
]

Right Join

If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement:

Example

Select all products and the user who have them as their favorite:

SELECT users.name AS user,
products.name AS favorite
FROM users
RIGHT JOIN products ON users.favorite_product = products.id

Run example »

Which will give you this result:

[
{ user: 'John', favorite: 'Chocolate Heaven' },
{ user: 'Peter', favorite: 'Chocolate Heaven' },
{ user: 'Amy', favorite: 'Tasty Lemons' },
{ user: null, favorite: 'Vanilla Dreams' }
]

Note: Hannah and Michael, who have no favorite product, are not included in the result.

Node.js can be used in database applications.

One of the most popular NoSQL database is MongoDB.

MongoDB

To be able to experiment with the code examples, you will need access to a MongoDB database.

You can download a free MongoDB database at https://www.mongodb.com.

Install MongoDB Driver

Let us try to access a MongoDB database with Node.js.

To download and install the official MongoDB driver, open the Command Terminal and execute the following:

Download and install mongodb package:

C:\Users\Your Name>npm install mongodb

Now you have downloaded and installed a mongodb database driver.

Node.js can use this module to manipulate MongoDB databases:

var mongo = require('mongodb');

Creating a Database

To create a database in MongoDB, start by creating a MongoClient object, then specify a connection URL with the correct ip address and the name of the database you want to create.

MongoDB will create the database if it does not exist, and make a connection to it.

Example

Create a database called "mydb":

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/mydb";

MongoClient.connect(url, function(err, db) {
if (err) throw err;
console.log("Database created!");
db.close();
});

Run example »

Save the code above in a file called "demo_create_mongo_db.js" and run the file:

Run "demo_create_mongo_db.js"

C:\Users\Your Name>node demo_create_mongo_db.js

Which will give you this result:

Database created!

Important: In MongoDB, a database is not created until it gets content!

MongoDB waits until you have created a collection (table), with at least one document (record) before it actually creates the database (and collection).

A collection in MongoDB is the same as a table in MySQL

Creating a Collection

To create a collection in MongoDB, use the createCollection() method:

Example

Create a collection called "customers":

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";

MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
dbo.createCollection("customers", function(err, res) {
    if (err) throw err;
    console.log("Collection created!");
    db.close();
});
});

Run example »

Save the code above in a file called "demo_mongodb_createcollection.js" and run the file:

Run "demo_mongodb_createcollection.js"

C:\Users\Your Name>node demo_mongodb_createcollection.js

Which will give you this result:

Collection created!

Important: In MongoDB, a collection is not created until it gets content!

MongoDB waits until you have inserted a document before it actually creates the collection.

Insert Into Collection

To insert a record, or document as it is called in MongoDB, into a collection, we use the insertOne() method.

A document in MongoDB is the same as a record in MySQL

The first parameter of the insertOne() method is an object containing the name(s) and value(s) of each field in the document you want to insert.

It also takes a callback function where you can work with any errors, or the result of the insertion:

Example

Insert a document in the "customers" collection:

Run example »

Save the code above in a file called "demo_mongodb_insert.js" and run the file:

Run "demo_mongodb_insert.js"

C:\Users\Your Name>node demo_mongodb_insert.js

Which will give you this result:

1 document inserted

Note: If you try to insert documents in a collection that do not exist, MongoDB will create the collection automatically.

Insert Multiple Documents

To insert multiple documents into a collection in MongoDB, we use the insertMany() method.

The first parameter of the insertMany() method is an array of objects, containing the data you want to insert.

It also takes a callback function where you can work with any errors, or the result of the insertion:

Example

Insert multiple documents in the "customers" collection:

Run example »

Save the code above in a file called "demo_mongodb_insert_multiple.js" and run the file:

Run "demo_mongodb_insert_multiple.js"

C:\Users\Your Name>node demo_mongodb_insert_multiple.js

Which will give you this result:

Number of documents inserted: 14

The Result Object

When executing the insertMany() method, a result object is returned.

The result object contains information about how the insertion affected the database.

The object returned from the example above looked like this:

{
result: { ok: 1, n: 14 },
ops: [
    { name: 'John', address: 'Highway 71', _id: 58fdbf5c0ef8a50b4cdd9a84 },
    { name: 'Peter', address: 'Lowstreet 4', _id: 58fdbf5c0ef8a50b4cdd9a85 },
    { name: 'Amy', address: 'Apple st 652', _id: 58fdbf5c0ef8a50b4cdd9a86 },
    { name: 'Hannah', address: 'Mountain 21', _id: 58fdbf5c0ef8a50b4cdd9a87 },
    { name: 'Michael', address: 'Valley 345', _id: 58fdbf5c0ef8a50b4cdd9a88 },
    { name: 'Sandy', address: 'Ocean blvd 2', _id: 58fdbf5c0ef8a50b4cdd9a89 },
    { name: 'Betty', address: 'Green Grass 1', _id: 58fdbf5c0ef8a50b4cdd9a8a },
    { name: 'Richard', address: 'Sky st 331', _id: 58fdbf5c0ef8a50b4cdd9a8b },
    { name: 'Susan', address: 'One way 98', _id: 58fdbf5c0ef8a50b4cdd9a8c },
    { name: 'Vicky', address: 'Yellow Garden 2', _id: 58fdbf5c0ef8a50b4cdd9a8d },
    { name: 'Ben', address: 'Park Lane 38', _id: 58fdbf5c0ef8a50b4cdd9a8e },
    { name: 'William', address: 'Central st 954', _id: 58fdbf5c0ef8a50b4cdd9a8f },
    { name: 'Chuck', address: 'Main Road 989', _id: 58fdbf5c0ef8a50b4cdd9a90 },
    { name: 'Viola', address: 'Sideway 1633', _id: 58fdbf5c0ef8a50b4cdd9a91 } ],
insertedCount: 14,
insertedIds: [
    58fdbf5c0ef8a50b4cdd9a84,
    58fdbf5c0ef8a50b4cdd9a85,
    58fdbf5c0ef8a50b4cdd9a86,
    58fdbf5c0ef8a50b4cdd9a87,
    58fdbf5c0ef8a50b4cdd9a88,
    58fdbf5c0ef8a50b4cdd9a89,
    58fdbf5c0ef8a50b4cdd9a8a,
    58fdbf5c0ef8a50b4cdd9a8b,
    58fdbf5c0ef8a50b4cdd9a8c,
    58fdbf5c0ef8a50b4cdd9a8d,
    58fdbf5c0ef8a50b4cdd9a8e,
    58fdbf5c0ef8a50b4cdd9a8f
    58fdbf5c0ef8a50b4cdd9a90,
    58fdbf5c0ef8a50b4cdd9a91 ]
}

The values of the properties can be displayed like this:

Example

Return the number of inserted documents:

console.log(res.insertedCount)

Which will produce this result:

The _id Field

If you do not specify an _id field, then MongoDB will add one for you and assign a unique id for each document.

In the example above no _id field was specified, and as you can see from the result object, MongoDB assigned a unique _id for each document.

If you do specify the _id field, the value must be unique for each document:

Example

Insert three records in a "products" table, with specified _id fields:

Run example »

Save the code above in a file called "demo_mongodb_insert_id.js" and run the file:

Run "demo_mongodb_insert_id.js"

C:\Users\Your Name>node demo_mongodb_insert_id.js

Which will give you this result:

{
result: { ok: 1, n: 3 },
ops: [
    { _id: 154, name: 'Chocolate Heaven },
    { _id: 155, name: 'Tasty Lemon },
    { _id: 156, name: 'Vanilla Dream } ],
insertedCount: 3,
insertedIds: [
    154,
    155,
    156 ]
}

In MongoDB we use the find and findOne methods to find data in a collection.

Just like the SELECT statement is used to find data in a table in a MySQL database.

Find One

To select data from a collection in MongoDB, we can use the findOne() method.

The findOne() method returns the first occurrence in the selection.

The first parameter of the findOne() method is a query object. In this example we use an empty query object, which selects all documents in a collection (but returns only the first document).

Example

Find the first document in the customers collection:

Run example »

Save the code above in a file called "demo_mongodb_findone.js" and run the file:

Run "demo_mongodb_findone.js"

C:\Users\Your Name>node demo_mongodb_findone.js

Which will give you this result:

Company Inc.

Find All

To select data from a table in MongoDB, we can also use the find() method.

The find() method returns all occurrences in the selection.

The first parameter of the find() method is a query object. In this example we use an empty query object, which selects all documents in the collection.

No parameters in the find() method gives you the same result as SELECT * in MySQL.

Example

Find all documents in the customers collection:

Run example »

Save the code above in a file called "demo_mongodb_find.js" and run the file:

Run "demo_mongodb_find.js"

C:\Users\Your Name>node demo_mongodb_find.js

Which will give you this result:

[
{ _id: 58fdbf5c0ef8a50b4cdd9a84 , name: 'John', address: 'Highway 71'},
{ _id: 58fdbf5c0ef8a50b4cdd9a85 , name: 'Peter', address: 'Lowstreet 4'},
{ _id: 58fdbf5c0ef8a50b4cdd9a86 , name: 'Amy', address: 'Apple st 652'},
{ _id: 58fdbf5c0ef8a50b4cdd9a87 , name: 'Hannah', address: 'Mountain 21'},
{ _id: 58fdbf5c0ef8a50b4cdd9a88 , name: 'Michael', address: 'Valley 345'},
{ _id: 58fdbf5c0ef8a50b4cdd9a89 , name: 'Sandy', address: 'Ocean blvd 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8a , name: 'Betty', address: 'Green Grass 1'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8b , name: 'Richard', address: 'Sky st 331'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8c , name: 'Susan', address: 'One way 98'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8d , name: 'Vicky', address: 'Yellow Garden 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8e , name: 'Ben', address: 'Park Lane 38'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8f , name: 'William', address: 'Central st 954'},
{ _id: 58fdbf5c0ef8a50b4cdd9a90 , name: 'Chuck', address: 'Main Road 989'},
{ _id: 58fdbf5c0ef8a50b4cdd9a91 , name: 'Viola', address: 'Sideway 1633'}
]

Find Some

The second parameter of the find() method is the projection object that describes which fields to include in the result.

This parameter is optional, and if omitted, all fields will be included in the result.

Example

Return the fields "name" and "address" of all documents in the customers collection:

Run example »

Save the code above in a file called "demo_mongodb_find_fields.js" and run the file:

Run "demo_mongodb_find_fields.js"

C:\Users\Your Name>node demo_mongodb_find_fields.js

Which will give you this result:

You are not allowed to specify both 0 and 1 values in the same object (except if one of the fields is the _id field). If you specify a field with the value 0, all other fields get the value 1, and vice versa:

Example

This example will exclude "address" from the result:

Run example »

To exclude the _id field, you must set its value to 0:

Example

This example will return only the "name" field:

Run example »

Example

This example will give you the same result as the first example; return all fields except the _id field:

Run example »

Example

You get an error if you specify both 0 and 1 values in the same object (except if one of the fields is the _id field):

Run example »

The Result Object

As you can see from the result of the example above, the result can be converted into an array containing each document as an object.

To return e.g. the address of the third document, just refer to the third array object's address property:

Example

Return the address of the third document:

console.log(result[2].address);

Which will produce this result:

Apple st 652

Filter the Result

When finding documents in a collection, you can filter the result by using a query object.

The first argument of the find() method is a query object, and is used to limit the search.

Example

Find documents with the address "Park Lane 38":

Run example »

Save the code above in a file called "demo_mongodb_query.js" and run the file:

Run "demo_mongodb_query.js"

C:\Users\Your Name>node demo_mongodb_query.js

Which will give you this result:

[
{ _id: 58fdbf5c0ef8a50b4cdd9a8e , name: 'Ben', address: 'Park Lane 38' }
]

Filter With Regular Expressions

You can write regular expressions to find exactly what you are searching for.

Regular expressions can only be used to query strings.

To find only the documents where the "address" field starts with the letter "S", use the regular expression /^S/:

Example

Find documents where the address starts with the letter "S":

Run example »

Save the code above in a file called "demo_mongodb_query_s.js" and run the file:

Run "demo_mongodb_query_s.js"

C:\Users\Your Name>node demo_mongodb_query_s.js

Which will give you this result:

[
{ _id: 58fdbf5c0ef8a50b4cdd9a8b , name: 'Richard', address: 'Sky st 331' },
{ _id: 58fdbf5c0ef8a50b4cdd9a91 , name: 'Viola', address: 'Sideway 1633' }
]

Sort the Result

Use the sort() method to sort the result in ascending or descending order.

The sort() method takes one parameter, an object defining the sorting order.

Example

Sort the result alphabetically by name:

Run example »

Save the code above in a file called "demo_sort.js" and run the file:

Run "demo_sort.js"

C:\Users\Your Name>node demo_sort.js

Which will give you this result:

[
{ _id: 58fdbf5c0ef8a50b4cdd9a86, name: 'Amy', address: 'Apple st 652'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8e, name: 'Ben', address: 'Park Lane 38'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8a, name: 'Betty', address: 'Green Grass 1'},
{ _id: 58fdbf5c0ef8a50b4cdd9a90, name: 'Chuck', address: 'Main Road 989'},
{ _id: 58fdbf5c0ef8a50b4cdd9a87, name: 'Hannah', address: 'Mountain 21'},
{ _id: 58fdbf5c0ef8a50b4cdd9a84, name: 'John', address: 'Highway 71'},
{ _id: 58fdbf5c0ef8a50b4cdd9a88, name: 'Michael', address: 'Valley 345'},
{ _id: 58fdbf5c0ef8a50b4cdd9a85, name: 'Peter', address: 'Lowstreet 4'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8b, name: 'Richard', address: 'Sky st 331'},
{ _id: 58fdbf5c0ef8a50b4cdd9a89, name: 'Sandy', address: 'Ocean blvd 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8c, name: 'Susan', address: 'One way 98'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8d, name: 'Vicky', address: 'Yellow Garden 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a91, name: 'Viola', address: 'Sideway 1633'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8f, name: 'William', address: 'Central st 954'}
]

Sort Descending

Use the value -1 in the sort object to sort descending.

{ name: 1 } // ascending
{ name: -1 } // descending

Example

Sort the result reverse alphabetically by name:

Run example »

Save the code above in a file called "demo_sort_desc.js" and run the file:

Run "demo_sort_desc.js"

C:\Users\Your Name>node demo_sort_desc.js

Which will give you this result:

[
{ _id: 58fdbf5c0ef8a50b4cdd9a8f, name: 'William', address: 'Central st 954'},
{ _id: 58fdbf5c0ef8a50b4cdd9a91, name: 'Viola', address: 'Sideway 1633'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8d, name: 'Vicky', address: 'Yellow Garden 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8c, name: 'Susan', address: 'One way 98'},
{ _id: 58fdbf5c0ef8a50b4cdd9a89, name: 'Sandy', address: 'Ocean blvd 2'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8b, name: 'Richard', address: 'Sky st 331'},
{ _id: 58fdbf5c0ef8a50b4cdd9a85, name: 'Peter', address: 'Lowstreet 4'},
{ _id: 58fdbf5c0ef8a50b4cdd9a88, name: 'Michael', address: 'Valley 345'},
{ _id: 58fdbf5c0ef8a50b4cdd9a84, name: 'John', address: 'Highway 71'},
{ _id: 58fdbf5c0ef8a50b4cdd9a87, name: 'Hannah', address: 'Mountain 21'},
{ _id: 58fdbf5c0ef8a50b4cdd9a90, name: 'Chuck', address: 'Main Road 989'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8a, name: 'Betty', address: 'Green Grass 1'},
{ _id: 58fdbf5c0ef8a50b4cdd9a8e, name: 'Ben', address: 'Park Lane 38'},
{ _id: 58fdbf5c0ef8a50b4cdd9a86, name: 'Amy', address: 'Apple st 652'}
]

Delete Document

To delete a record, or document as it is called in MongoDB, we use the deleteOne() method.

The first parameter of the deleteOne() method is a query object defining which document to delete.

Note: If the query finds more than one document, only the first occurrence is deleted.

Example

Delete the document with the address "Mountain 21":

Run example »

Save the code above in a file called "demo_delete.js" and run the file:

Run "demo_delete.js"

C:\Users\Your Name>node demo_delete.js

Which will give you this result:

1 document deleted

Delete Many

To delete more than one document, use the deleteMany() method.

The first parameter of the deleteMany() method is a query object defining which documents to delete.

Example

Delete all documents were the address starts with the letter "O":

Run example »

Save the code above in a file called "demo_delete_many.js" and run the file:

Run "demo_delete_many.js"

C:\Users\Your Name>node demo_delete_many.js

Which will give you this result:

2 document(s) deleted

The Result Object

The deleteMany() method returns an object which contains information about how the execution affected the database.

Most of the information is not important to understand, but one object inside the object is called "result" which tells us if the execution went OK, and how many documents were affected.

The result object looks like this:

{ n: 2, ok: 1 }

You can use this object to return the number of deleted documents:

Example

Return the number of deleted documents:

console.log(obj.result.n);

Which will produce this result:

Drop Collection

You can delete a table, or collection as it is called in MongoDB, by using the drop() method.

The drop() method takes a callback function containing the error object and the result parameter which returns true if the collection was dropped successfully, otherwise it returns false.

Example

Delete the "customers" table:

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";

MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
dbo.collection("customers").drop(function(err, delOK) {
    if (err) throw err;
    if (delOK) console.log("Collection deleted");
    db.close();
});
});

Run example »

Save the code above in a file called "demo_drop.js" and run the file:

Run "demo_drop.js"

C:\Users\Your Name>node demo_drop.js

Which will give you this result:

Collection deleted

db.dropCollection

You can also use the dropCollection() method to delete a table (collection).

The dropCollection() method takes two parameters: the name of the collection and a callback function.

Example

Delete the "customers" collection, using dropCollection():

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";

MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
dbo.dropCollection("customers", function(err, delOK) {
    if (err) throw err;
    if (delOK) console.log("Collection deleted");
    db.close();
});
});

Run example »

Save the code above in a file called "demo_dropcollection.js" and run the file:

Run "demo_dropcollection.js"

C:\Users\Your Name>node demo_dropcollection.js

Which will give you this result:

Collection deleted

Update Document

You can update a record, or document as it is called in MongoDB, by using the updateOne() method.

The first parameter of the updateOne() method is a query object defining which document to update.

Note: If the query finds more than one record, only the first occurrence is updated.

The second parameter is an object defining the new values of the document.

Example

Update the document with the address "Valley 345" to name="Mickey" and address="Canyon 123":

Run example »

Save the code above in a file called "demo_update_one.js" and run the file:

Run "demo_update_one.js"

C:\Users\Your Name>node demo_update_one.js

Which will give you this result:

1 document updated

Update Only Specific Fields

When using the $set operator, only the specified fields are updated:

Example

Update the address from "Valley 345" to "Canyon 123":

...
var myquery = { address: "Valley 345" };
var newvalues = { $set: { address: "Canyon 123" } };
dbo.collection("customers").updateOne(myquery, newvalues, function(err, res) {
...

Run example »

Update Many Documents

To update all documents that meets the criteria of the query, use the updateMany() method.

Example

Update all documents where the name starts with the letter "S":

Run example »

Save the code above in a file called "demo_update_many.js" and run the file:

Run "demo_update_many.js"

C:\Users\Your Name>node demo_update_many.js

Which will give you this result:

2 document(s) updated

The Result Object

The updateOne() and the updateMany() methods return an object which contains information about how the execution affected the database.

Most of the information is not important to understand, but one object inside the object is called "result" which tells us if the execution went OK, and how many documents were affected.

The result object looks like this:

{ n: 1, nModified: 2, ok: 1 }

You can use this object to return the number of updated documents:

Example

Return the number of updated documents:

console.log(res.result.nModified);

Which will produce this result:

Limit the Result

To limit the result in MongoDB, we use the limit() method.

The limit() method takes one parameter, a number defining how many documents to return.

Consider you have a "customers" collection:

customers

Example

Limit the result to only return 5 documents:

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";

MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("mydb");
dbo.collection("customers").find().limit(5).toArray(function(err, result) {
    if (err) throw err;
    console.log(result);
    db.close();
});
});

Run example »

Save the code above in a file called "demo_mongodb_limit.js" and run the file:

Run "demo_mongodb_limit.js"

C:\Users\Your Name>node demo_mongodb_limit.js

Which will give you this result:

customers

As you can see from the result above, only the 5 first documents were returned.

Join Collections

MongoDB is not a relational database, but you can perform a left outer join by using the $lookup stage.

The $lookup stage lets you specify which collection you want to join with the current collection, and which fields that should match.

Consider you have a "orders" collection and a "products" collection:

orders

[
{ _id: 1, product_id: 154, status: 1 }
]

products

[
{ _id: 154, name: 'Chocolate Heaven' },
{ _id: 155, name: 'Tasty Lemons' },
{ _id: 156, name: 'Vanilla Dreams' }
]

Example

Join the matching "products" document(s) to the "orders" collection:

Run example »

Save the code above in a file called "demo_mongodb_join.js" and run the file:

Run "demo_mongodb_join.js"

C:\Users\Your Name>node demo_mongodb_join.js

Which will give you this result:

[
{ "_id": 1, "product_id": 154, "status": 1, "orderdetails": [
{ "_id": 154, "name": "Chocolate Heaven" } ]
}
]

As you can see from the result above, the matching document from the products collection is included in the orders collection as an array.