Node.js + Built-in SQLite Crash Course Project

This project demonstrates a Node.js API using the built-in Node.js SQLite module (DatabaseSync) with a MVC structure, similar to a typical MERN stack project. It includes basic CRUD operations for a users table.

---

Project Structure

node-sqlite-crash/
├── package.json
├── server.js # Express server entry point
├── db/
│ └── database.js # SQLite database connection and schema creation
├── models/
│ └── userModel.js # Model layer: SQL queries / mini ORM
├── controllers/
│ └── userController.js # Controller layer: Handles HTTP requests
└── routes/
└── userRoutes.js # Route layer: Maps endpoints to controllers

---

How it Works

1. Database Initialization (db/database.js)

• Uses Node built-in DatabaseSync to create a SQLite database file (data.db) and a users table.
• Example schema:

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
)

• The database.js file ensures the table exists when the server starts.

---

2. Models (models/userModel.js)

• Functions (or classes) to interact with the database.

• Examples:

  • createUser(name, email) → Inserts a new user
  • getAllUsers() → Returns all users
  • getUserById(id) → Returns a single user by ID
  • updateUser(id, name, email) → Updates user info
  • deleteUser(id) → Deletes a user

Note: Unlike Mongoose, we write raw SQL queries because there’s no built-in ORM in Node.js.

---

3. Controllers (controllers/userController.js)

• Receives HTTP requests and calls model functions.
• Handles responses, errors, and validation.

---

4. Routes (routes/userRoutes.js)

• Maps HTTP endpoints to controller functions:

HTTP Method	Endpoint	Action
GET	/api/users	Get all users
GET	/api/users/:id	Get user by ID
POST	/api/users	Create a new user
PUT	/api/users/:id	Update a user
DELETE	/api/users/:id	Delete a user

---

5. Server (server.js)

• Express server with JSON body parsing:

app.use(express.json());
app.use("/api/users", userRoutes);

• Starts on port 3000.

---

How to Run

1. Install dependencies:

pnpm install express

2. Start the server:

node --watch server.js

• The database file data.db will be automatically created in db/ folder.
• Warning about experimental SQLite is normal in Node v22+.

---

cURL Commands to Test API

1. Get all users

curl -X GET http://localhost:3000/api/users

2. Create a new user

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

3. Get a user by ID

curl -X GET http://localhost:3000/api/users/1

4. Update a user

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

5. Delete a user

curl -X DELETE http://localhost:3000/api/users/1

---

Notes & Tips

• ID behavior: SQLite auto-increment IDs do not reuse deleted IDs.
• Database file: data.db can be ignored in Git (.gitignore) for production.
• Mini ORM: You can wrap models in classes to mimic Mongoose-style syntax if desired.
• VS Code: You can view the database using a SQLite extension.
• MVC structure: Works the same as Mongo/Mongoose projects; only model implementation differs.

---

Summary

This project shows:

• How to structure a Node.js + SQLite API using MVC.
• How to perform CRUD operations with built-in SQLite.
• How SQL differs from Mongo: more manual queries, no built-in ORM.
• How to test endpoints with cURL commands.

It’s a great starting point to learn Node.js APIs without external ORMs while maintaining a familiar MVC structure.