Database
In the previous chapter, we have implemented a simple command line TODO app. However, the data is stored in a single unstructured JSON file. This means that in order to make some changes on the system we must read the whole file and write the whole file back. This has some drawbacks such as:
- Reading the whole file will be slow if the file is large.
- If file is too large fitting it into memory will be a problem.
- Writing the whole file back will be slow if the file is large.
- Writing to a file is an error-prone operation. (If the system crashes while writing, the file might be corrupted)
- It is hard to query the data.
In order to overcome these problems, we may have two options:
- Implementing more sophisticated file operations.
- 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.
Implementing with SQLite
We will use SQLite in this chapter becuase of its simplicity.
-- Create a TODO table
CREATE TABLE todos (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;
// File: src/index.ts
import process from 'process';
import TodoService from './TodoService';
import TodoSQLiteService from './TodoSQLiteService';
// Get environment variables
const DB_TYPE = process.env.DB_TYPE || 'sqlite';
// Get command line arguments
const [program, script, subcommand, ...args] = process.argv;
async function main() {
const todoService = DB_TYPE === 'sqlite' ? new TodoSQLiteService() : new TodoService();
await todoService.init();
switch (subcommand) {
case 'add': {
const [title, description] = args;
await todoService.addTodo(title, description);
}
case 'list': {
await todoService.listTodos();
break;
}
case 'done': {
const [id] = args;
await todoService.markTodoAsDone(id);
break;
}
case 'undone': {
const [id] = args;
await todoService.markTodoAsUndone(id);
break;
}
case 'delete': {
const [id] = args;
await todoService.deleteTodo(id);
break;
}
default:
// Print help messages
console.log(`Unknown subcommand`);
console.log(`Usage: tdr <subcommand> [args]`);
console.log(`Subcommands: add, list, done, undone, delete`);
// Exit with an error code
process.exit(1);
}
}
main();
// File: src/TodoSQLiteService.ts
import * as sqlite from "sqlite";
import sqlite3 from "sqlite3";
// Define what a todo looks like
export interface Todo {
id: string;
title: string;
description: string;
completed: boolean;
}
// Define the TodoService class
class TodoSQLiteService {
db: sqlite.Database | null = null;
constructor() {}
public async init() {
// 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 INTEGER PRIMARY KEY, title TEXT, description TEXT, done BOOLEAN);",
);
}
public async addTodo(title: string, description: string): Promise<void> {
if (!this.db) throw new Error("Database not initialized");
await this.db.run(
"INSERT INTO todos (title, description, done) VALUES (?, ?, ?);",
[title, description, false],
);
console.log(`Added todo: ${title}`);
}
public async listTodos() {
if (!this.db) throw new Error("Database not initialized");
const todos = await this.db?.all("SELECT * FROM todos;");
console.log(todos);
}
public async markTodoAsDone(id: string): Promise<void> {
if (!this.db) throw new Error("Database not initialized");
await this.db.run("UPDATE todos SET done = ? WHERE id = ?;", [true, id]);
console.log(`Marked todo as done: ${id}`);
}
public async markTodoAsUndone(id: string): Promise<void> {
if (!this.db) throw new Error("Database not initialized");
await this.db.run("UPDATE todos SET done = ? WHERE id = ?;", [false, id]);
console.log(`Marked todo as undone: ${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]);
console.log(`Deleted todo: ${id}`);
}
}
export default TodoSQLiteService;
Implementing Validation
We will add these 3 validation rules to our application:
- Title must be at least 3 characters.
- Description must be at least 3 characters.
In order to implement these validation rules we need to add the logic to TodoService.ts file which is our custom
database.