Implementing the Persistence Layer - Part 1: Database

Implementing the Persistence Layer - Part 1: Database

Clean Architecture Series - Frameworks Layer

In Clean Architecture, the persistence layer is located at the frameworks layer operating as an intermediary between the data storage method and the application. It makes sure that the entities and business rules are kept apart from any particular framework or technology.

Also, the connections with databases, external APIs, and other mechanisms for data persistence are contained inside the persistence layer.

Database interface and result type

Firstly, go to the frameworks folder and create a new folder named "persistence" with a subfolder named "db" into the frameworks folder.

Then, create a file named db/database-result.type.ts

// ./src/frameworks/persistence/db/database-result.type.ts
export type DatabaseResult = {
  data: any;

After that, create another file named db/database.interface.ts:

// ./src/frameworks/persistence/db/database-result.type.ts
import { DatabaseResult } from "./database-result.type";

export interface IDatabase {
  connect: () => Promise<void>;
  disconnect: () => Promise<void>;
  query: (sql: string, ...args: any) => Promise<DatabaseResult>;

Let's explain the interface:

connect: The connect method allows to implement the database connection details.

disconnect: The disconnect method allows to implement how to terminate an open connection to a database.

query: The query method allows to implement how to send SQL queries to retrieve or manipulate data.

By using an interface, we can ensure that any class implementing the IDatabase interface adheres to the defined contract. This helps maintain consistency and allows us to easily switch between different database without changing the other parts of the codebase that depend on the interface.

Sqlite Database

In our case we are using sqlite3 as a database. Sqlite is a simple database which not required configuration, setup or administration.

So, to implement the database we will create a folder named "sqlite" inside the "persistence/db" folder. This folder allows to organize all related to the sqlite database implementation details.

Policy Table

As sqlite is an SQL Database Engine, we need to create a table to persist the Policy information. So, create the "sqlite/policy.table.ts" file with the following structure:

export const POLICY_TABLE_SQL = `
  name VARCHAR(200) NOT NULL,
  age INTEGER,
  smoker INTEGER,
  sedentary INTEGER,
  start_date TEXT NOT NULL,
  price INTEGER

By using "CREATE TABLE IF NOT EXISTS" we prevent drop the table and lost the previous data.

Policy Model

Also, we have to create a model which represent the data structure of the policy table. So, create a file named db/sqlite/policy.model.ts:

export type PolicyModel = {
  id: number;
  name: string;
  age: number;
  smoker: boolean;
  sedentary: boolean;
  price: number;
  start_date: string;

Database implementation

Then, create the db/sqlite/sqlite.db.ts file to write the sqlite database details:

import { Database } from "sqlite3";
import { IDatabase } from "../database.interface";
import { POLICY_TABLE_SQL } from "./policy.table";
import { DatabaseResult } from "../database-result.type";

export class SqliteDb implements IDatabase {
  private static instance: SqliteDb;
  private db: Database;

  private constructor() {
    this.db = new Database("db.sqlite");

  static getInstance() {
    if (!SqliteDb.instance) {
      this.instance = new SqliteDb();
    return this.instance;

  async connect(): Promise<void> {
    console.log("Checking SqliteDb integrity");
    this.db.exec("PRAGMA integrity_check");
    console.log("SqliteDb connected");

  async disconnect(): Promise<void> {
    console.log("SqliteDb disconnected");

  async query(sql: string, params?: []): Promise<DatabaseResult> {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (error, rows) => {
        if (error) {
        } else {
          resolve({ data: rows });

We are using singleton design pattern to assures we only have a single instance available throughout the application.

Let's see the SqliteDb implementation details:

constructor: The private constructor allows to have control about the instantiation, hiding the constructor of the class and force to use the static getInstance() method to get the SqliteDb instance.

getInstance: The static getInstanceMethod is responsable to handle the single instance of the SqliteDb. When there are not an instance, it create a new instance and execute the sql statement to create the Policy table. On the other hand, if there is an instance, it return the instance without creating a new one.

connect: The connect method has the connection implementation details, in this case, we are using the connect method to run the sqlite "integrity_check pragma" as a connection test.

disconnect: The disconnect method close the database connection calling the sqlite close method.

query: The query method execute the SQL query into the sqlite database and return the data result as a Promise.

In conclusion, Clean Architecture makes it easy to replace data storage technologies without affecting the functionality of the entire application by isolating the persistence layer from the main business logic. It also improves testability and maintainability of the codebase and helps to keep a clear division of responsibilities.

See you in the part 2 of the persistence layer.

Did you find this article valuable?

Support Max Martínez Cartagena by becoming a sponsor. Any amount is appreciated!