Implementing SQLite3 with Node.js and TypeScript

Introduction

Node.js has become a popular choice for building server-side applications, and when it comes to working with databases, SQLite3 is a lightweight and efficient option. In this article, we will explore how to use and implement SQLite3 with Node.js, utilizing TypeScript for strong type checking. We will ensure that our Node.js application follows best practices and SOLID principles, including the use of controllers and routers. Additionally, we will cover creating a sample Node.js app with TypeScript, installing the SQLite3 library, implementing CRUD operations, and handling database migrations.

Creating a Sample Node.js App with TypeScript

Before diving into the implementation of SQLite3, let's start by creating a basic Node.js application using TypeScript. Follow these steps:

Initialize a new Node.js project:
```
 npm init -y
```
Install TypeScript as a development dependency:
```
 npm install typescript --save-dev
```
Create a TypeScript configuration file (tsconfig.json) by running:
```
 npx tsc --init
```
Adjust the tsconfig.json to suit your project's needs.
Create a source directory and an entry file for your application, e.g., app.ts.

Now you have a basic TypeScript project structure in place.

Installing SQLite3 Library

Next, we need to install the SQLite3 library for Node.js. To do this, execute the following command:

npm install sqlite3

This will add the SQLite3 library as a dependency to your project.

Implementing SQLite3 CRUD Operations

Let's implement basic CRUD (Create, Read, Update, Delete) operations using SQLite3 in our Node.js application.

1. Import the required modules and set up the SQLite database:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

const db = await open({
  filename: './mydatabase.db', // Specify the database file
  driver: sqlite3.Database,
});

2. Create a controller and router for handling CRUD operations:

For best practices and SOLID principles, it's a good idea to create separate controller and router files for each entity (e.g., users, products). Here's a simplified example for a "tasks" entity:

taskController.ts

import { Database, Statement } from 'sqlite';

class TaskController {
  constructor(private db: Database) {}

  async createTask(task: Task): Promise<Task> {
    const { title, description } = task;
    const sql = 'INSERT INTO tasks (title, description) VALUES (?, ?)';

    const result = await this.db.run(sql, [title, description]);
    if (result.lastID) {
      const createdTask = await this.getTaskById(result.lastID);
      return createdTask!;
    } else {
      throw new Error('Failed to create task');
    }
  }

  async getTaskById(id: number): Promise<Task | undefined> {
    const sql = 'SELECT * FROM tasks WHERE id = ?';
    const row = await this.db.get(sql, [id]);
    return row as Task | undefined;
  }

  async updateTask(id: number, updatedTask: Task): Promise<Task | undefined> {
    const { title, description } = updatedTask;
    const sql = 'UPDATE tasks SET title = ?, description = ? WHERE id = ?';

    const result = await this.db.run(sql, [title, description, id]);
    if (result.changes > 0) {
      return updatedTask;
    } else {
      throw new Error('Task not found or update failed');
    }
  }

  async deleteTask(id: number): Promise<void> {
    const sql = 'DELETE FROM tasks WHERE id = ?';
    const result = await this.db.run(sql, [id]);
    if (result.changes === 0) {
      throw new Error('Task not found or deletion failed');
    }
  }
}

export default TaskController;

taskRouter.ts

import express, { Request, Response } from 'express';
import TaskController from './taskController';

const router = express.Router();

router.post('/tasks', async (req: Request, res: Response) => {
  // Handle task creation
});

router.get('/tasks/:id', async (req: Request, res: Response) => {
  // Handle task retrieval
});

router.put('/tasks/:id', async (req: Request, res: Response) => {
  // Handle task update
});

router.delete('/tasks/:id', async (req: Request, res: Response) => {
  // Handle task deletion
});

export default router;

3. Use the controller and router in your main application file (app.ts):

import express from 'express';
import TaskController from './taskController';
import taskRouter from './taskRouter';

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

// Initialize the database connection

const taskController = new TaskController(db);

app.use(express.json());
app.use(taskRouter);

app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

Database Migrations with SQLite3

To handle database migrations with SQLite3, you can use a package like sqlite-migrations. Here's a high-level overview of how to set it up:

Install sqlite-migrations as a development dependency:

npm install sqlite-migrations --save-dev

Create a directory for your migration files, e.g., migrations.
Define your migration files in the migrations directory, following a naming convention like 001-initial-schema.sql, 002-add-tasks-table.sql, etc.
Create a script to run the migrations in your package.json:

"scripts": {
  "migrate": "sqlite-migrate ./migrations ./mydatabase.db"
}

Run the migrations using the following command:

npm run migrate

Conclusion

In this article, we've explored how to use and implement SQLite3 with Node.js using TypeScript while following best practices and SOLID principles. We've created a sample Node.js app, installed the SQLite3 library, implemented CRUD operations, and discussed handling database migrations. This foundation will help you build robust and maintainable Node.js applications with SQLite3.

My next article will be about using Sequelize on the same architecture. For those who are curious, please stay tuned :)

Please click here for Seuelize article

Koray's Coding Diary