Habilelabs-Logo
Blog

Cross-Database with Association in Postgres with Sequelize

July 1st, 2022 . 5 minutes read
Blog featured image

Hello folks,

Today I’m going to share one of my real-time experiences when I was working on a project.

Now, what I’m about to describe is one of the most challenging cases when it comes to databases.

We were using NodeJS with Sequelize ORM. The project had a heavy database (approx. 10 -12 GB) in the initial phase, and approx. 70% of the project database data was common for all regions/instances. Even the tables were tightly coupled as lots of relations were present b/w tables.

So, the requirement was to set up multiple instances of the project in different G-cloud Regions/Geolocation. How did we proceed with it?? See below-

Separate Common Data from Database

Now that you understand the situation, let me tell you how did we deal with it-

We cloned the master database into 2 different Sequelize instances, i.e. master DB and cloned DB, and then refactored all APIs/code conditionally to fetch data from different servers

But what about the table’s associations as I already mentioned tables were tightly coupled, and we are using lots of associations (one-to-one, one-to-many, many-to-one, many-to-many)?

When we have a single DB, we can easily use includes (in Sequelize) or use join(in raw query) to fetch data from the child table with the help of a forging key, but when we have 2 different database servers then we can’t use joins b/w 2 different DB server.

What we needed to do was-

  • To run multiple queries to fetch data from 2 different DB servers
  • To use some extra code to merge both query results
  • To send the result back to the web app

After considering all these points, what do you think is the best approach to handle this case? Well, we will discuss here a solution that takes far less effort. Let’s go then-

Solution

For this scenario, we will use a foreign-data wrapper, PostgreSQL support multiple foreign-data wrappers to connect with remote/external database servers, as we are using PostgreSQL database, so we will use POSTGRES_FDWextension provided by PostgreSQL.

POSTGRES_FDW extension allows us to connect with remote PostgreSQL server and helps to fetch data from remote tables same as we do with a local server without any extra subquery, you can easily query from remote tables and also apply joins b/w local tables and foreign table.

Configurations

To configure POSTGRES_FDW,

  1. Enable POSTGRES_FDW extension.

CREATE EXTENSION postgres_fdw;       

  • Create server connection for remote/external database.

CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'demo2', port '5432');

  • Create user mapping to access.

CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres');

  • Sync database schema

IMPORT FOREIGN SCHEMA public FROM SERVER fdw_server INTO public;

Example

Let’s take an example, we have 1 database containing 2 tables (existing situation)

  • User Table
  • User_license Table

Now as per our requirement, we need to store common data in 1 DB and the client specifies data in another DB, so we need to create 2 different database servers (master DB server and client DB server)

  1. Create one database Ex: client_db

CREATE DATABASE client_db;

  • Create table users inside client_dbdatabase

CREATE TABLE IF NOT EXISTS public.users ( id uuid NOT NULL,  name character varying(255) NOT NULL, email_id character varying(255) NOT NULL, status boolean DEFAULT false, CONSTRAINT users_pkey PRIMARY KEY (id));

  • Insert some dummy data in the users table-

INSERT INTO public.users(id, name, email_id) VALUES ('3a79173b-7357-4fde-af46-1851ab7c908a', 'Om Sharma', 'om.sharma@outlook.in'),                     ('3a79173b-7357-4fde-af46-1851ab7c908b', 'Mahendra Sharma', 'mahendra.sharma@outlook.in');

  • Create 2nd database Ex: master_db

CREATE DATABASE master_db;

  • Create table user_licenses inside master_dbdatabase

CREATE TABLE IF NOT EXISTS public.user_licenses( id uuid NOT NULL, count integer DEFAULT 0, user_iduuid, expiration_date timestamp with time zone, status boolean DEFAULT false, CONSTRAINT user_licenses_pkey PRIMARY KEY (id));

  • Insert some dummy data in the user_licenses table-

INSERT INTO public.user_licenses(id, count, user_id) VALUES ('4062fb3d-794a-4590-ac9a-9d15f8b0ee12', 10,'3a79173b-7357-4fde-af46-1851ab7c908a'), ('4062fb3d-794a-4590-ac9a-9d15f8b0ee13',4,'3a79173b-7357-4fde-af46-1851ab7c908b');

Now, we need to configure and connect the master database with the child database using postgres_fdw extension.

  • Enable postgres_fdw extension-

CREATE EXTENSION postgres_fdw;

  • Create server connection for the remote database-

CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'master_db', port '5432');

  • Create user mapping to access-

CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres');

  • Sync public schema

IMPORT FOREIGN SCHEMA public FROM SERVER fdw_server INTO public;

Now we are done with configurations, you can verify the changes by running this query-

You can also try some other queries to verify connections- 

  • Query from local table
  • Select * from users
  • Query from foreign table
  • Select * from user_licenses
  • apply joins b/w local and remote tables
  • Select * from users join “user_licenses” on users.id = user_licenses.user_id

And this was how we were able to deal with this challenge.

Thanks for reading.😊 Hope you found the blog insightful.

Author: Om Sharma
Share: