Better Postgres Database Migrations and Testing by Ditching Bloated ORMs

Managing PostgresSQL schema can be a challenging task, especially when dealing with bloated ORMs. Learning new syntaxes that eventually translate to SQL but are almost always worse than writing pure SQL can be frustrating. In this blog post, we will explore a better approach to managing Postgres database migrations and testing, using a tool called Graphile Migrate. We will discuss the important principles to follow and how Graphile Migrate simplifies the workflow. So, let's jump right in!

Before I continue, a couple of useful links:

• The GitHub code repository containing an example of a database migration and integration test.

• The video version of this article which contains additional practical examples.

Principle #1: Writing Schema Migrations in the Language of Your Database:

The best way to write schema migrations is in the native language of your database, which is SQL. Graphile Migrate allows you to write migrations in pure SQL, freeing you from being tied to a particular tech stack. By using SQL, you can easily switch between different migration tools without rewriting your migration scripts.

Embracing the native language of your database — SQL — and harnessing its robust features will empower you as a software engineer. SQL is the key to querying, manipulating, and optimizing data efficiently. Whether you’re writing complex queries or designing efficient indexes, SQL proficiency is essential for successful database management.
So dive into SQL tutorials, practice writing queries, and explore the intricacies of database design.

Principle #2: Idempotency

Graphile Migrate emphasizes the important concept of idempotency in database migrations. An idempotent operation can be run multiple times without altering the results. This means that if a migration script is run multiple times, it will always produce the same outcome. In other words an operation is considered idempotent if it can be executed multiple times without altering the final state.
This property ensures predictability and stability during the migration process.

Getting Started with Graphile Migrate:

To get started with Graphile Migrate, you'll need to install it and run the initialization script (I'm using Bun, feel free to use NodeJS & npm / pnpm / yarn):

bun add graphile-migrate
bun graphile-migrate init

This will set up the basic folder structure and configuration file. The configuration file contains connection strings for your Postgres database, and you can customize it to suit your needs. Graphile Migrate also provides features like setting placeholders which are special strings (i.e. :DATABASE_NAME or :DATABASE_USER) that will evaluate to a value in the final migration. This value can be evaluated from env variables for example. You can also define hooks for executing custom commands during or after the migration process.

Fast and Seamless Migration Workflow:

One of the key advantages of Graphile Migrate is its speed. It runs migrations extremely fast, making the development workflow seamless. The watch mode allows you to continuously run the migration command, automatically executing the migration whenever there are changes in the SQL file. This immediate feedback enables quick iteration and boosts developer confidence that the migration scripts are correct.

Principle #3: Roll Forward Migrations

Graphile Migrate exclusively supports roll forward migrations. Unlike rollbacks, which revert changes, roll forward migrations only move forward in the migration history. Here’s why this approach is beneficial:

• No Need for Rollbacks: By focusing solely on forward progress, developers avoid the complexity of managing backward migrations. This simplifies the schema evolution process.
• Consistently Reproducible Schema: Roll forward migrations maintain a clean and reproducible schema, making it easier to collaborate and deploy changes across development, staging, and production environments.

Principle #4: Testing Database Migrations:

By writing integration tests for your migrations, you can ensure their correctness and catch any issues early. Testing also plays a crucial role in maintaining a consistently reproducible chain of events, which leads to the same final state every time.
An example of a database integration test would be:

1. spawn a new test database
2. run migrations
3. seed test data for different scenarios
4. execute functions, triggers etc.
5. make assertions
6. clean up the data

Conclusion

Ditching bloated ORMs and adopting a better approach to Postgres database migrations can greatly enhance your development workflow. By following the principles of writing migrations in SQL, ensuring idempotency, and leveraging the power of tools like Graphile Migrate, you can simplify the process and achieve a cleaner, consistently reproducible schema. So why not give it a try and level up your database migration game?

Remember, Your mastery of SQL will elevate your development skills and enable you to build robust, scalable applications. Happy migrating!

Note: To learn more about the advantages of PostgreSQL and why it's a strong choice even for non-relational data, check out this video:

Don't forget to subscribe to the Let's Talk Dev YouTube channel!