Database

In the previous chapter, we have implemented a simple command line TODO app and learned what is a layered architecture. However, the data is stored in a single unstructured JSON file is not the best way to store data. It has a lot of drawbacks such as:

• Adding, updating and deleting a single TODO item requires reading the whole file and writing the whole file back. If we have a large file, this will be very slow.
• If process crashes while writing the file, the file might be corrupted.
• Querying the data requires reading the whole file.
• If file is too large fitting it into memory will be a problem.
• Writing to a file is an error-prone operation. (If the system crashes while writing, the file might be corrupted)

In order to overcome these problems, we may have two options:

1. Implementing more sophisticated file operations.
2. Using a database.

The first solution will be hard to implement. For this reason, we will use a database in this chapter. However, in preceding chapters, we will also implement a simple file-based database in order to grasp the basics of databases. Without changing our functional requirements, we will refactor our code to store TODO items in a database. We will use SQLite because of its simplicity.

Single Responsibility Principle Revisited

Fortunately, we have separated our data access logic from the rest of our application. Therefore, we can easily switch to a different database system without changing the rest of our application. This is the Single Responsibility Principle in action. We should have only one reason to change our code. We have 3 parts and we have a single change that we want to make. Therefore, according to the Single Responsibility Principle, we should only change one part of our code. Let's look at the parts of our application and see what we need to change.

• Presentation Layer: Changing database is not a thing that user sees or interacts with. Therefore, this layer should not be affected by the change.
• Business Logic Layer: This layer is responsible for the business logic of our application. Our business logic which is mostly described by functional requirements of the previous chapter does not include any change related to the database. Therefore, this layer also should not be affected by the change.
• Data Access Layer: This layer is responsible for the data access logic of our application. This is the layer that we need to change. We can reimplement this layer for SQLite.

It looks clean right? But unfortunately, it is not the case. We have a problem. Our business logic layer is not separated from the data access layer properly. In other words, it is coupled to the data access layer in a way. Lets look at the problematic part of our business logic layer:

  public async addTodo(title: string, description: string): Promise<void> {
    const todoList = await this.repository.readTodos();

    ...

    todoList.push(newTodo);

    await this.repository.writeTodos(todoList);
  }

Do you see the problem? This service layer assumes that the data stored in the repository may be read completely and written completely. Lets assume that we have a database with millions of records. Reading and writing all of them at once is not a good idea. We should read and write only the necessary parts of the data. Therefore, we need to change our service layer to work with the data access layer in a more granular way. We need to change our service layer and data access layer in order to get decoupled layers. Let's start with the repository.

import fs from 'fs';

export interface Todo {
  id: string;
  title: string;
  description: string;
  completed: boolean;
}

class TodoRepository {
  fileName: string = 'todos.json';

  constructor() {}

  // Lets make this method private
  private async readTodos(): Promise<Todo[]> {
    try {
      await fs.promises.stat('todos.json');
    } catch {
      await fs.promises.writeFile('todos.json', '[]');
    }

    const fileConent = await fs.promises.readFile('todos.json', 'utf-8');
    const todoList = JSON.parse(fileConent) as Todo[];

    return todoList;
  }

  // Also this one
  private async writeTodos(todoList: Todo[]): Promise<void> {
    const updatedFileContent = JSON.stringify(todoList, null, 2);
    await fs.promises.writeFile(this.fileName, updatedFileContent);
  }

  public async createTodo(todo: Todo): Promise<void> {
    const todoList = await this.readTodos();
    todoList.push(todo);
    await this.writeTodos(todoList);
  }

  public async deleteTodo(id: string): Promise<void> {
    const todoList = await this.readTodos();
    const index = todoList.findIndex((todo) => todo.id === id);
    if (index === -1) throw new Error('Todo not found');
    todoList.splice(index, 1);
    await this.writeTodos(todoList);
  }

  public async listTodos(): Promise<Todo[]> {
    const todoList = await this.readTodos();
    return todoList;
  }

  // Only update provided fields
  public async updateTodo(id: string, todo: Partial<Todo>): Promise<void> {
    const todoList = await this.readTodos();
    const index = todoList.findIndex((todo) => todo.id === id);
    if (index === -1) throw new Error('Todo not found');
    todoList[index] = { ...todoList[index], ...todo };
    await this.writeTodos(todoList);
  }
}

export default TodoRepository;

Lets refactor TodoService to work with the new TodoRepository:

import TodoRepository, { Todo } from './TodoRepository';
import crypto from 'crypto';

// Define the TodoService class
class TodoService {
  repository: TodoRepository;

  constructor() {
    this.repository = new TodoRepository();
  }

  public async addTodo(title: string, description: string): Promise<void> {
    // Validate the input
    if (!title || typeof title !== 'string' || title.length < 2 || title.length > 255) throw new Error('Title is required and its length must be between 2 and 255 characters');
    if (description && (typeof description !== 'string' || description.length > 4096)) throw new Error('Description length must be less than 4096 characters');

    // Generate a new todo
    const id = crypto.randomBytes(4).toString('hex');
    const newTodo: Todo = {
      id: id,
      title,
      description,
      completed: false,
    };

    await this.repository.createTodo(newTodo);
  }

  public async listTodos(): Promise<Todo[]> {
    return await this.repository.listTodos();
  }

  public async markTodoAsDone(id: string): Promise<void> {
    await this.repository.updateTodo(id, { completed: true });
  }

  public async markTodoAsUndone(id: string): Promise<void> {
    await this.repository.updateTodo(id, { completed: false });
  }

  public async deleteTodo(id: string): Promise<void> {
    await this.repository.deleteTodo(id);
  }
}

export default TodoService;

Do you see the difference? Our TodoService is almost the same as our functional requirements. Let me remind you the functional requirements:

1. As a user, I should be able to list all of the todos that is created before.
2. As a user, I should be able to add a new todo to the list.
   • The todo should have a unique identifier.
   • Title of the todo must be a string and it is required.
     • Maximum length of the title is 255 characters.
     • Minimum length of the title is 2 character.
   • Description of the todo must be a string and it is optional.
     • Maximum length of the description is 4096 characters.
   • The todo should have a status, it is either done or undone.
3. As a user, I should be able to mark a todo as done.
4. As a user, I should be able to mark a todo as undone.
5. As a user, I should be able to delete a todo from the list.

Right now, we have completely separated our business logic from the data access logic. You should be very careful about this kind of leaks. You need to be sure that your code really follows the Single Responsibility Principle. If you are not sure, you can always ask yourself the following questions:

1. If I need to change a part of my code, how many parts of my code should be changed ?
2. What is the definition of the part that I am looking at?
3. Does the definition of the part that I am looking match with the implementation of the part?

Implementing SQLite Repository

npm install sqlite sqlite 3

-- Create a TODO table
CREATE TABLE todos (id TEXT, title TEXT, description TEXT, done BOOLEAN);

-- We can drop a table by using DROP TABLE statement
DROP TABLE todos;

-- We can update the definition of a table by using ALTER TABLE statement
ALTER TABLE todos ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT;

-- We can insert data into a table by using INSERT INTO statement
INSERT INTO todos (title, description, done) VALUES ('A sample task', 'This is a sample task', 0);

-- We can select data from a table by using SELECT statement
SELECT * FROM todos;
SELECT id, title FROM todos;
SELECT id, title FROM todos WHERE done = 0;
SELECT id, title FROM todos WHERE done = 0 ORDER BY id DESC;
SELECT id, title FROM todos WHERE done = 0 ORDER BY id DESC LIMIT 1;

-- We can update data in a table by using UPDATE statement
UPDATE todos SET done = 1 WHERE id = 1;

-- We can delete data from a table by using DELETE statement
DELETE FROM todos WHERE id = 1;

-- We can delete all data from a table by using DELETE statement without WHERE clause
DELETE FROM todos;

Lets rename our TodoRepository.ts file to TodoFSRepository.ts and create a new file TodoSQLiteRepository.ts with the following content:

import * as sqlite from 'sqlite';
import sqlite3 from 'sqlite3';

export interface Todo {
  id: string;
  title: string;
  description: string;
  completed: boolean;
}

class TodoSQLiteRepository {
  db: sqlite.Database | null = null;

  constructor() {}

  private async getDatabase() {
    if (this.db) return this.db;

    // Open the database
    this.db = await sqlite.open({
      filename: 'todos.db',
      driver: sqlite3.Database,
    });

    // Create the table if it doesn't exist
    await this.db.run('CREATE TABLE IF NOT EXISTS todos (id TEXT PRIMARY KEY, title TEXT, description TEXT, done BOOLEAN);');

    // Return the database
    return this.db;
  }

  public async createTodo(todo: Todo): Promise<void> {
    const db = await this.getDatabase();
    await db.run('INSERT INTO todos (id, title, description, done) VALUES (?, ?, ?, ?);', [todo.id, todo.title, todo.description, todo.completed]);
  }

  public async listTodos(): Promise<Todo[]> {
    const db = await this.getDatabase();
    return await db.all('SELECT * FROM todos;');
  }

  public async updateTodo(id: string, todo: Partial<Todo>): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');
    if (!id) throw new Error('ID is required');
    if (todo.description) await this.db.run('UPDATE todos SET description = ? WHERE id = ?;', [todo.description, id]);
    if (todo.completed) await this.db.run('UPDATE todos SET done = ? WHERE id = ?;', [todo.completed, id]);
    if (todo.title) await this.db.run('UPDATE todos SET title = ? WHERE id = ?;', [todo.title, id]);
  }

  public async deleteTodo(id: string): Promise<void> {
    if (!this.db) throw new Error('Database not initialized');

    await this.db.run('DELETE FROM todos WHERE id = ?;', [id]);
  }
}

export default TodoSQLiteRepository;

Combining two repositories (Wrong way)

import process from 'process';
import TodoFSRepository, { Todo } from './TodoFSRepository';
import TodoSQLiteService from './TodoSQLiteRepository';

export { Todo } from './TodoFSRepository';

class TodoRepository {
  fsRepository: TodoFSRepository | null = null;
  sqliteRepository: TodoSQLiteService | null = null;

  constructor() {
    if (process.env.DB === 'sqlite') {
      this.sqliteRepository = new TodoSQLiteService();
    } else {
      this.fsRepository = new TodoFSRepository();
    }
  }

  public async createTodo(todo: Todo): Promise<void> {
    if (this.sqliteRepository) {
      return this.sqliteRepository.createTodo(todo);
    }
    if (this.fsRepository) {
      return this.fsRepository.createTodo(todo);
    }
    throw new Error('No repository found');
  }

  public async deleteTodo(id: string): Promise<void> {
    if (this.sqliteRepository) {
      return this.sqliteRepository.deleteTodo(id);
    }
    if (this.fsRepository) {
      return this.fsRepository.deleteTodo(id);
    }
    throw new Error('No repository found');
  }

  public async listTodos(): Promise<Todo[]> {
    if (this.sqliteRepository) {
      return this.sqliteRepository.listTodos();
    }
    if (this.fsRepository) {
      return this.fsRepository.listTodos();
    }
    throw new Error('No repository found');
  }

  public async updateTodo(id: string, todo: Partial<Todo>): Promise<void> {
    if (this.sqliteRepository) {
      return this.sqliteRepository.updateTodo(id, todo);
    }
    if (this.fsRepository) {
      return this.fsRepository.updateTodo(id, todo);
    }
    throw new Error('No repository found');
  }
}

export default TodoRepository;

Combining two repositories (Right way)

Summary

Exercises

1. Implement an InMemoryRepository adapter that implements the Repository interface. This adapter should store the data in memory.