How to Build a Simple REST API with Node.js and SQLite (for Beginners)

Step 0: Let’s Get Ready

Alright kiddo, before we start coding, let’s make sure we have a few things ready:

1. Node.js and npm installed. You can check like this:

node -v
npm -v

2. A code editor like VS Code (it’s like your trusty notebook for coding).

3. A good mood and a cup of coffee (or tea ☕) because we’re going to have fun!

Think of this like cooking: we need our ingredients ready before we start mixing things together.

Step 1: Make a Folder for Our Project

Just like having a kitchen, we need a place to cook:

mkdir simple-rest-api
cd simple-rest-api

Now let’s tell Node.js we have a new project:

npm init -y

This will create a file called package.json—think of it as your recipe card for this project.

Step 2: Install Some Helpful Tools

We’re going to need a few “tools” to help us build our API:

npm install express sqlite3 body-parser

• Express → our helper to make the API run smoothly

• SQLite3 → our little database to store information

• body-parser → to read what people send to our API

It’s like having a spatula, a pan, and a measuring cup in the kitchen.

Step 3: Create the Main File

Now we’re ready to start cooking! Create a file called server.js:

const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();

const app = express();
const PORT = 3000;

// Middleware
app.use(bodyParser.json());

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

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

// Test route
app.get('/', (req, res) => {
    res.send('Hello, young coder! Your REST API is working. 😊');
});

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

Run the server:

node server.js

Open http://localhost:3000/ in your browser. You should see:
“Hello, young coder! Your REST API is working. 😊”

See? Easy peasy!

Step 4: Let’s Make Our API Useful

Now, we want to store some user information. Let’s make some routes to Create, Read, Update, and Delete (CRUD) users.

1. See 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(rows);
    });
});

2. Add a new user

app.post('/users', (req, res) => {
    const { name, email } = req.body;
    db.run('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ id: this.lastID, name, email });
    });
});

3. See a user by ID

app.get('/users/:id', (req, res) => {
    const { id } = req.params;
    db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json(row);
    });
});

4. Update a user

app.put('/users/:id', (req, res) => {
    const { id } = req.params;
    const { name, email } = req.body;
    db.run('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ updatedID: id, name, email });
    });
});

5. Delete a user

app.delete('/users/:id', (req, res) => {
    const { id } = req.params;
    db.run('DELETE FROM users WHERE id = ?', [id], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ deletedID: id });
    });
});

Step 5: Test It Out

Now, grab Postman or curl and try your API:

curl http://localhost:3000/users

Add a new user:

curl -X POST http://localhost:3000/users -H "Content-Type: application/json" -d '{"name":"Alice","email":"alice@example.com"}'

See that? You just added your first user! 🎉

Step 6: What Next, Young Coder?

• Try adding validation to check if the user entered a valid email

• Organize your routes using Express Router

• Explore other SQLite features to store more complex data

Remember, little by little, step by step—coding is like learning to ride a bike. You may fall sometimes, but you’ll get better and faster every day! 🚴‍♂️