Postgresql Multi-Tenant Migration Using Flyway Tool

PostgreSQL is an open-source relational database management system (RDBMS) known for its robustness, flexibility, and advanced features such as multi-version concurrency control, full-text search, and support for JSON and other data types. It is often used for large, complex, and high-traffic web, mobile and other applications and is available for many platforms including Windows, Linux, and MacOS.

In PostgreSQL, multi-tenancy refers to the ability to have multiple distinct groups of users, called tenants, sharing the same database system while keeping their data isolated from one another. This can be achieved through different approaches such as schema-based or database-based multi-tenancy.

What is Flyway Migration Tool?

Flyway is a database migration tool that allows you to manage changes to your database schema, such as creating or modifying tables, in a versioned and organized way. To run a migration of custom schema manually with Flyway, you can use the command-line tool to execute the desired migration. For example, you can use the command flyway migrate to apply pending migrations with -locations flag followed by the file path.

flyway -locations=filesystem:{file_path} migrate

We will add new column to our database schema and allowing the customer to access our application while making updates behind the scenes. Lets show you step-by-step process of creating a migration, executing it, and the benefits of the flyway migration tool to manage the database schema. Before deep dive into the topic we will give you short introduction about what is PostgreSQL and flyway migration tool.

Architecture Diagram

Version 1

products

The version 1 folder structure is shown as above, in a public schema, the users table is used for logging in. This table contains the necessary information for authentication, such as username and password. Each user also has their own private schema, where they can store their own data.

V0001__Users.sql

CREATE TABLE users (
 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
 username VARCHAR ( 50 ) UNIQUE NOT NULL,
 password VARCHAR ( 50 ) NOT NULL,
 email VARCHAR ( 255 ) UNIQUE NOT NULL,
 version INTEGER NOT NULL,
 created_on TIMESTAMP NOT NULL
);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_isolation_policy ON users USING (id::TEXT = current_user);

Each user will have a separate schema with the credentials table, which is used to store the password information. This table is protected by encryption and access is limited to authorized users only.

V0001__Credentials.sql

CREATE TABLE credentials (
 id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
 user_id UUID NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
 key VARCHAR ( 100 ) NOT NULL,
 value VARCHAR ( 255 ) NOT NULL,
 created_on TIMESTAMP NOT NULL
);

In Application, login as tenant mark. This will list table contents of Mark.

app

Version 2

app

One common use case for multi-tenant PostgreSQL is adding new fields to existing tables. For example, let’s say you want to add a new field called hello_world to the credentials table. This can be accomplished using a technique called “canary migrations.”

Canary migrations involve creating a new version of the table with the desired changes and gradually rolling it out to a small subset of users. This allows you to test the new changes and ensure that they are working as expected before deploying them to all users.

To begin, you will first create a new version of the credentials table with the hello_world field. This can be done using a standard SQL ALTER TABLE statement.

V0002__Add_hello_world.sql

ALTER TABLE credentials 
ADD COLUMN hello_world VARCHAR ( 100 ) NULL;

To start, you will select a small group of users to migrate to the new table. This can be done by selecting a random subset of users or by manually selecting a specific group. Here we are using an Admin portal to migrate selected users for the new changes.

app

Flyway will execute the migration for the selected users schema and update the version in the users table. So that the updated users will redirect to the new Green servers.

flyway -locations=filesystem:schemas -schemas={UserID} migrate

Output

app

SQL Query

UPDATE public.users SET
version = 2 WHERE
id = '0097bd70-efcb-4566–9b9b-df0d96d26b59';

Conclusion

The Flyway tool for migrating a multi-tenant PostgreSQL database proves to be a highly effective and efficient method for managing and updating the database schema. By re-logging as Tenant ‘mark’, you can introduce them to version 2 deployment that includes a new field. Overall, the Flyway tool is an excellent choice for managing and migrating multi-tenant PostgreSQL databases.

app