One goal for GOTRS was supporting both PostgreSQL and MariaDB. Users should choose their preferred database without application changes. Sounds simple. It wasn’t.

The Problem

Organisations have existing database infrastructure. Some have PostgreSQL expertise and don’t want to introduce MySQL. Others have MySQL clusters they’d prefer to use. Forcing a database choice limits adoption.

But SQL isn’t as portable as you’d hope. PostgreSQL uses $1, $2, $3 placeholders. MySQL uses ?. ILIKE for case-insensitive matching doesn’t exist in MySQL. Auto-increment ID retrieval works differently. A query written for one database fails on the other.

Our Solution

We built an abstraction layer. database.ConvertPlaceholders() transforms queries for the active driver. Helpers handle argument duplication when parameters appear multiple times. InsertWithReturning() abstracts away the differences in ID retrieval.

Instead of ILIKE, we use LOWER(column) LIKE LOWER(?) which works everywhere. Every query goes through the compatibility layer.

The test harness runs identical fixtures against both databases. Flip an environment variable and the entire suite runs against the other engine.

The Benefits

Users choose their preferred database without application changes. PostgreSQL shops use PostgreSQL. MySQL shops use MySQL. No compromises.

Testing both databases in CI means we catch compatibility issues before they ship. Several subtle bugs that only appeared on one database were caught this way.

The lesson: if you’re building something that might need multiple database support, add the abstraction early. Retrofitting it later is painful. We did the work upfront and now database portability is a non-issue.