dbml conversion error on CLI

[devinschumacher]

trying dbml2sql or sql2dbml through CLI gives me an empty dbml-error.log file.

no console output.

---

1. i exported from DrawSQL.app, and got this code:

CREATE TABLE "group"(
    "id" BIGINT NOT NULL,
    "url" BIGINT NOT NULL,
    "platform_id" BIGINT NOT NULL,
    "project_id" BIGINT NOT NULL,
    "status" VARCHAR(255) CHECK
        ("status" IN('active', 'deleted')) NOT NULL,
        "column_6" BIGINT NOT NULL
);
ALTER TABLE
    "group" ADD PRIMARY KEY("id");
CREATE TABLE "amazon_store"(
    "id" BIGINT NOT NULL,
    "store" BIGINT NOT NULL,
    "primary_owner" VARCHAR(255) CHECK
        (
            "primary_owner" IN(
                'm.devinschumacher@gmail.com',
                'devin@serp.co'
            )
        ) NOT NULL
);
ALTER TABLE
    "amazon_store" ADD PRIMARY KEY("id");
ALTER TABLE
    "amazon_store" ADD CONSTRAINT "amazon_store_store_unique" UNIQUE("store");
CREATE TABLE "video_post"(
    "id" BIGINT NOT NULL,
    "post_id" BIGINT NOT NULL,
    "thumbnail" VARCHAR(255) NOT NULL,
    "tags" VARCHAR(500) NOT NULL,
    "description" VARCHAR(5000) NOT NULL,
    "product_cards" TEXT NOT NULL,
    "playlists" TEXT NOT NULL,
    "monetization" BOOLEAN NOT NULL,
    "status" VARCHAR(255) CHECK
        (
            "status" IN('not started', 'working', 'done')
        ) NOT NULL,
        "visibility" VARCHAR(255)
    CHECK
        (
            "visibility" IN(
                'private',
                'unlisted',
                'scheduled',
                'publish'
            )
        ) NOT NULL,
        "srt" TEXT NOT NULL,
        "type" VARCHAR(255)
    CHECK
        ("type" IN('short', 'full')) NOT NULL,
        "youtube_id" BIGINT NOT NULL,
        "task_owner" VARCHAR(255)
    CHECK
        (
            "task_owner" IN(
                'hasanul',
                'joey',
                'frances',
                'sophia',
                'devin'
            )
        ) NOT NULL,
        "url_other_channel" VARCHAR(255) NOT NULL,
        "status_check_copyright" VARCHAR(255)
    CHECK
        (
            "status_check_copyright" IN(
                'downloading',
                'uploaded to burner',
                'done'
            )
        ) NOT NULL,
        "copyright_channel" VARCHAR(255)
    CHECK
        (
            "copyright_channel" IN('not affected', 'strike')
        ) NOT NULL,
        "copyright_visibility" VARCHAR(255)
    CHECK
        (
            "copyright_visibility" IN(
                'not affected',
                'partially blocked',
                'blocked'
            )
        ) NOT NULL,
        "copyright_monetization" VARCHAR(255)
    CHECK
        (
            "copyright_monetization" IN('Eligible', 'Ineligible', 'Shared')
        ) NOT NULL
);
ALTER TABLE
    "video_post" ADD PRIMARY KEY("id");
ALTER TABLE
    "video_post" ADD CONSTRAINT "video_post_youtube_id_unique" UNIQUE("youtube_id");
ALTER TABLE
    "video_post" ADD CONSTRAINT "video_post_url_other_channel_unique" UNIQUE("url_other_channel");
CREATE TABLE "platform"(
    "id" BIGINT NOT NULL,
    "name" VARCHAR(255) CHECK
        (
            "name" IN(
                'medium',
                'quora',
                'linkedin',
                'reddit',
                'youtube',
                'gist'
            )
        ) NOT NULL
);
ALTER TABLE
    "platform" ADD PRIMARY KEY("id");
CREATE TABLE "topic"(
    "id" BIGINT NOT NULL,
    "name" VARCHAR(255) NOT NULL
);
ALTER TABLE
    "topic" ADD PRIMARY KEY("id");
ALTER TABLE
    "topic" ADD CONSTRAINT "topic_name_unique" UNIQUE("name");
CREATE TABLE "project"(
    "id" BIGINT NOT NULL,
    "name" BIGINT NOT NULL
);
ALTER TABLE
    "project" ADD PRIMARY KEY("id");
ALTER TABLE
    "project" ADD CONSTRAINT "project_name_unique" UNIQUE("name");
CREATE TABLE "email"(
    "id" BIGINT NOT NULL,
    "email_address" VARCHAR(255) NOT NULL,
    "password" VARCHAR(255) NOT NULL,
    "recovery_email" VARCHAR(255) NOT NULL,
    "recovery_email_password" VARCHAR(255) NOT NULL,
    "two_factor_type" VARCHAR(255) CHECK
        (
            "two_factor_type" IN(
                'recovery email code',
                'recovery email confirmation',
                'phone code',
                'auth app'
            )
        ) NOT NULL,
        "status" VARCHAR(255)
    CHECK
        (
            "status" IN('accessible', 'inaccessible')
        ) NOT NULL
);
ALTER TABLE
    "email" ADD PRIMARY KEY("id");
ALTER TABLE
    "email" ADD CONSTRAINT "email_email_address_unique" UNIQUE("email_address");
CREATE TABLE "keyword"(
    "id" BIGINT NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "plural" VARCHAR(256) NOT NULL
);
ALTER TABLE
    "keyword" ADD PRIMARY KEY("id");
ALTER TABLE
    "keyword" ADD CONSTRAINT "keyword_name_unique" UNIQUE("name");
CREATE TABLE "amazon_tag"(
    "id" BIGINT NOT NULL,
    "tag" VARCHAR(100) NOT NULL,
    "status" VARCHAR(255) CHECK
        ("status" IN('unused', 'used')) NOT NULL,
        "fk_amazon_store" SMALLINT NOT NULL
);
ALTER TABLE
    "amazon_tag" ADD PRIMARY KEY("id");
ALTER TABLE
    "amazon_tag" ADD CONSTRAINT "amazon_tag_tag_unique" UNIQUE("tag");
COMMENT
ON COLUMN
    "amazon_tag"."status" IS 'not sure we need this bc maybe is supposed to be a calculated field?';
CREATE TABLE "posts_topics"(
    "id" BIGINT NOT NULL,
    "post_id" BIGINT NOT NULL,
    "topic_id" BIGINT NOT NULL
);
ALTER TABLE
    "posts_topics" ADD PRIMARY KEY("id");
CREATE TABLE "article_post"(
    "id" BIGINT NOT NULL,
    "post_id" BIGINT NOT NULL,
    "body" TEXT NOT NULL
);
ALTER TABLE
    "article_post" ADD PRIMARY KEY("id");
CREATE TABLE "profiles"(
    "id" BIGINT NOT NULL,
    "url" BIGINT NOT NULL,
    "username" BIGINT NOT NULL,
    "status" VARCHAR(255) CHECK
        ("status" IN('200', '410', '404')) NOT NULL,
        "status_checked" DATE NOT NULL,
        "password" BIGINT NOT NULL,
        "email_id" BIGINT NOT NULL,
        "platform_id" BIGINT NOT NULL,
        "api_key" VARCHAR(1000) NOT NULL,
        "auth_token" VARCHAR(1000) NOT NULL,
        "source" VARCHAR(255)
    CHECK
        (
            "source" IN('hasanul', 'shahin', 'automated')
        ) NOT NULL
);
ALTER TABLE
    "profiles" ADD CONSTRAINT "profiles_email_id_platform_id_unique" UNIQUE("email_id", "platform_id");
ALTER TABLE
    "profiles" ADD PRIMARY KEY("id");
ALTER TABLE
    "profiles" ADD CONSTRAINT "profiles_url_unique" UNIQUE("url");
COMMENT
ON COLUMN
    "profiles"."source" IS 'who built the thing';
CREATE TABLE "post"(
    "id" BIGINT NOT NULL,
    "url" VARCHAR(500) NOT NULL,
    "title" VARCHAR(500) NOT NULL,
    "keyword_id" BIGINT NOT NULL,
    "publish_date" DATE NOT NULL,
    "platform_id" BIGINT NOT NULL,
    "profile_id" BIGINT NOT NULL,
    "status" VARCHAR(255) CHECK
        ("status" IN('live', 'dead')) NOT NULL,
        "status_updated" DATE NOT NULL,
        "ahrefs_kw" INTEGER NOT NULL,
        "ahrefs_traffic" INTEGER NOT NULL,
        "ahrefs_rd" INTEGER NOT NULL,
        "ahrefs_updated" DATE NOT NULL,
        "shortlink" VARCHAR(255)
    CHECK
        (
            "shortlink" IN('serp.ly', 'sndn.to')
        ) NOT NULL,
        "amazon_tag_id" BIGINT NOT NULL,
        "group_id" BIGINT NOT NULL
);
ALTER TABLE
    "post" ADD PRIMARY KEY("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_url_unique" UNIQUE("url");
ALTER TABLE
    "profiles" ADD CONSTRAINT "profiles_email_id_foreign" FOREIGN KEY("email_id") REFERENCES "email"("id");
ALTER TABLE
    "profiles" ADD CONSTRAINT "profiles_platform_id_foreign" FOREIGN KEY("platform_id") REFERENCES "platform"("id");
ALTER TABLE
    "video_post" ADD CONSTRAINT "video_post_post_id_foreign" FOREIGN KEY("post_id") REFERENCES "post"("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_amazon_tag_id_foreign" FOREIGN KEY("amazon_tag_id") REFERENCES "amazon_tag"("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_profile_id_foreign" FOREIGN KEY("profile_id") REFERENCES "profiles"("id");
ALTER TABLE
    "group" ADD CONSTRAINT "group_project_id_foreign" FOREIGN KEY("project_id") REFERENCES "project"("id");
ALTER TABLE
    "posts_topics" ADD CONSTRAINT "posts_topics_post_id_foreign" FOREIGN KEY("post_id") REFERENCES "post"("id");
ALTER TABLE
    "posts_topics" ADD CONSTRAINT "posts_topics_topic_id_foreign" FOREIGN KEY("topic_id") REFERENCES "topic"("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_group_id_foreign" FOREIGN KEY("group_id") REFERENCES "group"("id");
ALTER TABLE
    "article_post" ADD CONSTRAINT "article_post_post_id_foreign" FOREIGN KEY("post_id") REFERENCES "post"("id");
ALTER TABLE
    "amazon_tag" ADD CONSTRAINT "amazon_tag_fk_amazon_store_foreign" FOREIGN KEY("fk_amazon_store") REFERENCES "amazon_store"("id");
ALTER TABLE
    "group" ADD CONSTRAINT "group_platform_id_foreign" FOREIGN KEY("platform_id") REFERENCES "platform"("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_keyword_id_foreign" FOREIGN KEY("keyword_id") REFERENCES "keyword"("id");
ALTER TABLE
    "post" ADD CONSTRAINT "post_platform_id_foreign" FOREIGN KEY("platform_id") REFERENCES "platform"("id");

2. Uploaded to dbdiagram.io and got:

Table "group" {
  "id" BIGINT [not null]
  "url" BIGINT [not null]
  "platform_id" BIGINT [not null]
  "project_id" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "column_6" BIGINT [not null]
}

Table "amazon_store" {
  "id" BIGINT [not null]
  "store" BIGINT [not null]
  "primary_owner" VARCHAR(255) [not null]
}

Table "video_post" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "thumbnail" VARCHAR(255) [not null]
  "tags" VARCHAR(500) [not null]
  "description" VARCHAR(5000) [not null]
  "product_cards" TEXT [not null]
  "playlists" TEXT [not null]
  "monetization" BOOLEAN [not null]
  "status" VARCHAR(255) [not null]
  "visibility" VARCHAR(255) [not null]
  "srt" TEXT [not null]
  "type" VARCHAR(255) [not null]
  "youtube_id" BIGINT [not null]
  "task_owner" VARCHAR(255) [not null]
  "url_other_channel" VARCHAR(255) [not null]
  "status_check_copyright" VARCHAR(255) [not null]
  "copyright_channel" VARCHAR(255) [not null]
  "copyright_visibility" VARCHAR(255) [not null]
  "copyright_monetization" VARCHAR(255) [not null]
}

Table "platform" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
}

Table "topic" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
}

Table "project" {
  "id" BIGINT [not null]
  "name" BIGINT [not null]
}

Table "email" {
  "id" BIGINT [not null]
  "email_address" VARCHAR(255) [not null]
  "password" VARCHAR(255) [not null]
  "recovery_email" VARCHAR(255) [not null]
  "recovery_email_password" VARCHAR(255) [not null]
  "two_factor_type" VARCHAR(255) [not null]
  "status" VARCHAR(255) [not null]
}

Table "keyword" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
  "plural" VARCHAR(256) [not null]
}

Table "amazon_tag" {
  "id" BIGINT [not null]
  "tag" VARCHAR(100) [not null]
  "status" VARCHAR(255) [not null, note: 'not sure we need this bc maybe is supposed to be a calculated field?']
  "fk_amazon_store" SMALLINT [not null]
}

Table "posts_topics" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "topic_id" BIGINT [not null]
}

Table "article_post" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "body" TEXT [not null]
}

Table "profiles" {
  "id" BIGINT [not null]
  "url" BIGINT [not null]
  "username" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "status_checked" DATE [not null]
  "password" BIGINT [not null]
  "email_id" BIGINT [not null]
  "platform_id" BIGINT [not null]
  "api_key" VARCHAR(1000) [not null]
  "auth_token" VARCHAR(1000) [not null]
  "source" VARCHAR(255) [not null, note: 'who built the thing']
}

Table "post" {
  "id" BIGINT [not null]
  "url" VARCHAR(500) [not null]
  "title" VARCHAR(500) [not null]
  "keyword_id" BIGINT [not null]
  "publish_date" DATE [not null]
  "platform_id" BIGINT [not null]
  "profile_id" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "status_updated" DATE [not null]
  "ahrefs_kw" INTEGER [not null]
  "ahrefs_traffic" INTEGER [not null]
  "ahrefs_rd" INTEGER [not null]
  "ahrefs_updated" DATE [not null]
  "shortlink" VARCHAR(255) [not null]
  "amazon_tag_id" BIGINT [not null]
  "group_id" BIGINT [not null]
}

Ref "profiles_email_id_foreign":"email"."id" < "profiles"."email_id"

Ref "profiles_platform_id_foreign":"platform"."id" < "profiles"."platform_id"

Ref "video_post_post_id_foreign":"post"."id" < "video_post"."post_id"

Ref "post_amazon_tag_id_foreign":"amazon_tag"."id" < "post"."amazon_tag_id"

Ref "post_profile_id_foreign":"profiles"."id" < "post"."profile_id"

Ref "group_project_id_foreign":"project"."id" < "group"."project_id"

Ref "posts_topics_post_id_foreign":"post"."id" < "posts_topics"."post_id"

Ref "posts_topics_topic_id_foreign":"topic"."id" < "posts_topics"."topic_id"

Ref "post_group_id_foreign":"group"."id" < "post"."group_id"

Ref "article_post_post_id_foreign":"post"."id" < "article_post"."post_id"

Ref "amazon_tag_fk_amazon_store_foreign":"amazon_store"."id" < "amazon_tag"."fk_amazon_store"

Ref "group_platform_id_foreign":"platform"."id" < "group"."platform_id"

Ref "post_keyword_id_foreign":"keyword"."id" < "post"."keyword_id"

Ref "post_platform_id_foreign":"platform"."id" < "post"."platform_id"

3. But when i tried to convert it to DBML with the CLI i just got an empty log file

4. I tried taking the dbdiagram.io code, and convert that with CLI to sql and got empty dbml-error.log

[pierresouchay]

@devinschumacher Not very clear to me... What is the version you are testing with?

if I pass you SQL file to the CLI (version 3.2.0):

sql2dbml --version
3.2.0

I have this result:

Table "group" {
  "id" BIGINT [not null]
  "url" BIGINT [not null]
  "platform_id" BIGINT [not null]
  "project_id" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "column_6" BIGINT [not null]
}

Table "amazon_store" {
  "id" BIGINT [not null]
  "store" BIGINT [not null]
  "primary_owner" VARCHAR(255) [not null]
}

Table "video_post" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "thumbnail" VARCHAR(255) [not null]
  "tags" VARCHAR(500) [not null]
  "description" VARCHAR(5000) [not null]
  "product_cards" TEXT [not null]
  "playlists" TEXT [not null]
  "monetization" BOOLEAN [not null]
  "status" VARCHAR(255) [not null]
  "visibility" VARCHAR(255) [not null]
  "srt" TEXT [not null]
  "type" VARCHAR(255) [not null]
  "youtube_id" BIGINT [not null]
  "task_owner" VARCHAR(255) [not null]
  "url_other_channel" VARCHAR(255) [not null]
  "status_check_copyright" VARCHAR(255) [not null]
  "copyright_channel" VARCHAR(255) [not null]
  "copyright_visibility" VARCHAR(255) [not null]
  "copyright_monetization" VARCHAR(255) [not null]
}

Table "platform" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
}

Table "topic" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
}

Table "project" {
  "id" BIGINT [not null]
  "name" BIGINT [not null]
}

Table "email" {
  "id" BIGINT [not null]
  "email_address" VARCHAR(255) [not null]
  "password" VARCHAR(255) [not null]
  "recovery_email" VARCHAR(255) [not null]
  "recovery_email_password" VARCHAR(255) [not null]
  "two_factor_type" VARCHAR(255) [not null]
  "status" VARCHAR(255) [not null]
}

Table "keyword" {
  "id" BIGINT [not null]
  "name" VARCHAR(255) [not null]
  "plural" VARCHAR(256) [not null]
}

Table "amazon_tag" {
  "id" BIGINT [not null]
  "tag" VARCHAR(100) [not null]
  "status" VARCHAR(255) [not null, note: 'not sure we need this bc maybe is supposed to be a calculated field?']
  "fk_amazon_store" SMALLINT [not null]
}

Table "posts_topics" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "topic_id" BIGINT [not null]
}

Table "article_post" {
  "id" BIGINT [not null]
  "post_id" BIGINT [not null]
  "body" TEXT [not null]
}

Table "profiles" {
  "id" BIGINT [not null]
  "url" BIGINT [not null]
  "username" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "status_checked" DATE [not null]
  "password" BIGINT [not null]
  "email_id" BIGINT [not null]
  "platform_id" BIGINT [not null]
  "api_key" VARCHAR(1000) [not null]
  "auth_token" VARCHAR(1000) [not null]
  "source" VARCHAR(255) [not null, note: 'who built the thing']
}

Table "post" {
  "id" BIGINT [not null]
  "url" VARCHAR(500) [not null]
  "title" VARCHAR(500) [not null]
  "keyword_id" BIGINT [not null]
  "publish_date" DATE [not null]
  "platform_id" BIGINT [not null]
  "profile_id" BIGINT [not null]
  "status" VARCHAR(255) [not null]
  "status_updated" DATE [not null]
  "ahrefs_kw" INTEGER [not null]
  "ahrefs_traffic" INTEGER [not null]
  "ahrefs_rd" INTEGER [not null]
  "ahrefs_updated" DATE [not null]
  "shortlink" VARCHAR(255) [not null]
  "amazon_tag_id" BIGINT [not null]
  "group_id" BIGINT [not null]
}

Ref "profiles_email_id_foreign":"email"."id" < "profiles"."email_id"

Ref "profiles_platform_id_foreign":"platform"."id" < "profiles"."platform_id"

Ref "video_post_post_id_foreign":"post"."id" < "video_post"."post_id"

Ref "post_amazon_tag_id_foreign":"amazon_tag"."id" < "post"."amazon_tag_id"

Ref "post_profile_id_foreign":"profiles"."id" < "post"."profile_id"

Ref "group_project_id_foreign":"project"."id" < "group"."project_id"

Ref "posts_topics_post_id_foreign":"post"."id" < "posts_topics"."post_id"

Ref "posts_topics_topic_id_foreign":"topic"."id" < "posts_topics"."topic_id"

Ref "post_group_id_foreign":"group"."id" < "post"."group_id"

Ref "article_post_post_id_foreign":"post"."id" < "article_post"."post_id"

Ref "amazon_tag_fk_amazon_store_foreign":"amazon_store"."id" < "amazon_tag"."fk_amazon_store"

Ref "group_platform_id_foreign":"platform"."id" < "group"."platform_id"

Ref "post_keyword_id_foreign":"keyword"."id" < "post"."keyword_id"

Ref "post_platform_id_foreign":"platform"."id" < "post"."platform_id"

=> Do you have another kind of result?

[NQPhuc]

Hi @devinschumacher,

As @pierresouchay have already mentioned, you're probably using a old dbml CLI version. Yours SQL should import just fine on the newest version. To upgrade your CLI to the lastest version, you can use this command: npm i -g @dbml/cli.

Thanks

[devinschumacher]

@NQPhuc im probably using the old version? i literally installed the CLI yesterday from the instructions on your docs.

[NQPhuc]

Hi @devinschumacher,

I think the empty log you mention is the error log. In that case, an empty error log indicate that your SQL was successfully converted.

To output the dbml to of a text file you can use this command: sql2dbml --postgres <path_to_your_sql> -o <out_file_path>

If you omit the -o option, the dbml will be printed on your console and any parsed errors is written into dbml-error.log.

[devinschumacher]

ah okay excellent thank you so much for that! @NQPhuc