Feature Request: Add foreign key constraints support to DBML conversion from SQL file

albertogr · July 6, 2022, 8:17am

In postgreSQL, constraints are not translated to the DBML file. (I don’t know the behavior for other languages)
It doesn’t matter if it’s done with ALTER TABLE [...] ADD CONSTRAINT [...] FOREIGN KEY [...] or CREATE TABLE [...] CONSTRAINT [...] FOREIGN KEY [...], the constraints are not translated into the resulting DBML file

Phuc_Nguyen · July 8, 2022, 3:23am

Hi albertogr,

From our end, we’re still able to import from PostgreSQL with the syntax

ALTER TABLE […] ADD CONSTRAINT […] FOREIGN KEY […]

.Can you provide a specific example where it might not work?

Thanks.

albertogr · July 11, 2022, 5:48am

This is a sample that doesn’t work for me:

CREATE TABLE public.dim_date
(
    date_id	                integer         	        NOT NULL,
    formatted_date	date	                    NOT NULL,
    day_name	            varchar(9)	                NOT NULL,
    day_of_week	            smallint	                NOT NULL,
    month_name	            varchar(10)	                NOT NULL,
    month	                smallint	                NOT NULL,
    year	                smallint	                NOT NULL,
    CONSTRAINT dim_date_date_id_pkey PRIMARY KEY (date_id) -- This works
) 
TABLESPACE pg_default;

CREATE INDEX formatted_date_index
  ON public.dim_date(formatted_date);

COMMENT ON TABLE public.dim_date
    IS 'Date dimension.';
COMMENT ON COLUMN public.dim_date.date_id	             
    IS 'Date id. Format: yyyymmdd';
COMMENT ON COLUMN public.dim_date.formatted_date
    IS 'Date in date format';
COMMENT ON COLUMN public.dim_date.day_name	            
    IS 'Weekday name';
COMMENT ON COLUMN public.dim_date.day_of_week	            
    IS 'Day number of the week';
COMMENT ON COLUMN public.dim_date.month_name	            
    IS 'Month name';
COMMENT ON COLUMN public.dim_date.month	            
    IS 'Month number';
COMMENT ON COLUMN public.dim_date.year	            
    IS 'Year';

DROP SEQUENCE IF EXISTS sample_table_id_seq;
CREATE SEQUENCE sample_table_id_seq;

CREATE TABLE public.sample_table (
    sample_table_id integer NOT NULL DEFAULT nextval('sample_table_id_seq'::regclass),
    start_date_id integer NOT NULL,
    end_date_id integer NOT NULL,
    code varchar(16) NOT NULL,
    name varchar(128) NOT NULL,
    CONSTRAINT sample_table_sample_table_id_pkey PRIMARY KEY (sample_table_id) -- This works

) TABLESPACE pg_default;

ALTER TABLE public.sample_table ADD CONSTRAINT sample_table_start_date_id_fkey FOREIGN KEY (start_date_id) REFERENCES dim_date; -- This doesn't work
ALTER TABLE public.sample_table ADD CONSTRAINT sample_table_end_date_id_fkey FOREIGN KEY (end_date_id) REFERENCES dim_date; -- This doesn't work

COMMENT ON TABLE public.sample_table
    IS 'This is a sample table.';
COMMENT ON COLUMN public.sample_table.sample_table_id
    IS 'Id of sample table';
COMMENT ON COLUMN public.sample_table.start_date_id
    IS 'Id of start date';
COMMENT ON COLUMN public.sample_table.end_date_id
    IS 'Id of end date';
COMMENT ON COLUMN public.sample_table.code
    IS 'Code';
COMMENT ON COLUMN public.sample_table.name
    IS 'Name';

When I execute the command to translate, I get this error in the log file:

SyntaxError: You have a syntax error at "DB-SAMPLE.sql".
    at C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\utils.js:55:13
    at Array.forEach (<anonymous>)
    at generate (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\utils.js:48:14)
    at importHandler (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\import.js:40:27)
    at Object.sql2dbml (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\lib\cli\index.js:38:23)
    at Object.<anonymous> (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\bin\sql2dbml.js:4:19)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)

ROOT_ERROR:
TypeError: Cannot read properties of null (reading 'forEach')
    at Endpoint.setFields (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\endpoint.js:143:18)
    at new Endpoint (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\endpoint.js:81:11)
    at C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\ref.js:99:31
    at Array.forEach (<anonymous>)
    at Ref.processEndpoints (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\ref.js:98:20)
    at new Ref (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\ref.js:83:11)
    at C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\database.js:173:28
    at Array.forEach (<anonymous>)
    at Database.processSchemaElements (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\database.js:143:16)
    at new Database (C:\Users\Administrador\AppData\Roaming\npm\node_modules\@dbml\cli\node_modules\@dbml\core\lib\model_structure\database.js:99:11)

Then, if I comment the 2 SQL lines where I add the foreign keys, it works perfectly.
I also tried to add the foreign keys inside the CREATE TABLE statement, but it also fails:

CREATE TABLE public.sample_table (
    sample_table_id integer NOT NULL DEFAULT nextval('sample_table_id_seq'::regclass),
    start_date_id integer NOT NULL,
    end_date_id integer NOT NULL,
    code varchar(16) NOT NULL,
    name varchar(128) NOT NULL,
    CONSTRAINT sample_table_sample_table_id_pkey PRIMARY KEY (sample_table_id), -- This works
    CONSTRAINT sample_table_start_date_id_fkey FOREIGN KEY (start_date_id) REFERENCES dim_date,
    CONSTRAINT sample_table_end_date_id_fkey FOREIGN KEY (end_date_id) REFERENCES dim_date

) TABLESPACE pg_default;

Also, in my original script, constraints are added like this (still doesn’t work):

ALTER TABLE public.sample_table 
    ADD CONSTRAINT sample_table_start_date_id_fkey FOREIGN KEY (start_date_id) REFERENCES dim_date,
    ADD CONSTRAINT sample_table_end_date_id_fkey FOREIGN KEY (end_date_id) REFERENCES dim_date; -- This doesn't work

Phuc_Nguyen · July 11, 2022, 1:22pm

Hi albertogr,

Thank you for providing the samples,

The cause of the error is in the last part REFERENCES dim_date. In Postgres, if the references field is not mentioned then the primary key is used.

However, our exporter hadn’t implemented this yet so it doesn’t know which field of dim_date to refer to.

We will try to fix this in the next update. In the meantime, you can refer explicitly to the primary key of the referenced table as a workaround. Like this:

ALTER TABLE public.sample_table ADD CONSTRAINT sample_table_start_date_id_fkey FOREIGN KEY (start_date_id) REFERENCES dim_date (date_id);
ALTER TABLE public.sample_table ADD CONSTRAINT sample_table_end_date_id_fkey FOREIGN KEY (end_date_id) REFERENCES dim_date (date_id);

Hope this will help,
Thanks.

albertogr · July 12, 2022, 6:03am

Thank you so much Phuc_Nguyen!

I will do that then!

Nguyen_Hoang · February 13, 2023, 7:48am

Implemented shorthand references syntax for PostgreSQL and MSSQL in DBML PR#274