Many projects start with just one database. So AI naturally writes:

async def get_user(user_id: int):
    async with sqlite_session() as session:
        ...

Or even more directly:

conn = sqlite3.connect("app.db")

The feature certainly works, but problems will show up later.

Today it’s SQLite, tomorrow it might need to switch to PostgreSQL …

As the codebase grows, the project quickly becomes:

Business code tightly coupled to a specific storage technology.

This is exactly the problem that Multi-Data-Source Architecture aims to solve.

📌 Technology Card

Multi-Data-Source Architecture

A system that simultaneously uses multiple different data sources and manages them through unified architectural boundaries, instead of letting business code depend directly on specific database implementations.

Common data sources include:

SQLite
PostgreSQL
MySQL
DuckDB

The most important thing here is not:

“How many databases are supported.”

But rather:

Whether business code needs to know what the underlying database actually is.

💡 One-Sentence Summary

You can understand multi-data-source architecture as:

The business layer is only responsible for “what data I need”; the infrastructure layer handles “where to get it and how”.

An Intuitive Analogy: Databases Are Like Shipping Companies

Suppose you run an e-commerce business. The business team only cares about:

Getting products into customers' hands.

As for the shipping method:

SF Express
JD Logistics
DHL
Sea freight
Air freight

None of these should be written into the order business itself. A wrong design might look like:

Order is domestic:
    call SF Express API

Order is for Europe:
    call DHL API

Order is oversized:
    call sea freight API

The more reasonable approach is for the order system to simply say:

“Ship this for me.”

And let the logistics layer decide how to actually do it.

Databases are the same. The business layer should say:

Query users
Save an Agent
Read the knowledge base
Run analytical SQL

Rather than:

Use this syntax for SQLite
Use that syntax for PostgreSQL
Handle DuckDB as a special case

I. Cross-Data-Source Architecture

1. The Business Layer Must Not Depend on Databases Directly

A healthy call chain should look like:

Router
  ↓
Service
  ↓
Repository / Data Access
  ↓
Database Driver / ORM
  ↓
Database

Where:

Repository (the repository layer)

is responsible for encapsulating data access.

A business Service should not know about:

SQLAlchemy
sqlite3
aiosqlite
psycopg
DuckDB connection

It should only know:

user = await user_repository.get_by_id(user_id)

Not:

async with session.execute(...):

2. The Point of an ORM Is Not Just “Writing Less SQL”

This brings up an important term:

ORM / Object-Relational Mapping

An ORM establishes a mapping between Python Objects and Relational Database Tables; for example, User maps to the users table.

When many people think about ORMs, they only think of:

Writing less SQL.

But in a multi-database architecture, it has an even more important value:

Isolating the huge amount of syntax and driver differences between databases.

For example, SQLAlchemy can adapt to different databases through different:

Dialects

The application layer writes:

select(User).where(User.id == user_id)

and under the hood this is translated into the appropriate SQL depending on the database.

3. But an ORM Is Not a “Universal Compatibility Layer” Either

It must be made clear that significant differences still exist between databases, for example:

Data types
JSON support
Full-text search
Pagination syntax
UPSERT
Auto-increment primary keys
Date functions
Locking mechanisms
Transaction isolation
Indexes
Window functions

If AI writes lots of:

text("some database-specific SQL")

then even though an ORM is being used:

The project is still locked to one database.

Therefore the rule should be:

Prefer the ORM’s generic expression capabilities; database-specific syntax may only live in the infrastructure layer.

II. The Main Business Database of miniagent

1. Creation

miniagent’s current main business database uses:

SQLite
+
SQLAlchemy Async

At application startup, everything is created centrally in the ServiceContainer:

database_url = f"sqlite+aiosqlite:///{db_path}"

self.engine = create_async_engine(
    database_url,
    echo=False,
    future=True,
)

self.session_factory = async_sessionmaker(
    bind=self.engine,
    ...
)

Then these are uniformly injected into different data access objects:

engine
session_factory

self.user_db = AsyncUserDatabase(
    self.engine,
    self.session_factory
)

self.chat_db = AsyncChatDatabase(
    self.engine,
    self.session_factory
)

self.kb_db = AsyncKnowledgeBaseDatabase(
    self.engine,
    self.session_factory
)

This structure is critically important.

Because business Services do not each call create_engine() themselves; they all reuse the shared database infrastructure.

2. Unified Session Management, So AI Doesn’t Scatter commit/rollback Everywhere

There is another aspect of database access that AI easily messes up:

Transactions

AI tends to write in one place:

await session.commit()

In another place:

await session.rollback()

And forget to close in yet another.

So the Session lifecycle ends up scattered everywhere.

miniagent provides a unified AsyncBaseDatabase, which contains:

@asynccontextmanager
async def get_session(self):

    async with self.AsyncSessionLocal() as session:
        try:
            yield session
            await session.commit()

        except SQLAlchemyError:
            await session.rollback()
            raise

        finally:
            await session.close()

Here, a:

Transaction

can be understood as:

A group of database operations that either all succeed or all fail.

This way the Repository layer can uniformly use:

async with self.get_session() as session:
    ...

instead of making every business function figure out again:

When to commit?
Should we roll back on failure?
When to close?

3. Multiple Data Sources Does Not Mean “Forcibly Unifying All Databases”

This is one of the most important points in this article.

miniagent itself is a great example.

Its:

Users
Agents
Knowledge base configuration
Permissions
Chat history
System settings

are typical business data.

Well suited for:

SQLAlchemy
↓
SQLite

But the SQL Agent serves a different scenario:

Analytical data queries.

So miniagent also has a separate DuckDBManager:

self.conn = duckdb.connect(db_path)

def execute(self, sql, params=None):
    return self.conn.execute(
        sql,
        params or []
    ).fetchall()

In other words, miniagent does not force:

All data to go through the same database access method.

Instead:

Operational Data
Business data
     ↓
SQLAlchemy / SQLite

Analytical Data
Analytics data
     ↓
DuckDB

And that is perfectly reasonable.

4. Why Can an Analytical Database Exist Separately?

Because databases like:

SQLite
PostgreSQL
MySQL

are better at:

OLTP (Online Transaction Processing)

For example:

Creating users
Modifying an Agent
Saving chat history
Permission management

While DuckDB leans toward:

OLAP (Online Analytical Processing)

For example:

Analyzing CSVs
Aggregating millions of rows
GROUP BY
Statistical reports
Ad-hoc analysis

So a correct multi-data-source architecture is not:

“Find one database that solves every problem.”

But rather:

Different data sources take on different responsibilities.

5. A More Complete Multi-Data-Source Architecture

Below is the complete data architecture of miniagent:

miniagent data architecture

As shown above, miniagent has two clearly distinct data access routes.

The first is for business data:

Router
   ↓
Service
   ↓
Async*Database / Repository
   ↓
AsyncBaseDatabase
   ↓
SQLAlchemy
   ↓
SQLite

ServiceContainer centrally creates the Engine and SessionFactory, then injects them into multiple business database objects, rather than letting each module create its own database connections.

AsyncBaseDatabase further manages the Session, Commit, Rollback, and Close lifecycle in one place.

The second is for analytical data:

SQL Agent
   ↓
DuckDBManager
   ↓
DuckDB

DuckDBManager directly wraps the DuckDB connection and SQL execution.

So what miniagent truly demonstrates today is:

flowchart TB A[miniagent] --> B[Business / Runtime] B --> C[Business Data] B --> D[Analytics Data] C --> E[Repository Layer] E --> F[AsyncBaseDatabase] F --> G[SQLAlchemy] G --> H[SQLite] D --> I[SQL Agent] I --> J[DuckDBManager] J --> K[DuckDB]

It is not:

“All databases must go through SQLAlchemy.”

But rather:

Each data source uses the access method that suits it, while the concrete database implementation stays behind the infrastructure boundary.

This is how multi-data-source architecture actually lands in practice.

6. The Core of Cross-Data-Source Design Is Not “Compatibility” but “Isolation”

Many people would state the goal as:

I want to support MySQL, SQLite, and PostgreSQL.

But a better goal should be:

When the database changes, the changes stay as much as possible within the infrastructure layer.

That is:

Database Changed
      ↓
Infrastructure
      ↓
Repository

Instead of:

Database Changed
 ├── Router modified
 ├── Service modified
 ├── Tool modified
 ├── API modified
 └── Frontend modified

This is what we call:

Change Isolation

What architecture truly wants to solve is the cost of change.

7. The Repository Layer Is the Database’s “Firewall”

You can think of a Repository as:

A wall between the business world and the database world.

On the left:

Business

says:

Give me a User
Save an Agent
Query the KB

On the right:

Database

deals with:

SQL
Join
Session
Transaction
Dialect
Index
Connection

The Repository in the middle does the translation.

For example:

Service
   ↓
user_repository.get_by_id(10)
   ↓
Repository
   ↓
SQLAlchemy
   ↓
SQLite / PostgreSQL

So a very important AI Rule is:

Services are not allowed to bypass the Repository and touch the database directly.

III. Multi-Data-Source Architecture

1. How Should It Be Layered?

You can adopt rules like this:

flowchart TB A[Application / Service] --> B[Data Access Abstraction] B --> C[Repository] B --> D[Adapter] B --> E[Client] C --> F[SQL DB] D --> G[DuckDB] E --> H[Vector DB]

Where:

Repository

Fits business database CRUD.

Adapter

Fits adapting different protocols or special data sources.

Client / Manager

Fits:

Vector Store
External Search Engine

The key point is:

What the upper layers see are “capabilities”, not “product names”.

2. When Should You Extract a Unified Interface?

For example, if the business layer only needs:

await user_repository.get_by_id(id)

then you can have:

UserRepository
      │
      ├── SQLAlchemyUserRepository
      └── FutureOtherRepository

But don’t — in the name of “we might support ten databases in the future” — start by creating:

AbstractDatabaseFactoryProviderManagerAdapter

a giant apparatus like that.

Architecture needs extension points, but not over-engineering.

For many Python projects:

Service
↓
Repository
↓
SQLAlchemy

is already enough to isolate changes like SQLite → PostgreSQL.

3. What Are the Benefits?

Lower database migration cost

Suppose:

Development: SQLite

and later production switches to:

PostgreSQL

If the business code makes heavy use of SQLAlchemy’s generic capabilities, the changes mainly concentrate on:

Database URL
Driver
Migration
A few dialect differences

rather than rewriting all the Services.

Each database can play to its own strengths

For example:

Business transactions
→ PostgreSQL / SQLite

Analytical queries
→ DuckDB

Vector search
→ Vector Database

Caching
→ Redis

instead of using one hammer for every problem.

Easier testing

If a Service only depends on:

Repository

then in tests it can be replaced with:

Fake Repository

without actually starting a database.

Database capabilities don’t pollute business semantics

Business code remains:

await agent_service.create(...)

instead of:

SQLite INSERT
PostgreSQL ON CONFLICT
MySQL UPSERT

The business code stays easier to understand.

Better suited for AI coding

The most dangerous trait of AI is:

Seeing one working pattern and copying it across the entire project.

If the project prescribes:

Router → Service → Repository → Database

then AI has a clear path to follow.

Otherwise it will easily do:

Wherever data is needed
→ connect to the database right there

IV. Establishing Multi-Data-Source Architecture Rules for AI

You can add the following directly to your Project Rules:

## Multi-Data-Source Architecture

Business code is forbidden from depending directly on specific database implementations.

Call chain:

Router
→ Service
→ Repository / Data Adapter
→ Database Client / ORM
→ Data Source

Rules:

- Routers must not create database connections;
- Services must not contain database-specific SQL;
- Ordinary business database access goes into Repositories;
- Connection, Session, Transaction, and Driver lifecycles belong to the infrastructure layer;
- Ordinary CRUD should prefer generic ORM expressions;
- Database-specific SQL may only stay in the data access layer;
- Scattered database_type checks in the business layer are forbidden;
- Different data sources may use different access technologies;
- Do not force DuckDB, vector databases, Redis, etc. into a relational ORM.

Core goal:

What gets unified is the "access boundary",
not forcing all data sources to use the same implementation.

Prompting in Practice: Don’t Just Tell AI “Support PostgreSQL”

A bad prompt:

Change this project to support both SQLite and PostgreSQL.

The AI will likely start writing:

if db_type == "sqlite":
    ...
else:
    ...

and then change things everywhere.

A more reasonable prompt:

Please add PostgreSQL compatibility to the current project.

Requirements:

1. First inspect the existing database access boundaries;
2. Router and Service must not be aware of the database type;
3. Ordinary CRUD continues to reuse the existing Repository;
4. SQLAlchemy Engine and SessionFactory are created centrally by the infrastructure layer;
5. Prefer SQLAlchemy generic expressions;
6. Identify SQLite-specific SQL or data types;
7. Database differences may only be encapsulated in infrastructure / repository;
8. Adding new db_type if/else in business code is forbidden;
9. Keep the existing Service APIs unchanged;
10. Provide a checklist of compatibility differences between SQLite and PostgreSQL.

This way the AI’s goal shifts from:

“Add an if wherever something breaks.”

to:

“Confine database differences to the correct architectural layer.”

When Adding a New Data Source, Ask AI a Few Questions First

For example, to introduce DuckDB, Redis, or a vector database, you can require the AI to first answer:

Before implementing, please answer:

1. What responsibility does the new data source take on?
2. Is it business transactional data or analytical data?
3. Should the existing Repository be reused?
4. Does it need a dedicated Adapter / Manager?
5. What unified capability should the Service see?
6. Which database-specific logic must be isolated?
7. Who manages the Connection lifecycle?
8. Are transactions needed?
9. Is connection pooling needed?
10. Should it really share one implementation with the existing database?

The last question is especially important.

Because:

Multi-data-source architecture does not mean making all data sources the same.

V. How Can miniagent Naturally Evolve in the Future?

If the business database later switches from SQLite:

SQLite
↓
PostgreSQL

As long as it keeps the structure:

Service
↓
Repository
↓
SQLAlchemy

most business logic never needs to know about the change.

Meanwhile the system can keep DuckDB for analytics, with Chroma / Vector Store handling vector retrieval, and possibly Redis serving as a shared value cache in the future.

Eventually forming:

flowchart TB A[Application] --> B[Service Layer] B --> C[Data Access Boundaries] C --> D[Relational DB] C --> E[DuckDB] C --> F[Vector DB] C --> G[Redis] D --> H[PostgreSQL] E --> I[Analytics] F --> J[Retrieval] G --> K[Cache]

Upper-layer business code never needs to handle these differences directly.

Summary

The real problem that multi-data-source architecture solves is not:

“How do I write code that connects to five databases at once?”

But rather:

“When five databases exist, how does the business code stay clean?”

The truly important principle is:

Business defines what data is needed
        ↓
The data access layer decides how to obtain it
        ↓
Infrastructure decides which database to use

For ordinary business CRUD:

Service
↓
Repository
↓
ORM
↓
Relational Database

For special data sources:

Service / Tool
↓
Dedicated Adapter / Manager
↓
DuckDB / Vector DB / Redis

So the one sentence most worth writing into your AI project rules is not:

“The project must support multiple databases.”

But rather:

Forbid AI from writing a specific database into business logic; what gets unified is the access boundary, not forcing all data sources to use the same implementation.

Then no matter what sits underneath:

SQLite
PostgreSQL
DuckDB
Redis
Vector Database

what actually changes should mainly be the infrastructure — not the entire project.

Open Source Code

🪐 Good luck 🪐