How to Create Table in SQLite3 Database using Node.js ?
Creating a table in an SQLite database using Node.js involves several steps, including setting up a connection to the database, defining the table schema, and executing SQL commands to create the table. SQLite is a lightweight and serverless database that is widely used, especially in applications that need a simple and embedded database solution. Here's a step-by-step guide on how to create a table in SQLite using Node.js:
SQLite
SQLite is a self-contained, high-reliability, embedded, public-domain, SQL database engine. It is the most used database engine in the world. Let’s understand How to create a table in a SQLite3 database using Node.js.
Steps to Setup Project
Step 1: Make a folder structure for the project.
mkdir myapp
Step 2: Navigate to the project directory
cd myapp
Step 3: Initialize the NodeJs project inside the myapp folder.
npm init -y
Step 4: Install the necessary packages/libraries in your project using the following commands.
npm install express sqlite3
Project Structure:

The updated dependencies in package.json file will look like:
"dependencies": {
"express": "^4.19.2",
"sqlite3": "^5.1.7",
}
Step 1: Set Up Database Connection
Require the sqlite3
module and create a new SQLite database connection. You can also create an in-memory database or specify a file-based database.
const sqlite3 = require('sqlite3').verbose();
// Connect to a database (in this example, a new file-based database)
const db = new sqlite3.Database('mydatabase.db');
If the database file (mydatabase.db
in this example) does not exist, SQLite will create it when you connect to it.
Step 2: Define Table Schema
Define the schema of the table using SQL CREATE TABLE
statement. Specify the table name, column names, data types, and any constraints (such as primary keys or unique constraints).
// Define the SQL statement to create a table
const createTableSql = `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password TEXT NOT NULL
)`;
// Execute the SQL statement to create the table
db.run(createTableSql, (err) => {
if (err) {
return console.error('Error creating table:', err.message);
}
console.log('Table created successfully');
});
In this example, a table named users
is created with columns id
, username
, email
, and password
. The id
column is set as the primary key with auto-increment, email
is marked as unique, and username
and password
are required (NOT NULL
).
Step 3: Execute SQL Commands
Use the db.run()
method to execute SQL commands. This method is used for SQL commands that don't return rows (like CREATE TABLE
, INSERT
, UPDATE
, DELETE
).
db.run(createTableSql, function(err) {
if (err) {
return console.error(err.message);
}
console.log('Table created successfully');
});
Example
Here's a complete example demonstrating how to create a SQLite database and a table using Node.js:
// main.js
import x from 'sqlite3'
const sqlite3 = x.verbose();
// Connect to a database (in this example, a new file-based database)
const db = new sqlite3.Database('mydatabase.db');
// Define the SQL statement to create a table
const createTableSql = `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password TEXT NOT NULL
)
`;
// Execute the SQL statement to create the table
db.run(createTableSql, function (err) {
if (err) {
return console.error('Error creating table:', err.message);
}
console.log('Table created successfully');
});
// Close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
console.log('Database connection closed');
});
Step to Run Application: Run the application using the following command from the root directory of the project
