How to Create Table in SQLite3 Database using Node.js ?

Last Updated : 29 Jul, 2024

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:

Node

// 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');
});