Load MySQL data to PostgreSQL

MySQL is an open-source relational database management system. This page has everything needed to load MySQL tables into PostgreSQL with dlt's sql_database source: the SQLAlchemy driver, the connection string, how to choose tables and load only new rows, plus the steps that hand the job to your coding agent.

---

Build your MySQL to PostgreSQL pipeline

Set up a project, install the dltHub AI harness and its sql-database-pipeline toolkit, and let your coding agent build the pipeline:

Copy
uv init
uv add "dlt[hub,sql_database,postgres]" pyarrow pandas PyMySQL
uv run dlthub ai init --agent <your-agent> # <agent>: claude | cursor | codex
uv run dlthub ai toolkit install sql-database-pipeline

Then paste this prompt into Claude, Codex, or Cursor:

The toolkit lists the schemas and tables in your database, asks which tables to load, then scaffolds a one-table pipeline and runs it with a load limit before scaling up. Credentials are inspected through MCP tools, so your agent never reads secrets.toml itself. About the toolkit →

Prefer to write it yourself? Every fact the agent uses is below.

---

MySQL at a glance

---

How do I connect to MySQL?

dlt needs a database user with read access to the tables you want to load. Add its connection details to .dlt/secrets.toml:

Copy
[sources.sql_database.credentials]
drivername = "mysql+pymysql"
host = "localhost"
port = 3306
username = "my_user"
password = "my_password"
database = "my_database"

Or as a single connection string:

Copy
[sources.sql_database]
credentials = "mysql+pymysql://my_user:my_password@localhost:3306/my_database"

Use SQLAlchemy’s built-in MySQL dialect with a DBAPI driver such as mysqlclient (the default for the mysql URL) or PyMySQL via mysql+pymysql. For reliable reflection, declare table names in lowercase: MySQL’s identifier case behavior depends on the operating system and lower_case_table_names, and SQLAlchemy warns that reflected foreign-key table names are received in lowercase.

PostgreSQL credentials go in the same file; the PostgreSQL destination docs list the fields it needs. dlt reads .dlt/secrets.toml automatically at runtime — never hardcode credentials in your pipeline script. For production, see setting up credentials with dlt.

---

Which MySQL tables can I load into PostgreSQL?

Any table or view the database user can read. The sql_database source reads each table's schema from MySQL and creates a matching table in PostgreSQL. These options narrow a load down to what you need:

Always pass table_names=[...] to sql_database(): without it, dlt reflects every table in the database before loading anything.

---

How do I load only new MySQL rows?

Give each table a cursor column and a primary key, and dlt loads only rows changed since the last run and merges them into PostgreSQL:

Copy
source.orders.apply_hints(
    incremental=dlt.sources.incremental("updated_at"),
    write_disposition="merge",
    primary_key="id",
)

Use a timestamp such as updated_at for tables whose rows change, and an increasing id for append-only tables. On the first run dlt loads everything; after that it reads only rows past the last cursor value. See incremental loading.

---

What does the MySQL to PostgreSQL pipeline look like?

This loads the customers and orders tables from the my_database database into PostgreSQL. Replace the table names with your own:

Copy
import dlt
from dlt.sources.sql_database import sql_database


def load_mysql_to_postgres() -> None:
    source = sql_database(
        schema="my_database",
        table_names=["customers", "orders"],
        backend="pyarrow",
    )
    source.orders.apply_hints(
        incremental=dlt.sources.incremental("updated_at"),
        write_disposition="merge",
        primary_key="id",
    )

    pipeline = dlt.pipeline(
        pipeline_name="mysql_pipeline",
        destination="postgres",
        dataset_name="mysql_data",
    )
    load_info = pipeline.run(source)
    print(load_info)


if __name__ == "__main__":
    load_mysql_to_postgres()

Credentials are not passed in the code: sql_database() reads them from [sources.sql_database] in .dlt/secrets.toml. Run it with uv run python mysql_pipeline.py.

---

Which backend should I use?

The backend decides how rows are read from MySQL. The example uses pyarrow; switch if your data calls for it:

The toolkit's /optimize-sql-performance skill measures where time goes and picks a backend, chunk size and parallelism for you. See configuring the backend.

---

How do I query MySQL data in PostgreSQL?

After a run, pipeline.dataset() holds the loaded tables. A Relation is a query on that dataset, either a whole table or a SQL string, and reading it gives you the data as a Pandas DataFrame, a PyArrow table or Python tuples. Nothing is read until you ask for it.

Copy
import dlt

pipeline = dlt.pipeline("mysql_pipeline")

# Step 1: Get the dataset from the pipeline
dataset = pipeline.dataset()

# Step 2: Get a Relation, either a whole table...
orders = dataset.table("orders")
# ...or any SQL query against PostgreSQL
recent = dataset("SELECT * FROM orders ORDER BY updated_at DESC LIMIT 100")

# Step 3: Read the data
df = orders.df()  # or orders.df(chunk_size=50) for a large table
arrow_table = recent.arrow()  # a PyArrow Table
rows = recent.fetchall()  # a list of Python tuples

The columns are the ones of your MySQL tables; dataset.row_counts().df() lists every table with its row count. Your agent can run the same queries through the MCP server's execute_sql_query tool, and uv run dlthub show opens the Pipeline Dashboard with the schema, load metrics and a query box. See access loaded data in Python and exploring it in marimo notebooks.

---

How do I deploy the MySQL to PostgreSQL pipeline in production?

The pipeline runs locally, which is ideal for prototyping and one-off loads. When you need it on a schedule, monitored on every load, and shared with your team, deploy the same dlt code on the dltHub platform — no infrastructure to maintain. The harness's dlthub-platform toolkit takes your agent there:

Copy
uv run dlthub ai toolkit install dlthub-platform

• Deploy & schedule — run the pipeline as a managed job with automatic retries.
• Monitor — observable job queues, alerting, and load metrics for every run.
• Transform — promote raw MySQL loads into governed, documented models.
• Visualize & share — explore data in notebooks and publish live dashboards instead of static screenshots.

Book a demo →

---

What other destinations can I load MySQL data to?

dlt loads into any of these — only the destination argument changes:

Set dlt.pipeline(destination="snowflake") and add that destination's credentials in .dlt/secrets.toml. See the full destinations list.

---

Next steps

• Ready for production? Book a demo of the dltHub platform →
• SQL database source reference
• The sql-database-pipeline toolkit
• Join the dlt community on Slack