Summary: in this tutorial, you will learn how to insert one or more row into an SQLite table from a Node.js application.
To insert data into an SQLite table from a Node.js application, you follow these steps:
- Open a database connection.
- Execute an
INSERT
statement. - Close the database connection.
For the demonstration, we will create a new database named sample.db
in the db
folder.
When you open a database connection in the default mode, the database is created if it does not exist.
let db = new sqlite3.Database('./db/sample.db');
Code language: JavaScript (javascript)
In the sample.db
database, we create a table called langs
for storing programming languages:
db.run('CREATE TABLE langs(name text)');
Code language: JavaScript (javascript)
You can run the program to create the sample.db
database and langs
table as follows:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('../db/sample.db');
db.run('CREATE TABLE langs(name text)');
db.close();
Code language: JavaScript (javascript)
Now, we are ready to insert data into the langs
table.
Insert one row into a table
To execute an INSERT
statement, you use the run()
method of the Database
object:
db.run(sql, params, function(err){
//
});
Code language: JavaScript (javascript)
The run()
method executes an INSERT
statement with specified parameters and calls a callback afterwards.
If an error occurred, you can find the detailed information in the err
argument of the callback function.
In case the statement is executed successfully, the this
object of the callback function will contain two properties:
lastID
property stores the value of the last inserted row ID.changes
property stores the rows affected by the query.
The following insert.js
program illustrates how to insert a row into the langs
table:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./db/sample.db');
// insert one row into the langs table
db.run(`INSERT INTO langs(name) VALUES(?)`, ['C'], function(err) {
if (err) {
return console.log(err.message);
}
// get the last insert id
console.log(`A row has been inserted with rowid ${this.lastID}`);
});
// close the database connection
db.close();
Code language: JavaScript (javascript)
Let’s run the insert.js
program:
>node insert.js
A row has been inserted with rowid 1
Code language: JavaScript (javascript)
It worked as expected.
Insert multiple rows into a table at a time
To insert multiple rows at a time into a table, you use the following form of the INSERT
statement:
INSERT INTO table_name(column_name)
VALUES(value_1), (value_2), (value_3),...
Code language: SQL (Structured Query Language) (sql)
To simulate this in the Node.js application, we first need to construct the INSERT
statement with multiple placeholders:
INSERT INTO table_name(column_name)
VALUES(?), (?), (?),...
Code language: SQL (Structured Query Language) (sql)
Suppose, you want to insert rows into the langs
table with the data from the following languages
array:
let languages = ['C++', 'Python', 'Java', 'C#', 'Go'];
Code language: JavaScript (javascript)
To construct the INSERT
statement, we use the map()
method to map each element in the languages
array into (?)
and then join all placeholders together.
let placeholders = languages.map((language) => '(?)').join(',');
let sql = 'INSERT INTO langs(name) VALUES ' + placeholders;
Code language: JavaScript (javascript)
The following insert-many.js
program illustrates how to insert multiple rows into the langs
table:
const sqlite3 = require('sqlite3').verbose();
// open the database connection
let db = new sqlite3.Database('../db/sample.db');
let languages = ['C++', 'Python', 'Java', 'C#', 'Go'];
// construct the insert statement with multiple placeholders
// based on the number of rows
let placeholders = languages.map((language) => '(?)').join(',');
let sql = 'INSERT INTO langs(name) VALUES ' + placeholders;
// output the INSERT statement
console.log(sql);
db.run(sql, languages, function(err) {
if (err) {
return console.error(err.message);
}
console.log(`Rows inserted ${this.changes}`);
});
// close the database connection
db.close();
Code language: JavaScript (javascript)
Let’s run the insert-many.js
program to see how it works.
> node insert-many.js
INSERT INTO langs(name) VALUES (?),(?),(?),(?),(?)
Rows inserted 5
Code language: JavaScript (javascript)
It inserted 5 rows into the langs
table which is what we expected.
In this tutorial, you have learned how to insert one or more rows into an SQLite table from a Node.js application.