DEV Community

Yuya Miyamoto
Yuya Miyamoto

Posted on

[First Post] Created a CLI Tool to Infer Relationships from DB Schemas and Convert Them to DBML

Hello! I’m Ymmy, a web engineer born in 2001.

I’ve just published my first npm package, so I wrote this article to introduce it.

Motivation

When working on both professional and personal projects, I often find myself wanting an ER diagram to understand the database schema more clearly. However, drawing it by hand every time can be tedious, and there are so many tools out there that it can be confusing to choose.

I became interested in automatically generating ER diagrams using Database Markup Language (DBML). But since I couldn’t find many tools that can infer relationships from existing table and column names and generate DBML automatically, I decided to create my own CLI tool and publish it on npm.

Overview of the Tool

The npm package I’ve released is called @ymmy/dbml-relationalizer.

Here are its main features:

  • Automatically generate DBML by fetching schema information from an existing database
  • Read a custom relations.yml file to define additional relationships
  • Infer relationships (through a built-in inference feature) based on table and column naming patterns

You can find the source code on GitHub:

https://github.com/Ymmy833y/dbml-relationalizer

Key Features

  1. Inference

    Automatically determines relationships such as users.id ↔ orders.user_id based on table and column names.

    • You can select the inference strategy (default or identical) to match your naming conventions.

  2. Manual Relationship Definitions

    If needed, you can specify custom column-to-column relationships in a relations.yml file.

    • Supports flexible pattern matching using wildcards (%).

  3. Control Over Self-Referencing Relationships

    By toggling ignoreSelfReferences, you can easily include or exclude self-referencing relationships.

Installation

Via npm 

npm install -g @ymmy/dbml-relationalizer
Enter fullscreen mode Exit fullscreen mode

Usage

1. (Optional) Create relations.yml

Here’s an example:

inference:
  enabled: true
  strategy: default

relations:
  - parentQualifiedColumn: "users.id"
    childQualifiedColumns:
      - "orders.user_id"

ignoreSelfReferences: false
Enter fullscreen mode Exit fullscreen mode
  • inference.enabled: Whether to enable inference
  • inference.strategy: The inference mode that matches your naming conventions
    • default: e.g. users.id → orders.user_id
    • identical: e.g. users.user_id → orders.user_id
  • relations: Manually added relationship definitions
  • ignoreSelfReferences: Whether to exclude self-referencing relationships

For more details, check out the GitHub repository.

2. Run the CLI

Just run the following command to generate DBML:

relation2dbml mysql mysql://user:pass@localhost:3306/dbname -o schema.dbml
Enter fullscreen mode Exit fullscreen mode
  • First argument: Connection type (see here for details)
  • Second argument: Connection string (see here for details)
  • -o, --out-file: Specifies the output file (if omitted, the DBML is printed to stdout)
  • -i, --input-file: Path to relations.yml (default: ./relations.yml)
  • -v, --verbose: Outputs detailed logs

When you run this command, a file named schema.dbml is generated, containing your tables and relationships in DBML format.

Example: Generated DBML

Below is an example of the kind of DBML you might end up with (just for illustration):

Table "user" {
  "id" int [pk, not null, increment]
  ...
}

Table "order" {
  "order_id" int [pk, not null, increment]
  "user_id" int [not null]
  ...
}

Ref "infer_fk_user_order_id":"user"."id" < "order"."user_id"
Enter fullscreen mode Exit fullscreen mode

You can load this into a database design tool to visualize an ER diagram, which is quite handy.

Conclusion

The @ymmy/dbml-relationalizer CLI tool:

  • Fetches your DB schema and converts it to DBML
  • Infers relationships automatically, with optional manual overrides
  • Makes it easy to visualize everything as an ER diagram (for example, by using dbml-renderer)

This workflow may be particularly helpful for those who want a straightforward way to generate and maintain database documentation for personal or medium-scale projects.

If you have any questions or suggestions, feel free to leave a comment or open an issue on GitHub. Your feedback is welcome!

Thank you for reading my very first post!

If you find it interesting, please give it a try. I plan to keep improving it little by little.

See you in the next article!

Top comments (0)