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