Flexible settings

[RWOverdijk]

I've been wanting to use dbml as the foundation for my project, but it's currently held back due to the lack of options to add custom information.

In my case I need, for example, policies and RLS markers. I was thinking of using the table settings for this but this is being validated in a switch statement that only allows headercolor (which shouldn't be in dbml imo, and should instead be part of a "plugin" of sorts that enables the feature for the studio) and notes.

If the settings (or some other mechanism like decorators, custom handles or metadata) allow users to define, validate and implement their own, you open up dbml to many more use cases.

For now I'll try to add settings in my fork, but I think it would be nice as a core thing.

Settings {
  rls: true;
  mt_column: company_id;
}

Table companies [mt_column: id] {
  id UUID [pk, default: `gen_random_uuid()`]
  name VARCHAR(255) [not null]
}

Table services {
  id UUID [pk, default: `gen_random_uuid()`]
  name VARCHAR(255) [not null]
  company_id UUID [ref: > companies.id, not null]
}

Not the best example, but it does more or less illustrate what I'd like to be able to do.

[TeaNguyen]

Thank you for sharing your thoughts.

Indeed, we're considering letting users define & store custom metadata for DB objects with inline and the new Metadata concept.

Something like this:

// ----- Inline metadata & settings -----
Table public.merchants [added_by: "TeaNguyen", owner: "TeaNguyen"] {
    id int [pk, added_by: "TeaNguyen", owner: "TeaNguyen"]
    ...
}

// ----- Custom metadata & settings -----
Metadata <field> public.merchants.id {
    note: '''
        lorem ipsum
        ...
    '''
    color: #000000
    added_by: "TeaNguyen" // custom metadata
}

Metadata <table> public.merchants {
    owner: "TeaNguyen" // custom metadata
}

Metadata <schema> public {
    ...
}

Inner thoughts

Why are we introducing the new Metadata concept? Why don't we simply add custom metadata in column settings, as proposed above?

• Flexible: Allow users to decouple the code definition for (custom) metadata/settings from the physical structure. We believe it will be useful for users in working with DBML in the CI/CD pipeline.
• Clean and legible syntax: We do not want a table/column's code definition to become a muddle after users add a lot of custom metadata to its settings.

Feel free to share your thoughts about this.

[kenpb]

Hello,

I'm also interested in this, for the time being I ended up using a JSON object inside a notes property as a workaround.

The example doesn't show it, but I guess this will also apply to table partials, right? Any ideas of an ETA for this feature?

Salutations

[scribahti]

Reposting my comment from #824. Reposting feedback from @hdnax as a reply.

I'm using dbml to version control a Postgres instance, and I'd like to use this tool to model my RLS policies and generate CREATE POLICY scripts.

Suggested markup:

Policy {
  name 'Users can take all actions on their own accounts'
  schema public
  table users
  behavior permissive
  command all
  roles [authenticated]
  using `auth.uid() = user_id`
  check null
}

schema defaults to public if not provided.

behavior can have the values permissive or restrictive. It defaults to permissive if not provided.

command can have the values select, insert, update, delete, or all.

roles accepts a list of roles and defaults to public if none are provided.

using and check default to null if not provided.

[scribahti]

From @hdnax:

Hi @scribahti, thank you for your suggestion. I also saw that you've opened a PR (thanks for your contribution).

Regarding RLS, we're considering an approach listed in this discussion: #676.

Row-level security (RLS) primarily falls into the physical layer (also known as the physical data model and is more DBMS-specific compared to the existing DBML features.

We don't really want DBML to be too coupled to these details just yet. Instead, we want these as add-on metadata (like in the discussion above).

I think it would be nice if you could flesh out these ideas, as we're weighing many options regarding this issue.

Thanks @hdnax.

My goal is to version control my db configuration with dbml. I only have the perspective of running a simple Postgres instance and this discussion is beyond my experience with db mgmt. But I understand that the point here is that certain features like RLS change from platform to platform, ie postgres vs mysql vs mssql etc.

Perhaps one alternative to a universal approach as described in @TeaNguyen 's Metadata example is to define a set of platform specific markups.

dbml2sql already lets the user declare their target database using the following long options:

• --mysql
• --postgres
• --mssql
• --postgres-legacy
• --mysql-legacy
• --mssql-legacy
• --snowflake
• --oracle

As an example, perhaps the suggested Policy markup could belong to a set of Postgres-only markups that get recognized when the user invokes --postgres, triggering the logic to write CREATE POLICY statements. In all other cases dbml2sql would ignore that specific declaration and write a warning to the dbml-error.log.

It's a simple approach that puts the onus on the community to add support for platform-specific features.

[huydo862003]

As an example, perhaps the suggested Policy markup could belong to a set of Postgres-only markups that get recognized when the user invokes --postgres, triggering the logic to write CREATE POLICY statements. In all other cases dbml2sql would ignore that specific declaration and write a warning to the dbml-error.log.

Yes, I do think dbml should have a clean way to import add-ons for platform-specific features while keeping the core dbml clean.

But that may be a bigger change and need careful planning 🤔 So supporting custom metadata may be a temporary short-termed universal approach.

[scribahti]

@hdnax makes sense. You could use the metadata approach to enable RLS, perhaps like:

Metadata <table> public.merchants {
    rls: true
    ...
}

This is very close to @RWOverdijk 's original example.

@hdnax what do you think about nesting the Policy object within Metadata?

[huydo862003]

Yes, something along those lines. But the decision is not yet finalized for us!

[cnagel-wti]

This feature might be a perfect case for the multiple files feature.

// policies.dbml

Metadata <table> hr.user {
    rls: true
    ...
}

policy tenant_isolation on hr.user
  for select
  using (tenant_id = current_tenant_id())

policy tenant_write on hr.user
  for insert, update
  check (tenant_id = current_tenant_id())
  
// user.dbml
  
use policies.dbml
  
table hr.users {
  id int pk
  tenant_id int not null
  email text
}

[huydo862003]

Yes, that's something we're having in mind. A separate file for metadata and a main file for schema definition.

[xuantho573]

Hi everyone,

We implemented this in #932. For more information, please see https://dbml.dbdiagram.io/syntax/enrichment-visualization#custom-metadata