Lang
Blog

Cross-Database Association in Postgres with Sequelize

ByOm Sharma
July 1st . 5 min read
Cross-Database Association in Postgres with Sequelize

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_FDW extension 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 tables.

Configurations

To configure POSTGRES_FDW,

CREATE EXTENSION postgres_fdw;

CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'demo2', port '5432');
CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres'); 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)

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)

CREATE DATABASE client_db;
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 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');

cross-database-association_1.webp

CREATE DATABASE master_db;
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 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');

cross-database-association_2.webp

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

CREATE EXTENSION postgres_fdw;
CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'master_db', port '5432');
CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres'); 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-

cross-database-association_3.webp

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.

For more details, you can check this demo git repository.

Thanks for reading.😊 Hope you found the blog insightful. And if you did, don’t forget to hit some claps.😃

Share:
0
+0