How to Version Control MariaDB Queries in PostgreSQL

Why bother version-controlling MariaDB queries?

Version control turns ad-hoc SQL into traceable, peer-reviewed changes. Teams see who altered a report, roll back bugs fast, and align dev, staging, and prod schemas.

What folder layout works best?

Use a "migrations" root with timestamped pairs:
└── 20240611_1200_create_products
    ├── up.sql
    └── down.sql

Place repeatable reference views in /queries/views/ and analytical reports in /queries/reports/.

How do I write an up.sql script?

Each file should be idempotent. Wrap DDL in IF NOT EXISTS guards and insert default rows with ON CONFLICT DO NOTHING.

-- up.sql
CREATE TABLE IF NOT EXISTS Products (
id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
);


How do I apply migrations?

Add a tiny metadata table schema_migrations(version INT PRIMARY KEY). A shell runner scans the folder, skips applied versions, and feeds each script to mysql or psql. Commit the runner in bin/migrate.sh so CI/CD can call it.

How can I roll back safely?

Every up.sql needs a matching down.sql. The runner executes down.sqls in reverse order inside a transaction to restore the previous schema.

-- down.sql
DROP TABLE IF EXISTS Products;


How do I version control read-only queries?

Save views and reports in Git too. Example file queries/reports/top_customers.sql:

SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC
LIMIT 10;


What CI checks should I add?

Run mysql --dry-run or EXPLAIN inside CI to validate syntax. Enforce code review with a pull-request template that links issue IDs and requires a rollback plan.

Common mistakes

Forgetting a down.sql

A missing rollback file forces manual fixes. Always pair every migration.

Committing generated SQL dumps

Large dumps obscure history and break diffs. Store schema-only scripts; seed data separately.

Best practices recap

• Timestamp folders for deterministic ordering
• Keep migrations small and single-purpose
• Tag releases to match database versions
• Automate in CI/CD to avoid drift