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

Yuya Miyamoto — Sun, 26 Jan 2025 01:33:06 +0000

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

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.
Manual Relationship Definitions

If needed, you can specify custom column-to-column relationships in a relations.yml file.
- Supports flexible pattern matching using wildcards (%).
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

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

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

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"

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!

DEV Community: Yuya Miyamoto