test_connection_to_multiple_db

This is a django test project to see how to connect 2 postgres databases with foreign key from one table in database1 to second table in database2.

  • Project uses django-rest-framework to serve big dataset
  • Displaying data in datatable

Here is the code to create tables in 2 DBs :
CREATE TABLE public.vente_vente ( id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY, date_vente date NOT NULL, prix_de_vente numeric(10, 2) NOT NULL, client_id int4 NOT NULL, vendeur_id int4 NOT NULL, CONSTRAINT vente_vente_pkey PRIMARY KEY (id) ); CREATE INDEX vente_vente_client_id_2ed41f10 ON public.vente_vente USING btree (client_id); CREATE INDEX vente_vente_vendeur_id_53db2fcf ON public.vente_vente USING btree (vendeur_id);


In second database exercices
CREATE TABLE public.vendeurs ( vendeur_id int4 NOT NULL DEFAULT nextval('vendeurs_seq'::regclass), nom varchar(50) NOT NULL, prenom varchar(50) NOT NULL, adr_id int4 NOT NULL, CONSTRAINT vendeurs_pkey PRIMARY KEY (vendeur_id) );

CREATE TABLE public.clients ( client_id int4 NOT NULL DEFAULT nextval('clients_seq'::regclass), categorie bpchar(6) NOT NULL DEFAULT 'GC-001'::bpchar, nom varchar(50) NOT NULL, prenom varchar(50) NOT NULL, adr_id int4 NOT NULL, CONSTRAINT clients_pkey PRIMARY KEY (client_id) );

I have used raw query with a dblink in postgres defined via the sql command : SELECT dblink_connect('your_connection_name', 'host=localhost dbname=exercices user=postgres password=xxxx');

Then in my filter i have to user the correct cursor and execute the raw query using the dblink:
Vente.objects.raw("""SELECT tb1.id , tb1.date_vente, tb1.prix_de_vente, tb2.nom FROM vente_vente tb1 LEFT JOIN ( SELECT * FROM dblink('host=localhost dbname=exercices user=postgres password=xxxx','SELECT vendeur_id, nom FROM vendeurs') AS tb2(vendeur_id int, nom text) ) AS tb2 ON tb2.vendeur_id = tb1.vendeur_id ORDER BY tb2.nom

I have also used a little bit of htmx and a sweetalert to display a popup on submit new object