Cross-database compatibility is critical for: (1) SaaS products supporting multiple databases (customers choose MySQL or PostgreSQL), (2) Database migrations (MySQL → PostgreSQL for better features), (3) Multi-cloud deployments (AWS RDS MySQL, Azure PostgreSQL, Google Cloud SQL), (4) Open-source projects (WordPress supports MySQL/MariaDB, GitLab supports PostgreSQL/MySQL). Real examples: GitLab supports both PostgreSQL (recommended) and MySQL (legacy) - 100K+ installations. WordPress supports MySQL and MariaDB - 40% of websites. Metabase supports 10+ databases (PostgreSQL, MySQL, SQL Server, Oracle, BigQuery) - analytics for any database. Hibernate ORM supports 20+ databases - write once, run anywhere. Trade-offs: Portability vs performance (standard SQL is slower than optimized database-specific SQL), complexity (testing on multiple databases), feature limitations (can't use advanced features like PostgreSQL arrays, MySQL JSON functions).

GitLab: Supporting PostgreSQL and MySQL with ActiveRecord ORM

GitLab is a DevOps platform with 100K+ self-hosted installations. Initially supported both PostgreSQL (recommended) and MySQL (legacy). Challenge: Write code that works on both databases despite syntax differences (LIMIT, string concatenation, date functions, JSON support). Solution: Use ActiveRecord ORM (Ruby on Rails) for automatic SQL generation. Trade-off: PostgreSQL has better features (arrays, JSONB, full-text search), but MySQL support required for legacy customers.

GitLab uses ActiveRecord ORM to abstract database differences: (1) Pagination: ActiveRecord generates LIMIT for both MySQL and PostgreSQL. (2) String concatenation: ActiveRecord uses CONCAT() for both. (3) Date functions: ActiveRecord uses appropriate functions per database. (4) JSON support: ActiveRecord uses JSON columns (PostgreSQL JSONB, MySQL JSON). (5) Feature detection: Check database type, use PostgreSQL-specific features (arrays, full-text search) only if available. (6) Migration path: Gradually deprecate MySQL support, recommend PostgreSQL for new installations. Result: 90% of code is database-agnostic (ActiveRecord), 10% has conditional logic for PostgreSQL-specific features.

-- GitLab code example (Ruby on Rails with ActiveRecord)

# Pagination (works on both MySQL and PostgreSQL)

User.order(:id).limit(20).offset(40)

# MySQL: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40

# PostgreSQL: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40

# String concatenation (works on both)

User.select("CONCAT(first_name, ' ', last_name) AS full_name")

# MySQL: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users

# PostgreSQL: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users

# Date filtering (works on both)

User.where("created_at >= ?", 7.days.ago)

# MySQL: SELECT * FROM users WHERE created_at >= '2024-01-08 12:00:00'

# PostgreSQL: SELECT * FROM users WHERE created_at >= '2024-01-08 12:00:00'

# JSON support (works on both)

# Migration

class AddSettingsToUsers < ActiveRecord::Migration[6.0]

def change

add_column :users, :settings, :jsonb # PostgreSQL: JSONB, MySQL: JSON

end

end

# Query JSON (works on both)

User.where("settings->>'theme' = ?", 'dark')

# PostgreSQL: SELECT * FROM users WHERE settings->>'theme' = 'dark'

# MySQL: SELECT * FROM users WHERE JSON_EXTRACT(settings, '$.theme') = 'dark'

# PostgreSQL-specific features (with feature detection)

if ActiveRecord::Base.connection.adapter_name == 'PostgreSQL'

# Use PostgreSQL arrays

User.where("tags && ARRAY[?]::varchar[]", ['ruby', 'rails'])

# PostgreSQL: SELECT * FROM users WHERE tags && ARRAY['ruby','rails']::varchar[]

else

# Fallback for MySQL (store as comma-separated string)

User.where("tags LIKE ? OR tags LIKE ?", '%ruby%', '%rails%')

# MySQL: SELECT * FROM users WHERE tags LIKE '%ruby%' OR tags LIKE '%rails%'

end

# Full-text search (PostgreSQL-specific)

if ActiveRecord::Base.connection.adapter_name == 'PostgreSQL'