Express.js - REST API with SQLite

A REST API (Representational State Transfer Application Programming Interface) in Express.js is a way to build web services that allow clients (such as web or mobile apps) to communicate with a server using HTTP methods like GET, POST, PUT, and DELETE.

Key Features of REST API in Express.js

Stateless : Each request from a client contains all the information needed, with no session stored on the server.
Uses HTTP Methods
- GET → Retrieve data
- POST → Create new data
- PUT → Update existing data
- DELETE → Remove data
Follows REST Principles : Uses URLs as resources (e.g., /users to manage users).
Returns Data in JSON Format : Standard for APIs, making it easy for clients to consume.

What is SQLite?

SQLite is a lightweight, serverless, self-contained SQL database engine that's built into many applications.
SQLite is an embedded database — it runs in the same process as your application, with no need for a separate server.
It uses a single file (.db) to store the entire database.
It's written in C and extremely fast and efficient for local storage needs.

Install Dependencies

Install Express.js with the npm command.

npm install express sqlite3

Basic Express.js REST API Setup

Create a file index.js and set up a simple API

index.js

const express = require('express');
const sqlite3 = require('sqlite3').verbose();

const app = express();
app.use(express.json());

// Connect to SQLite DB
const db = new sqlite3.Database('./db_crud.db', (err) => {
  if (err) return console.error(err.message);
  console.log('Connected to SQLite database.');
});

// Create 'users' table if it doesn't exist
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  )
`);

// Create user
app.post('/users', (req, res) => {
  const { name, email } = req.body;
  if (!name || !email)
    return res.status(400).json({ message: 'Name and email are required' });

  db.run(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email],
    function (err) {
      if (err) return res.status(500).json({ error: err.message });
      res.status(201).json({
        message: 'User created successfully',
        data: { id: this.lastID, name, email }
      });
    }
  );
});

// Get all users
app.get('/users', (req, res) => {
  db.all('SELECT * FROM users', [], (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json({
      message: 'Users retrieved successfully',
      data: rows
    });
  });
});

// Get user by ID
app.get('/users/:id', (req, res) => {
  db.get('SELECT * FROM users WHERE id = ?', [req.params.id], (err, row) => {
    if (err) return res.status(500).json({ error: err.message });
    if (!row) return res.status(404).json({ message: 'User not found' });

    res.json({
      message: 'User retrieved successfully',
      data: row
    });
  });
});

// Update user
app.put('/users/:id', (req, res) => {
  const { name, email } = req.body;
  if (!name || !email)
    return res.status(400).json({ message: 'Name and email are required' });

  db.run(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [name, email, req.params.id],
    function (err) {
      if (err) return res.status(500).json({ error: err.message });
      if (this.changes === 0)
        return res.status(404).json({ message: 'User not found' });

      res.json({
        message: 'User updated successfully',
        data: { id: parseInt(req.params.id), name, email }
      });
    }
  );
});

// Delete user
app.delete('/users/:id', (req, res) => {
  db.run('DELETE FROM users WHERE id = ?', [req.params.id], function (err) {
    if (err) return res.status(500).json({ error: err.message });
    if (this.changes === 0)
      return res.status(404).json({ message: 'User not found' });

    res.json({ message: 'User deleted successfully' });
  });
});

// Start server
const PORT = 5000;

app.listen(PORT, () => {
  console.log(`Server running at http://localhost:${PORT}`);
});

Run the Server

Run the server using the command is given below.

node index.js

D:\my-app>node index.js
Server running at http://localhost:5000
Connected to SQLite database.

Output

Get All Users

Request:
GET: http://localhost:5000/users
Content-Type: application/json

Response:
Status: 200 OK
Content-Type: application/json

{
  "message": "Users retrieved successfully",
  "data": [
    { 
        "id": 1, 
        "name": "Sam", 
        "email": "sam@gmail.com" 
    },
    { 
        "id": 2, 
        "name": "Ram", 
        "email": "ram@gmail.com" 
    }
  ]
}

Get User by ID

Request:
GET: http://localhost:5000/users/1
Content-Type: application/json

Response:
Status: 200 OK
Content-Type: application/json

{
  "message": "User retrieved successfully",
  "data": {
    "id": 1,
    "name": "Sam",
    "email": "sam@gmail.com"
  }
}

Create New User

Request: 
POST: http://localhost:5000/users
Content-Type: application/json

{
  "name": "Alex",
  "email": "alex123@gmail.com"
}

Response:
Status: 201 Created
Content-Type: application/json

{
  "message": "User created successfully",
  "data": {
    "id": 3,
    "name": "Alex",
    "email": "alex123@gmail.com"
  }
}

Update User

Request:
PUT: http://localhost:5000/users/2
Content-Type: application/json

{
  "name": "Ram Kumar",
  "email": "ramkumar12@gmail.com"
}

Response:
Status: 200 OK
Content-Type: application/json

{
  "message": "User updated successfully",
  "data": {
    "id": 2,
    "name": "Ram Kumar",
    "email": "ramkumar12@gmail.com"
  }
}

Delete User

Request:
DELETE: http://localhost:5000/users/3
Content-Type: application/json

Response:
Status: 200 OK
Content-Type: application/json

{
  "message": "User deleted successfully"
}

Examples