Database Backup CLI

A modular, extensible command-line utility for backing up and restoring databases. Built with Node.js and TypeScript, the project was designed with a strong emphasis on Low-Level Design (LLD) — applying clean architecture principles and well-known design patterns to produce a codebase that is easy to extend, maintain, and reason about.

Project requirements sourced from roadmap.sh — Database Backup Utility.

---

Table of Contents

• What It Does
• Design Focus: Low-Level Design
• Design Patterns Used
• Project Structure
• Running the Test Databases with Docker
• Installation
• Command Reference
• Extending the Project

---

What It Does

• Creates full database backups via CLI, with support for both local and Dockerized databases.
• Compresses backup files (currently Gzip) to minimize storage usage.
• Stores backup files to a configured storage destination (currently local filesystem).
• Restores a database from a previously created backup file.
• Schedules automatic backups using cron expressions.
• Optionally sends email notifications on backup/restore events.
• Logs all operations to console and rotating log files via Winston.

---

Design Focus: Low-Level Design

The primary goal of this project was not just to ship a working tool, but to practice and demonstrate solid Low-Level Design decisions. Every part of the system was designed with the following principles:

• Abstraction over implementation: All major subsystems (database, compression, storage, notifications, scheduling) are defined as interfaces (contracts). The core business logic depends only on these interfaces, never on concrete classes.
• Open/Closed Principle: The system is open for extension (add a new database type) but closed for modification (no existing code needs to change).
• Single Responsibility Principle: Each class has exactly one reason to change. Commands, services, adapters, and factories are all separate concerns.
• Dependency Inversion: High-level services depend on abstractions, not on low-level modules.

---

Design Patterns Used

1. Factory Pattern

Where: DatabaseFactory, CompressionFactory, StorageFactory, NotificationFactory, SchedulerFactory

What it does: Each factory encapsulates the object-creation logic for a category of components. The caller provides a type string; the factory instantiates and returns the correct implementation.

Example:

// DatabaseFactory.ts
static create(type: DatabaseType, config: DatabaseConfig): IDatabaseAdapter {
  switch (type) {
    case DatabaseType.MYSQL:    return new MysqlAdapter(config);
    case DatabaseType.POSTGRES: return new PostgresAdapter(config);
    default: throw new Error(`Unsupported database type: ${type}`);
  }
}

Why it matters: The command layer never calls new MysqlAdapter() directly. Adding MongoDB support tomorrow requires only a new adapter class and a single new case in DatabaseFactory — nothing else changes.

---

2. Adapter Pattern

Where: MysqlAdapter, PostgresAdapter, GzipCompressionProvider, LocalStorageProvider, EmailNotifier, CronScheduler

What it does: Each external dependency (MySQL driver, pg, nodemailer, node-cron) is wrapped behind a common interface. The rest of the application only sees the interface, never the third-party library.

Example:

// IDatabaseAdapter contract
export interface IDatabaseAdapter {
  testConnection(): Promise<void>;
  backup(): Promise<string>;
  restore(backupFile: string): Promise<void>;
}

// MysqlAdapter fulfills the contract using mysql2 internally
export class MysqlAdapter extends BaseDatabaseAdapter {
  async backup(): Promise<string> {
    /* uses mysqldump */
  }
  async restore(fileName: string): Promise<void> {
    /* uses mysql CLI */
  }
}

Why it matters: Swapping mysql2 for another driver, or replacing the Gzip library, does not affect any service or command.

---

3. Template Method Pattern

Where: BaseDatabaseAdapter

What it does: The abstract base class defines the default behavior for all database operations (throwing NotImplementedException). Concrete adapters override only the methods they support.

Example:

// BaseDatabaseAdapter.ts
export abstract class BaseDatabaseAdapter implements IDatabaseAdapter {
  async backup(): Promise<string> {
    throw new NotImplementedException("backup");
  }
  async restore(backupFile: string): Promise<void> {
    throw new NotImplementedException("restore");
  }
}

// MysqlAdapter.ts — overrides only what MySQL supports
export class MysqlAdapter extends BaseDatabaseAdapter {
  async backup(): Promise<string> {
    /* concrete implementation */
  }
  async restore(fileName: string): Promise<void> {
    /* concrete implementation */
  }
}

Why it matters: New database adapters can be added with minimal boilerplate. Any operation not yet implemented throws a clear, descriptive error rather than failing silently.

---

4. Command Pattern

Where: BackupCommand, RestoreCommand, TestConnectionCommand, ScheduleCommand

What it does: Each CLI action is encapsulated as a command object that implements ICommand. The execute() method contains the full logic for that operation, including input validation, factory calls, and service invocation.

Example:

// ICommand contract
export interface ICommand {
  execute(): Promise<void>;
}

// BackupCommand.ts
export class BackupCommand implements ICommand {
  async execute(): Promise<void> {
    const databaseAdapter = DatabaseFactory.create(dbType, dbConfig);
    const compressionProvider = CompressionFactory.create(compressType);
    const storageProvider = StorageFactory.create(storageType);
    const backupService = new BackupService(...);
    await backupService.execute(backupType);
  }
}

Why it matters: Each command is isolated and testable independently. Adding a new CLI action means adding a new command class, not modifying index.ts business logic.

---

5. Strategy Pattern

Where: Implicit in the Factory + Interface combination across all providers (compression, storage, notification)

What it does: The concrete algorithm (how to compress, where to store, how to notify) is interchangeable at runtime based on the CLI option provided. The BackupService receives an ICompressionProvider and does not care whether it is Gzip or Zip.

Why it matters: Behavior is configurable via CLI flags. Users can combine any database type with any storage or compression type without code changes.

---

Project Structure

src/
  commands/           # CLI command handlers (Command Pattern)
  core/
    contracts/        # Interfaces defining all subsystem boundaries
    enums/            # Typed enumerations for all configurable options
    exceptions/       # Custom exception types
    factories/        # Object creation (Factory Pattern)
    models/           # Plain data models (DatabaseConfig, BackupResult)
    services/         # Core business logic (BackupService, RestoreService)
  adapters/
    databases/        # Database adapters (MySQL, PostgreSQL)
    compression/      # Compression adapters (Gzip)
    storage/          # Storage adapters (Local filesystem)
    notifications/    # Notification adapters (Email)
    scheduler/        # Scheduler adapters (Cron)
  infrastructure/
    config/           # Environment variable access (AppConfig)
    logger/           # Winston logger implementation

---

Running the Test Databases with Docker

A docker-compose.yml is included to spin up a MySQL and a PostgreSQL instance for local testing. No manual database setup is required.

Start the containers:

docker-compose up -d

This will start:

Service	Container	Host Port	Credentials	Database
MySQL 8	test-mysql	5555	user: test / pw: test	mysqlTestDb
PostgreSQL	test-postgres	5556	user: test / pw: test	postgresTestDb

Stop the containers:

docker-compose down

Stop and remove all data volumes:

docker-compose down -v

---

Installation

git clone <repository-url>
cd databaseBackup
npm install

---

Command Reference

All commands are run via:

npm run start -- <command> [options]

---

test-connection

Verify that the provided credentials can reach the database.

npm run start -- test-connection \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb

npm run start -- test-connection \
  -t postgres \
  -H localhost \
  -p 5556 \
  -u test \
  -P test \
  -d postgresTestDb

---

backup

Create a compressed backup of the database and save it to local storage.

With Local Database:

npm run start -- backup \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  -c gzip \
  -s local

With Docker (database inside a container):

npm run start -- backup \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  -c gzip \
  -s local \
  --docker \
  --container-name test-mysql

With email notification on completion:

npm run start -- backup \
  -t postgres \
  -H localhost \
  -p 5556 \
  -u test \
  -P test \
  -d postgresTestDb \
  -c gzip \
  -s local \
  -n email

---

restore

Restore a database from a previously created backup file.

With Local Database:

npm run start -- restore \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  -c gzip \
  -s local \
  -f mysqlTestDb-1718000000000.sql.gz

With Docker (database inside a container):

npm run start -- restore \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  -c gzip \
  -s local \
  -f mysqlTestDb-1718000000000.sql.gz \
  --docker \
  --container-name test-mysql

From inside a Docker container:

npm run start -- restore \
  -t postgres \
  -H localhost \
  -p 5556 \
  -u test \
  -P test \
  -d postgresTestDb \
  -f postgresTestDb-1718000000000.sql.gz \
  --docker \
  --container-name test-postgres

---

schedule

Schedule automatic backups using a cron expression. The process stays alive and runs the backup on the defined interval.

With Local Database:

# Run a backup every day at 2:00 AM
npm run start -- schedule \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  --cron "0 2 * * *"

With Docker (database inside a container):

# Run a backup every day at 2:00 AM
npm run start -- schedule \
  -t mysql \
  -H localhost \
  -p 5555 \
  -u test \
  -P test \
  -d mysqlTestDb \
  --cron "0 2 * * *" \
  --docker \
  --container-name test-mysql

# Run a backup every hour
npm run start -- schedule \
  -t postgres \
  -H localhost \
  -p 5556 \
  -u test \
  -P test \
  -d postgresTestDb \
  --cron "0 * * * *" \
  --docker \
  --container-name test-postgres

Common cron expressions:

Expression	Meaning
0 2 * * *	Every day at 2:00 AM
0 * * * *	Every hour
*/15 * * * *	Every 15 minutes
0 0 * * 0	Every Sunday at midnight

---

Extending the Project

The architecture is designed so that adding new capabilities requires writing new code, not modifying existing code.

Adding a New Database Type (e.g., MongoDB)

1. Add MONGODB = "mongodb" to src/core/enums/databaseType.enum.ts.
2. Create src/adapters/databases/mongodb.adapter.ts that extends BaseDatabaseAdapter.
3. Add one case to DatabaseFactory:

case DatabaseType.MONGODB:
  return new MongodbAdapter(config);

No other file needs to change.

---

Adding a New Compression Algorithm (e.g., Zip)

1. Add ZIP = "zip" to src/core/enums/compressionType.enum.ts.
2. Create src/adapters/compression/zipCompression.provider.ts implementing ICompressionProvider.
3. Add one case to CompressionFactory.

---

Adding a New Storage Backend (e.g., AWS S3)

1. Add S3 = "s3" to src/core/enums/storageType.enum.ts.
2. Create src/adapters/storage/s3Storage.provider.ts implementing IStorageProvider.
3. Add one case to StorageFactory.

---

Adding a New Notification Channel (e.g., Slack)

1. Add SLACK = "slack" to src/core/enums/notificationType.enum.ts.
2. Create src/adapters/notifications/slackNotifier.adapter.ts implementing INotificationProvider.
3. Add one case to NotificationFactory.

In every case, the services, commands, and CLI entry point remain completely untouched.