Workshop · Tech note #03 · personal project

IdempotentBase

Database scripts that can safely run twice

A desktop tool that compares DEV × PROD schemas, rates each difference by risk and generates an idempotent SQL script. Destructive changes are blocked by default, and nothing is dropped silently.

2 min read by Edinaldo Sá

  • Databases
  • SQL
  • DevOps
  • .NET
  • WPF
  • Open source
Illustrative schematic: “DEV, source of truth” and “PROD” cylinders feed a “Reconcile” box, which produces four risk labels (SAFE_AUTO, REVIEW_REQUIRED, DESTRUCTIVE_BLOCKED, INFO_ONLY) and a script document with IF NOT EXISTS lines; below, k6, JMeter and Gatling.
Illustrative schematic made for this post. Not a screenshot.

The script that can run twice

“The script ran twice. Now the table is gone.” Shipping database changes to production is the riskiest moment of a deploy. Hand-written scripts drop the wrong column, break when re-run or lock the database at peak time.

IdempotentBase is a desktop tool for DBAs and DevOps that compares environments, rates each difference by risk and generates a script that can run as many times as needed, always with the same result.

Reconcile: DEV × PROD

The core module compares the DEV schema with PROD. DEV is the source of truth. Each difference found gets one of four ratings:

  • SAFE_AUTOsafe to apply
  • REVIEW_REQUIREDneeds review
  • DESTRUCTIVE_BLOCKEDdestructive, blocked
  • INFO_ONLYinformational only

From there, the tool generates an idempotent SQL script. Applying it to production requires mandatory confirmation.

Safety model

The safety model is explicit. It fits in three lines:

  • No silent drops
  • No automatic shrink
  • Destructive changes are blocked by default

Backup, Migrate and Load Test

Backup

Uses SQL Server’s native BACKUP DATABASE.

Migrate

Moves schema and data between MySQL, PostgreSQL and SQL Server, with DDL and batched INSERTs.

Load Test

Sandbox load: bulk insert, concurrent database users and an HTTP API. Exports to k6, JMeter and Gatling.

The Load Test export is my favourite part: the test leaves the tool and goes into your pipeline, instead of being stuck in a proprietary format.

Supported databases

  • SQL Server
  • PostgreSQL
  • MySQL
  • MariaDB
  • Oracle

Oracle is covered in several flows. SQLite and MongoDB have partial support.

Open source and a ready binary

The project comes in two forms:

Source code

On GitHub under the MIT license, with in-memory snapshot tests and a README in English and Portuguese.

Binary distribution

The ready-to-run package, nothing to compile: the executable, the product DLLs and sample connection files for each provider.

The distribution’s DLLs are split by responsibility: Core, Migration, Reporting and one per database (SqlServer, PostgreSQL, MySql, Oracle, Sqlite and MongoDB). To run it, all you need is Windows 10 or later with .NET Framework 4.8.

Tech sheet

  • .NET Framework 4.8
  • WPF
  • Dapper
  • Npgsql
  • MySqlConnector
  • Oracle.ManagedDataAccess
  • Microsoft.Data.Sqlite
  • Serilog
Version
v1.1.0: Load Test workflow, MariaDB and export to k6, JMeter and Gatling
License
MIT
Requirements
Windows 10+ and .NET Framework 4.8
Audience
DBAs, DevOps and squads that ship schema changes often

Work on database deploys and want to talk idempotent scripts? Write to me.

Write to me