Multi-Database Support

Overview

Duckling supports multiple isolated database replicas running on a single server instance. Each MySQL source database gets its own DuckDB file, connection pool, and sync service with complete isolation.

Key Features

Isolated Replicas

Each MySQL source gets its own DuckDB file

Zero Cross-Contamination

Separate connection pools per database

Unified Monitoring

All databases visible in one dashboard

Database Selector

Frontend UI dropdown to switch contexts

Configuration

databases.json Schema

Database configurations are stored in data/databases.json:

[
  {
    "id": "lms",
    "name": "LMS",
    "mysqlConnectionString": "mysql://user:pass@host:port/chitti_lms?dateStrings=true",
    "duckdbPath": "data/lms.db",
    "createdAt": "2025-11-06T18:58:36.480Z",
    "updatedAt": "2025-11-06T18:58:36.480Z",
    "s3": {
      "enabled": true,
      "bucket": "my-backups",
      "region": "us-east-1",
      "accessKeyId": "AKIA...",
      "secretAccessKey": "...",
      "pathPrefix": "lms/",
      "encryption": "client-aes256",
      "encryptionKey": "a3f1c2d4e5b6a7f8..."
    }
  },
  {
    "id": "chitti_common",
    "name": "Common",
    "mysqlConnectionString": "mysql://user:pass@host:port/chitti_common?dateStrings=true",
    "duckdbPath": "data/chitti_common.db",
    "createdAt": "2025-11-06T18:58:36.480Z",
    "updatedAt": "2025-11-06T18:58:36.480Z"
  }
]

The config file is hot-reloadable. Changes take effect immediately without server restart.

File Paths

Environment	Path
Host (for editing)	`./data/databases.json`
Docker Container	`/app/data/databases.json`
Volume Mount	`./data:/app/data`

Multi-Instance Architecture

Singleton Pattern Per Database

Each database gets isolated instances:

// DuckDB connections (one per database)
class DuckDBConnection {
  private static instances: Map<string, DuckDBConnection> = new Map();
  
  static getInstance(databaseId: string, duckdbPath: string): DuckDBConnection {
    if (!this.instances.has(databaseId)) {
      this.instances.set(databaseId, new DuckDBConnection(duckdbPath));
    }
    return this.instances.get(databaseId)!;
  }
}

// MySQL connections (one per database)
class MySQLConnection {
  private static instances: Map<string, MySQLConnection> = new Map();
  
  static getInstance(databaseId: string, connectionString: string): MySQLConnection {
    if (!this.instances.has(databaseId)) {
      this.instances.set(databaseId, new MySQLConnection(connectionString));
    }
    return this.instances.get(databaseId)!;
  }
}

// Sync services (one per database)
class SequentialAppenderService {
  private static instances: Map<string, SequentialAppenderService> = new Map();
  
  static getInstance(
    databaseId: string, 
    mysql: MySQLConnection, 
    duckdb: DuckDBConnection
  ): SequentialAppenderService {
    if (!this.instances.has(databaseId)) {
      this.instances.set(
        databaseId, 
        new SequentialAppenderService(mysql, duckdb)
      );
    }
    return this.instances.get(databaseId)!;
  }
}

Database Context Middleware

Every request is scoped to a database via middleware:

// src/middleware/database.ts
export const attachDatabaseContext = (req, res, next) => {
  const databaseId = req.query.db || 'default';
  const dbConfig = DatabaseConfigManager.getInstance().getDatabase(databaseId);
  
  if (!dbConfig) {
    return res.status(404).json({ error: 'Database not found' });
  }
  
  // Attach database-specific connections to request
  req.databaseId = databaseId;
  req.duckdb = DuckDBConnection.getInstance(databaseId, dbConfig.duckdbPath);
  req.mysql = MySQLConnection.getInstance(databaseId, dbConfig.mysqlConnectionString);
  
  next();
};

Database Management APIs

List All Databases

curl 'http://localhost:3001/api/databases' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Response:

[
  {
    "id": "lms",
    "name": "LMS",
    "mysqlConnectionString": "mysql://...",
    "duckdbPath": "data/lms.db",
    "createdAt": "2025-11-06T18:58:36.480Z",
    "updatedAt": "2025-11-06T18:58:36.480Z"
  }
]

Create Database

curl -X POST 'http://localhost:3001/api/databases' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "name": "My Database",
    "mysqlConnectionString": "mysql://user:pass@host:port/mydb"
  }'

The duckdbPath is automatically generated as data/{id}.db where {id} is derived from the database name.

Update Database

curl -X PUT 'http://localhost:3001/api/databases/lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "name": "LMS (Updated)",
    "mysqlConnectionString": "mysql://..."
  }'

Delete Database

curl -X DELETE 'http://localhost:3001/api/databases/lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Deleting a database removes the configuration but does not delete the DuckDB file. You must manually delete data/{id}.db if needed.

Test Connection

curl -X POST 'http://localhost:3001/api/databases/lms/test' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Response:

{
  "success": true,
  "message": "Connection successful",
  "tableCount": 45
}

Using Multi-Database in API Calls

All data endpoints accept the ?db={database_id} query parameter:

Sync Specific Database

# Full sync
curl -X POST 'http://localhost:3001/api/sync/full?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

# Incremental sync
curl -X POST 'http://localhost:3001/api/sync/incremental?db=chitti_common' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Query Specific Database

curl -X POST 'http://localhost:3001/api/query?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "sql": "SELECT COUNT(*) FROM User"
  }'

Get Tables from Specific Database

curl 'http://localhost:3001/api/tables?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Health Check Specific Database

curl 'http://localhost:3001/health?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Frontend Integration

Database Selector Component

The frontend provides a database selector in the header:

<!-- packages/frontend/app/components/DatabaseSelector.vue -->
<template>
  <select v-model="selectedDatabaseId" @change="handleChange">
    <option v-for="db in databases" :key="db.id" :value="db.id">
      {{ db.name }}
    </option>
  </select>
</template>

<script setup lang="ts">
import { useDatabase } from '~/composables/useDatabase';

const { selectedDatabaseId, databases, setDatabase } = useDatabase();

const handleChange = () => {
  setDatabase(selectedDatabaseId.value);
  // Reload page data for new database
  window.location.reload();
};
</script>

useDatabase Composable

// packages/frontend/app/composables/useDatabase.ts
export const useDatabase = () => {
  const selectedDatabaseId = ref(localStorage.getItem('selectedDatabase') || 'default');
  const databases = ref<DatabaseConfig[]>([]);
  
  const setDatabase = (id: string) => {
    selectedDatabaseId.value = id;
    localStorage.setItem('selectedDatabase', id);
  };
  
  const getApiUrlWithDatabase = (path: string) => {
    return `${path}?db=${selectedDatabaseId.value}`;
  };
  
  // Watch for database changes
  watch(selectedDatabaseId, () => {
    // Reload data when database changes
  });
  
  return {
    selectedDatabaseId,
    databases,
    setDatabase,
    getApiUrlWithDatabase
  };
};

The selected database is persisted in localStorage for session continuity. Users don’t need to re-select after page refresh.

Isolation Benefits

Zero Cross-Contamination

Connection Pools

Each database has its own MySQL/DuckDB connection pools

Sync Services

Independent sync operations per database

Storage

Separate DuckDB files prevent data mixing

Watermarks

Isolated watermark tracking per database

Resource Efficiency

Single Server: No need to deploy multiple instances
Shared Worker Pool: Sanitization workers shared across databases
Unified Monitoring: One dashboard for all databases
Centralized Backups: All databases backed up to same S3 bucket (different prefixes)

S3 Backup Configuration

Each database can have independent S3 backup settings:

Get S3 Config

curl 'http://localhost:3001/api/databases/lms/s3' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Response:

{
  "enabled": true,
  "bucket": "my-duckling-backups",
  "region": "us-east-1",
  "accessKeyId": "AKIA...",
  "secretAccessKey": "***",
  "pathPrefix": "lms/",
  "encryption": "client-aes256",
  "encryptionKey": "***"
}

Sensitive fields (secretAccessKey, encryptionKey) are masked in API responses for security.

Update S3 Config

curl -X PUT 'http://localhost:3001/api/databases/lms/s3' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "enabled": true,
    "bucket": "my-backups",
    "region": "us-east-1",
    "accessKeyId": "AKIA...",
    "secretAccessKey": "...",
    "pathPrefix": "lms/",
    "encryption": "client-aes256",
    "encryptionKey": "a3f1c2d4e5b6a7f8..."
  }'

Test S3 Connection

curl -X POST 'http://localhost:3001/api/databases/lms/s3/test' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

CDC (Change Data Capture) Per Database

CDC can be started independently for each database:

# Start CDC for LMS database
curl -X POST 'http://localhost:3001/api/cdc/start?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

# Get CDC stats for LMS
curl 'http://localhost:3001/api/cdc/stats?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

# Stop CDC for LMS
curl -X POST 'http://localhost:3001/api/cdc/stop?db=lms' \
  -H 'Authorization: Bearer ${DUCKLING_API_KEY}'

Production Best Practices

Database Naming

Use lowercase with underscores: my_database
Avoid special characters and spaces
Keep names short but descriptive

Connection Strings

Always include ?dateStrings=true in MySQL connection strings to prevent date/time conversion issues.

mysql://user:pass@host:port/database?dateStrings=true&timezone=UTC

Resource Allocation

For multiple databases, increase connection pool sizes:

# Total MySQL connections = MYSQL_MAX_CONNECTIONS × number of databases
MYSQL_MAX_CONNECTIONS=10

# Total DuckDB connections = DUCKDB_MAX_CONNECTIONS × number of databases
DUCKDB_MAX_CONNECTIONS=15

Monitoring

Monitor per-database metrics:

# Get status for all databases
for db in lms chitti_common; do
  curl "http://localhost:3001/status?db=$db" \
    -H "Authorization: Bearer ${DUCKLING_API_KEY}"
done

Implementation Files

File	Purpose
`src/database/databaseConfig.ts`	Database configuration manager
`src/middleware/database.ts`	Request-scoped database context
`src/database/duckdb.ts`	Multi-instance DuckDB connections
`src/database/mysql.ts`	Multi-instance MySQL connections
`packages/frontend/app/composables/useDatabase.ts`	Frontend database selector

Get Started

Core Concepts

Setup & Deployment

Operations

Advanced Features

​Overview

​Key Features

Isolated Replicas

Zero Cross-Contamination

Unified Monitoring

Database Selector

​Configuration

​databases.json Schema

​File Paths

​Multi-Instance Architecture

​Singleton Pattern Per Database

​Database Context Middleware

​Database Management APIs

​List All Databases

​Create Database

​Update Database

​Delete Database

​Test Connection

​Using Multi-Database in API Calls

​Sync Specific Database

​Query Specific Database

​Get Tables from Specific Database

​Health Check Specific Database

​Frontend Integration

​Database Selector Component

​useDatabase Composable

​Isolation Benefits

​Zero Cross-Contamination

Connection Pools

Sync Services

Storage

Watermarks

​Resource Efficiency

​S3 Backup Configuration

​Get S3 Config

​Update S3 Config

​Test S3 Connection

​CDC (Change Data Capture) Per Database

​Production Best Practices

​Database Naming

​Connection Strings

​Resource Allocation

​Monitoring

​Implementation Files

​Next Steps

Architecture

Sync Modes

Build docs developers (and LLMs) love

Overview

Key Features

Configuration

databases.json Schema

File Paths

Multi-Instance Architecture

Singleton Pattern Per Database

Database Context Middleware

Database Management APIs

List All Databases

Create Database

Update Database

Delete Database

Test Connection

Using Multi-Database in API Calls

Sync Specific Database

Query Specific Database

Get Tables from Specific Database

Health Check Specific Database

Frontend Integration

Database Selector Component

useDatabase Composable

Isolation Benefits

Zero Cross-Contamination

Resource Efficiency

S3 Backup Configuration

Get S3 Config

Update S3 Config

Test S3 Connection

CDC (Change Data Capture) Per Database

Production Best Practices

Database Naming

Connection Strings

Resource Allocation

Monitoring

Implementation Files

Next Steps