October, 2026
How to Make a MySQL-Only Product Work With Multiple Databases Using SQLAlchemy
Part 1
- Asad Abdullah
How I reduced database coupling in a mature, raw-SQL-heavy product without rewriting the business layer.
Enterprise products often begin with one database and inherit years of assumptions around it. In my case, the application had been built around MySQL and contained a large amount of raw SQL. When the product needed to support additional database engines, the challenge was not just connection management; the SQL, parameter handling, generated IDs, procedures, and result behavior were also tied to database-specific choices. In this first part of the series, I explain how SQLAlchemy became the shared execution layer for the Python codebase, and which differences it could not remove on its own.
The engineering goal
Create one maintainable data-access path that could support multiple relational databases while preserving existing APIs and business behavior.
1. The Starting Point: Raw SQL Tied to MySQL
Here is the concept in simple EngliMany repository functions mixed SQL construction with execution. Some values were interpolated directly, while other queries relied on driver-specific placeholders or MySQL-only syntax.
EXAMPLE 1 — LEGACY PYTHON / SQL
query = f”””
SELECT *
FROM Customer
WHERE customerId = {customer_id}
ORDER BY createdOn DESC
LIMIT 20
“””
cursor.execute(query)
This creates two kinds of coupling at once: the repository knows how the database is called, and the SQL assumes how that database behaves. A move to Oracle, PostgreSQL, SQL Server, or Vertica therefore becomes a codebase-wide migration rather than a configuration change.
2. Centralizing Database Access with SQLAlchemy
The first step was to make SQLAlchemy the common execution foundation. Repositories stopped creating database-specific connections directly and instead called a shared database abstraction
EXAMPLE 2 — CENTRAL DATABASE ABSTRACTION
class DatabaseAbstraction:
def get_engine(self, db_type, config):
…
def execute_query(self, query, params=None):
…
def execute_many(self, query, rows):
…
def execute_stored_procedure(self, name, params):
…
Under that abstraction, SQLAlchemy engines can use the appropriate driver for each deployment—for example, PyMySQL for MySQL, oracledb for Oracle, psycopg2 for PostgreSQL, or pyodbc for SQL Server. The repository does not need to change because the execution contract stays the same.
3. Standardizing Parameter Binding
The next change was to remove string-built values and standardize named bind parameters. This improved security, consistency, and portability at the same time.
EXAMPLE 3 — PORTABLE BOUND QUERY
query = “””
SELECT customerId, customerName, createdOn
FROM Customer
WHERE status = :status
ORDER BY createdOn DESC
“””
params = {“status”: status}
rows = db.execute_query(query, params)
The SQL text and the runtime values are now separate. SQLAlchemy and the active driver handle value transmission instead of repository code manually quoting or formatting inputs.
- Reduces SQL-injection risk from string interpolation.
- Gives repositories one consistent parameter style.
- Makes query logging and testing easier.
- Avoids database-specific formatting for strings, numbers, and dates.
Dynamic lists can follow the same idea. SQLAlchemy expanding parameters allow a repository to pass a Python list for an IN condition instead of constructing comma-separated SQL manually.
EXAMPLE 4 — DYNAMIC IN PARAMETER
from sqlalchemy import text, bindparam
query = text(“””
SELECT branchId, branchName
FROM Branch
WHERE branchId IN :branch_ids
“””).bindparams(bindparam(“branch_ids”, expanding=True))
params = {“branch_ids”: [1, 2, 3, 4]}
4. Moving Queries Toward Common SQL
A key rule was to use common SQL wherever the databases already agree. Database abstraction becomes harder to maintain when every query is wrapped in custom logic, so simple SQL should stay simple.
EXAMPLE 5 — COMMON SQL KEPT UNCHANGED
SELECT customerId, customerName, createdOn
FROM Customer
WHERE isActive = :is_active
ORDER BY createdOn DESC
The database-specific layer is reserved for genuine differences such as pagination, date operations, string aggregation, concatenation, generated IDs, and some datatype behavior. This keeps the migration practical rather than turning every SELECT statement into a framework of its own.
5. Before and After: A Repository Refactor
The clearest improvement is visible at repository level. The repository becomes responsible for the data requirement, while connection and execution details move to infrastructure.
BEFORE — MYSQL-ORIENTED REPOSITORY
def get_active_customers(status):
query = f”””
SELECT *
FROM Customer
WHERE status = ‘{status}’
ORDER BY createdOn DESC
LIMIT 20
“””
cursor.execute(query)
return cursor.fetchall()
AFTER — DATABASE-AGNOSTIC REPOSITORY
def get_active_customers(status):
query = “””
SELECT customerId, customerName, createdOn
FROM Customer
WHERE status = :status
ORDER BY createdOn DESC
“””
return db.execute_query(query, {“status”: status})
The remaining pagination difference is applied by the database-specific layer rather than hardcoded into this repository. That separation becomes increasingly valuable as the number of modules grows.
6. Handling the Remaining Cross-Database Differences
Not every behavior can be standardized with plain SQL. I kept these differences behind the shared abstraction instead of scattering database checks through repositories.
Generated IDs. MySQL may use LAST_INSERT_ID(), SQL Server SCOPE_IDENTITY(), PostgreSQL RETURNING, while Oracle often uses RETURNING … INTO. The repository simply asks for the inserted ID; the database layer decides how to retrieve it.
Stored procedures. The calling mechanism can differ between drivers and database engines, so services call one execute_stored_procedure interface rather than embedding CALL, EXEC, or driver-specific procedure logic.
Result normalization. Legacy data can represent empty values as NULL, N/A, NA, a dash, or an empty string. Normalizing these values centrally prevents a database change from unexpectedly altering API behavior.
Queries stored in a Database table. Some query definitions are configuration-driven rather than written directly in Python. Routing them through the same centralized execution path lets them benefit from the same parameterization and database-aware processing. The transformation of vendor-specific SQL is covered in Part 2.
7. What This Solved in the Real World
The value of the change was not limited to cleaner code. It addressed deployment and maintenance problems that become expensive in enterprise products.
One product for different customer standards. A customer may mandate Oracle, another PostgreSQL, and another SQL Server. A shared data-access layer reduces the pressure to maintain separate product branches for each database.
Less vendor lock-in. When MySQL-specific functions and execution assumptions are spread through hundreds of repositories, changing databases becomes a major commercial and engineering constraint. Centralization limits that dependency.
Incremental migration. The product did not need a big-bang rewrite. Connection handling and parameter binding could be standardized first, followed by common SQL, then the smaller set of true dialect differences.
Safer feature development. New repository functions start with the same execution contract, which reduces the chance that a feature works on one database but introduces another database-specific pattern into the codebase.
Conclusion: A Practical Path for Legacy Systems
The main lessons are straightforward and reusable beyond this project:
- SQLAlchemy is the execution foundation, not a magic converter for every raw SQL function.
- Repositories should express data intent, not driver behavior.
- Use common SQL first and abstract only genuine database differences.
For teams maintaining mature applications, database portability does not require a full rewrite. Applied together and adopted module by module, these principles reduce migration risk, preserve business logic, and make new customer database requirements easier to support without separate product branches. Part 2 shows how the remaining vendor-specific SQL can be handled through dialect-driven query processing.
Asad Abdullah
Asad Abdullah works as a Software Engineer at TenX
