dbmerge 2.0.0

DBMerge is a Python library that provides a simplified interface for performing UPSERT (Insert/Update/Delete) operations.
Built on top of SQLAlchemy, it abstracts away engine-specific SQL MERGE or ON CONFLICT statements.

Overview

Common problems solved

• Efficient bulk upsert with conflict resolution - Insert or update records in bulk without writing complex SQL. DBMerge automatically handles primary key conflicts: existing rows are updated, new rows are inserted.
• Update rows only when values change - Automatically compares source data against the target table and skips writes for unchanged rows, reducing database load and I/O.
• Materialize complex views - Persist results of heavy computations to a physical table for fast querying, supporting also partial data updates.

DBMerge accepts multiple data sources as input:

• Pandas/Polars DataFrame
• Lists of dictionaries / Dictionary of Lists
• Database table or view

DBMerge automates data update process by comparing your source data against the target table and automatically performing the required operations.

• Insert new records that do not exist in the target table.
• Update existing records only if their values have changed.
• Delete (or mark) existing records in the target table that are no longer present in the source data.

To ensure optimal performance, the library loads your data into a temporary table first, and then executes bulk synchronization queries.

Supported Databases

Tested and verified with:

• PostgreSQL
• MariaDB / MySQL
• SQLite
• MS SQL Server
• CockroachDB
• DuckDB
• ClickHouse

COMPATIBILITY.md lists the tested drivers and versions.

Installation

pip install dbmerge

Quick Start Example

The library uses a context manager to handle database connections and ensure resources are safely released.

from sqlalchemy import create_engine
from datetime import date
from dbmerge import dbmerge

# 1. Initialize DB engine
engine = create_engine("sqlite://")

# 2. Prepare your source data
data = [
    {'Shop': '123', 'Product': 'A1', 'Date': date(2025, 1, 1), 'Qty': 2, 'Price': 50.10},
    {'Shop': '124', 'Product': 'A1', 'Date': date(2025, 1, 1), 'Qty': 1, 'Price': 100.50}
]

# 3. Execute the merge operation
# The table will be created automatically if it doesn't exist.
with dbmerge(engine=engine, data=data, table_name="Facts", 
             key=['Shop', 'Product', 'Date']) as merge:
    result = merge.exec()

Key Features

• Database Agnostic: Write your synchronization logic once and run it across different SQL databases without modifying the code.
• High Performance: Uses temporary staging tables for fast bulk operations rather than slow row-by-row changes.
• Smart Deletion: Supports scoped deletion. You can pass a SQLAlchemy logical expression to delete missing data only within a specific timeframe or subset (e.g., updating only a single month).
• Auto-Schema Management: Automatically creates missing tables or columns in the database.
• Audit: Optional parameters to automatically add merged_on and inserted_on timestamps to track when rows were created or modified.
• Statistics: Measures number of updated/inserted/deleted rows, total time and time for each operation step.

Benchmark

DBMerge handles the entire reconciliation process (staging, comparing, updating, inserting) with solid performance, scaling well even for larger datasets.

Here is a rough performance comparison for synchronizing data of different sizes using DBMerge (measured on a developer laptop with an Intel Core i5-1235U, the driver in parentheses):

The staging table is loaded in bulk where the driver allows it: COPY FROM STDIN on PostgreSQL and CockroachDB, bulk copy on MS SQL Server, Arrow on DuckDB and ClickHouse. The other drivers of PostgreSQL (psycopg, pg8000) and CockroachDB (psycopg) are within a few percent of psycopg2; on MS SQL Server pyodbc takes ~1.9s / ~14.5s and pymssql, which loads row by row, ~11s for 100k rows. See COMPATIBILITY.md for every driver.

Database-Specific Notes & Limitations

• PostgreSQL:
  • Staging tables are real TEMPORARY tables, they are invisible to other connections and vanish with the session. temp_schema does not apply here and is quietly ignored — PostgreSQL keeps temporary tables in its own session schema.
  • JSONB type is supported, but not JSON (as it cannot be compared to detect changes).
• MariaDB / MySQL:
  • Does not detect changes in uppercase vs. lowercase or space padding by default (e.g., 'test' == ' Test'). If this is important, you need to change the collation settings in your database.
  • The schema is treated the same as the database, but schema settings are still supported by this library.
  • A string column of the merge key needs an explicit length, because InnoDB shares one 3072-byte index budget between all columns of the key (e.g., data_types={'Your Key Field': String(100)}). Other string columns are created as LONGTEXT automatically.
  • Timezone-aware datetimes lose their UTC offset: no MySQL/MariaDB type stores one. Normalize to UTC before merging if the offset matters.
  • A BOOLEAN column is stored as TINYINT(1), so a delete_mark_field there holds 0/1 rather than False/True.
  • The staging table is TEMPORARY on MariaDB and a regular table on MySQL, which can not refer to one temporary table twice in a statement.
• SQLite: Does not support schemas. If a schema setting is provided, it is automatically reset to None with a warning.
• MS SQL Server: Use pyodbc or mssql-python for a fast staging load. pymssql loads row by row and keeps milliseconds only.
• CockroachDB: Uses the official sqlalchemy-cockroachdb dialect (over psycopg2 or psycopg); speaks the PostgreSQL wire protocol.
  • exec(commit_all_steps=False) is noticeably faster here. On a single-node instance, merging 200 000 rows took ~6s against ~16s with the default, because one commit costs a consensus round regardless of how much it carries. The update phase showed no difference. It also makes the merge all-or-nothing; the trade-off is one larger transaction, which raises the chance of a serialization retry.
• Oracle: Currently not supported (missing support for JOIN operations in UPDATE statements within the oracledb module).
• DuckDB: Use a database file: with duckdb:///:memory: every connection gets a database of its own.
• ClickHouse: Through clickhouse-connect (pip install dbmerge[clickhouse]). The target must be a ReplacingMergeTree ordered by the merge key; a changed row is written as a new version, so read the table with FINAL. See ClickHouse.

Data Loss Risks

DBMerge modifies your target table in bulk, and three deliberate design trade-offs that may have negative effects on your data:

• delete_mode='delete' without delete_condition treats the source as a complete snapshot (an empty source wipes the table).
• automatic schema creation with default can_create_table=True and can_create_columns=True infers column types from a sample of your data.
• default commit_all_steps=True commits each phase separately, so a failure leaves a partial result.

Read Data Loss Risks for the full list and the pre-merge checklist.

Documentation

• Full Module Documentation
• Advanced Examples (Python)