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

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

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.

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

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.

1 Like

Thank you so much Phuc_Nguyen!

I will do that then!