Feature: Add --no-clobber switch to dbml2sql

[cnagel-wti]

The SQL generated from DBML assumes it is going into an empty database. It might be useful to include a --no-clobber flag in the command to modify the output to add protective clauses to the SQL.

For example, instead of

CREATE SCHEMA "mySchema"

--no-clobber would emit

CREATE SCHEMA IF NOT EXISTS "mySchema"

Some object types require a more comprehensive template than iF NOT EXISTS:

DO $$
BEGIN
  CREATE TYPE "mySchema"."email_type" AS ENUM (
    'PERSONAL',
    'WORK',
    'OTHER'
  );
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conrelid = 'mySchema.email'::regclass
      AND contype = 'f'
      AND conname IN ('fk_email_contact', 'vip_contact_id_fkey')
  ) THEN
    ALTER TABLE "identity"."email"
      ADD CONSTRAINT "fk_email_contact"
      FOREIGN KEY ("contact_id") REFERENCES "identity"."customer" ("id") DEFERRABLE INITIALLY IMMEDIATE;
  END IF;
END $$;

NOTE: In order to protect constraints in this way, DBML would need to name them deterministically so the names could be used in the protective query shown above.

[nguyenalter]

Hi @cnagel-wti,

Could you share your use case that requires the --no-clobber flag? We will consider if it makes sense.

Regards.

[cnagel-wti]

Generated DBML SQL currently works best for a pristine database. But in real projects, especially before a formal migration chain exists, the same generated schema may need to be rerun against a database that already has some schemas, tables, indexes, enum types, constraints, and preserved test/import data.

Without a --no-clobber, rerunning generated SQL either fails on existing objects or requires substantial post-processing.

A --no-clobber mode would let DBML output serve as a safe bootstrap as the database evolves.

A related idea might even be to include some DBML syntax to include in an evolving design that generates explicit DROP statements.

Drops {
  mySchema.fish_type // enum
  mySchema.water_ph_range_loookup // table
}

And now that I think of that, perhaps even an explicit --clobber switch too, which adds a drop before every object's create?

dbml2sql  newSchema.dbml --postgres --clobber

Would output

DROP TABLE IF EXISTS mySchema.fish_pond;
CREATE TABLE mySchema.fish_pond {
...
}

[huydo862003]

Hi @cnagel-wti, I think that makes sense. Designs evolve often and unavoidably. This is a pretty complex use case though, so please allow us some time to prioritize. Thank you