Hugh Garnier de Blog

Tools for PostgreSQL Schema Managements

The article titled Managing Database Schema Changes Efficiently raises a critical issue in software development - managing database schema changes. As a software developer, I have come across this problem on multiple occasions, and I agree with the author’s perspective on the subject.

In today’s fast-paced software development industry, changes in database schema are a common occurrence. However, these changes can be challenging to manage, especially when multiple developers are working on the same project. It is, therefore, essential to have a streamlined process for managing database schema changes.

One approach suggested in the article is to use a version control system such as Git to manage changes to the database schema. This approach ensures that changes to the schema are tracked, and the history of the changes is maintained. This is a good practice and one that I have used in the past.

Another useful approach suggested in the article is to use a database migration tool to manage database schema changes. These tools enable developers to write scripts that make changes to the database schema and track the changes made. The advantage of using a migration tool is that the changes can be applied in a controlled manner, and developers can easily roll back changes if required.

One database migration tool that I have adopted after reading the article mentioned is Schema Guard. It is a database migration tool that allows developers to manage database schema changes using SQL scripts. It tracks the changes made to the database schema and ensures that the changes are applied in the correct order.

Here is an example of how Schema Guard can be used to manage database schema changes:

Assume we have a table named “customer” with the following schema:

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL
);

Now, let’s say we want to add a new column called “address” to the “customer” table. We can do this using the following SQL script:

ALTER TABLE customer ADD COLUMN address VARCHAR(100) NOT NULL;

We can save this SQL script in a file named “V1__Add_address_to_customer_table.sql”. The “V1” prefix indicates that this is the first version of the database schema. We can then use the app to apply this change to the database schema by running the following command:

rdbm migrate

This will apply the changes to the database schema, and Schema Guard will update its metadata table to record that the “V1” script has been applied.

Now, let’s say we want to make another change to the database schema by adding a new table named “orders.” We can create a new SQL script with the following code:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY, 
    customer_id INTEGER NOT NULL, 
    order_date DATE NOT NULL, 
    total_amount DECIMAL(10,2) NOT NULL, 
    FOREIGN KEY (customer_id) REFERENCES customer(id)
);

We can save this SQL script in a file named “V2__Create_orders_table.sql”. The “V2” prefix indicates that this is the second version of the database schema. We can then use Schema Guard to apply this change to the database schema by running the following command:

rdbm migrate

This will apply the changes to the database schema and update tool’s metadata table to record that the “V2” script has been applied.

As we continue to make changes to the database schema, we can create new SQL scripts and apply them using Schema Guard. It will ensure that the changes are applied in the correct order and that the history of the changes is maintained.

Another important concept to consider when managing database schema changes efficiently is version control. Just as code changes are tracked and managed through version control systems such as Git, database schema changes should also be managed in a similar way.

Other way to accomplish version control for database schema changes is to use tools such as Liquibase or Flyway. These tools allow you to define the database schema as code, in the form of XML, YAML or SQL files, and track changes made to the schema over time. They also provide mechanisms for migrating the schema from one version to another and managing rollbacks if necessary.

For example, let’s say we have a table called “users” with the following schema:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

If we need to add a new column to this table, such as “phone_number”, we can create a new Liquibase changelog file called “001_add_phone_number_column.xml” with the following contents:

<changeSet author="me" id="add_phone_number_column">
    <addColumn tableName="users">
        <column name="phone_number" type="varchar(255)"/>
    </addColumn>
</changeSet>

We can then run Liquibase to apply this change to the database, and it will keep track of the fact that this change has been applied. If we need to roll back this change, we can simply run the tool with the “rollback” command, and Liquibase will automatically generate the SQL necessary to undo the change.

Another advantage of using version control for database schema changes is that it makes it easier to collaborate with other developers. Just as multiple developers can work on the same codebase simultaneously using Git, multiple developers can also work on the same database schema simultaneously.

In conclusion, managing database schema changes efficiently is critical to the success of any software project that relies on a database. By following best practices such as using a migration tool, version control, and testing, we can ensure that our database schema changes are made in a controlled and predictable way, with minimal disruption to our applications.