Support many-to-many relationship in DBML

Feature Suggestions
1

As per discussed at these places:

Consider supporting native many-to-many relationship in DBML.

Current proposed syntax:

Table books {
  id int [pk]
  name varchar
}
Table authors {
  id [pk]
  name varchar
}
Ref books_authors: books.id <> authors.id // many-to-many

Equivalent:

Table books {
  id int [pk]
  name varchar
}
Table authors {
  id [pk]
  name varchar
}
Table books_authors {
  book_id int
  author_id int
  indexes {
    (book_id, author_id) [pk]
  }
}
Ref: books.id > books_authors.book_id
Ref: authors.id > books_authors.author_id

Please upvote so that we can prioritize accordingly.

2

I have no idea why this is not implemented and it needs a vote, its basic ERD stuff.

3

From this notation, what would you expect the generated SQL to look like?

If it takes only the primary keys for field names the SQL will not be valid - two fields will have the same name: id - so you would have to prepend the table name: books_id

But if people already use distinct primary key names, e.g. if the books table already has a primary key book_id, you would end up with the join table field called books_book_id.

Seems to me the choice will always annoy someone :slight_smile:

4

Apologies, I think the correct syntax should be follows (the .id should be explicit)

Ref books_authors: books.id <> authors.id // many-to-many

And I think it will be equivalent to the following DBML:

Table books {
  id int [pk]
  name varchar
}
Table authors {
  id [pk]
  name varchar
}
Table books_authors {
  id int [pk]
  book_id int
  author_id int
}
Ref: books.id > books_authors.book_id
Ref: authors.id > books_authors.author_id

But if people already use distinct primary key names, e.g. if the books table already has a primary key book_id , you would end up with the join table field called books_book_id .

Yes you’re right, though I guess if they really care about the structure of the bridge table, they can opt to go with the explicit (two many-to-ones) route.

5

The id primary key in this table is not necessary, the primary key needs to be a composite of the book_id and author_id, otherwise you have the possibility for duplicate rows.

Many ORM frameworks rely on a single PK per table but not all databases are designed for use with ORMs.

1 Like
6

You’re right, thanks for the suggestion! A simpler/better one should be:

Table books_authors {
  book_id int
  author_id int
  indexes {
    (book_id, author_id) [pk]
  }
}

I’ve updated the original post.

7

Update June 2022: This feature has been developed and released. Please see below for the official announcement.

8

This doesn’t seem to actually work. All I get is Expected "." but ":" found. from any attempt at exercising this syntax:

Table orders {
  id integer [primary key]
  created_at timestamp
}

Table fulfillments {
  id integer [primary key]
  created_at timestamp
}

Ref orders_fulfillments: orders.id <> fulfillments.id
9

Hi @nurple,

The DBML syntax you provided is correct. The resulting diagram is shown below:

image
image1243×579 31.7 KB

Please provide more information about the issue you are experiencing so that we can assist you effectively, such as:

  • Screenshot of the error
  • The DBML content that raises the error

Regards.

1 Like