Cross-database compatibility

[matthewhartstonge]

Hey Team,

First off, thank you for this awesome project!

I'm currently working on a use case where it's "Bring Your Own DB" (BYODB) and was wondering what the types supported are? The first issue I hit with MySQL and Postgres was binary support for storing UUIDs.

My first reading of the docs suggested that DBML sat at a higher level which led me to believe that would generate the specific types per-database output:

DBML is database-agnostic and designed for the high-level database architecting instead of low-level database creation

AFAIK you've got two choices, varchar(36) or binary(16) (#544) at least in MySQL world, which should be either uuid or bytea in postgres.

Is there a way to work with different types across multiple database backends where types need to differ?

I would like my DBML to be the single source of truth 😄

Related issues:

• UUID Marked as a Syntax Error in MySQL Adapter #544

Workarounds

• Use varchar(36), which works across both DBs at the expense of being in-optimal in terms of storage.

[nguyenalter]

Hi @matthewhartstonge,

Thank you for sharing your idea!
If I'm interpreting your request correctly, you'd like to define a DBML as follows:

Table users {
  id uuid
}

When exporting to MySQL or PostgreSQL, the resulting SQL should be:

CREATE TABLE "users" (
  "id" varchar(36)
);

This syntax is indeed valid for both MySQL and PostgreSQL.

Could you please confirm if this aligns with your expectations? Your feedback will help us understand your needs better. Thank you!

[matthewhartstonge]

Yeah, pretty much, but does this work for all data types?
For example, under the documentation, its mentioned that we can use any data type:

• type of the data in the column listed as column_type
  • supports all data types, as long as it is a single word (remove all spaces in the data type). Example, JSON, JSONB, decimal(1,2), etc.

To give a minimal reproducible for postgres and mysql, if I use a 'postgres' dialect for types in my DBML:

Table test {
  one   bytea
  two   json
  three money
  four  uuid
}

I expected dbml2sql to generate an output that makes valid Postgres and MySQL in their required dialects doing the mapping for me.

Current Output

Currently, with the above defined DBML I get:

MySQL

dbml2sql database.dbml --mysql -o mysql/schema.sql

🛑 invalid MySQL dialect

CREATE TABLE `test` (
  `one` bytea,
  `two` json,
  `three` money,
  `four` uuid
);

Postgres

dbml2sql database.dbml --postgres -o mysql/schema.sql

✅ valid postgres syntax+dialect

CREATE TABLE "test" (
  "one" bytea,
  "two" json,
  "three" money,
  "four" uuid
);

(my?) Expectation

MySQL

dbml2sql database.dbml --mysql -o mysql/schema.sql

✅ valid MySQL

CREATE TABLE `test` (
  `one` binary,
  `two` longtext,
  `three` decimal(19,2),
  `four` varchar(36)
);

Postgres

dbml2sql database.dbml --postgres -o mysql/schema.sql

✅ valid postgres

CREATE TABLE "test" (
  "one" bytea,
  "two" json,
  "three" money,
  "four" uuid
);

The postgres -> MySQL mappings were found here: https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html

Does that help?