func (*bun.CreateTableQuery) WithForeignKeys() results in unwanted foreign keys when m2m relationships are in place

[brueli]

Affected Versions: bun @ 1.0.25

Reproduction

package test

import (
	"context"
	"log"
	"time"

	"github.com/uptrace/bun"
)

type Profile struct {
	ID     int64     `bun:",pk,autoincrement"`
	Policy []*Policy `bun:"m2m:policy_to_profiles,join:Profile=Policy"`
}

type Policy struct {
	ID      int64     `bun:",pk,autoincrement"`
	Profile []*Profile `bun:"m2m:policy_to_profiles,join:Policy=Profile"`
}

type PolicyToProfile struct {
	ProfileID int64    `bun:",pk"`
	PolicyID  int64    `bun:",pk"`
	Profile   *Profile `bun:"rel:belongs-to,join:profile_id=id"`
	Policy    *Policy  `bun:"rel:belongs-to,join:policy_id=id"`
}

func DeployModel(db *bun.DB) {
	m2mModels := []interface{}{
		(*PolicyToProfile)(nil),
	}

	tableDefs := []interface{}{
		(*Profile)(nil),
		(*Policy)(nil),
		(*PolicyToProfile)(nil),
	}

	ctx, cancel := context.WithTimeout(context.Background(), 500*time.Millisecond)
	defer cancel()

	// drop all tables
	db.NewDropTable().Model(tableDefs[2]).IfExists().Exec(ctx)
	db.NewDropTable().Model(tableDefs[1]).IfExists().Exec(ctx)
	db.NewDropTable().Model(tableDefs[0]).IfExists().Exec(ctx)

	// register many-2-many models
	for _, m2mModel := range m2mModels {
		db.RegisterModel(m2mModel)
	}

	// create the tables with IfNotExists() and WithForeignKeys() options
	for key, tableDef := range tableDefs {
		if _, err := db.NewCreateTable().Model(tableDef).IfNotExists().WithForeignKeys().Exec(ctx); err != nil {
			log.Printf("cannot create table %d: %s", key, err)
			break
		}
	}

}

This results in the following error:

DROP TABLE IF EXISTS "policy_to_profiles"
DROP TABLE IF EXISTS "policies"
DROP TABLE IF EXISTS "profiles"
CREATE TABLE IF NOT EXISTS "profiles" ("id" BIGSERIAL NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "policies" ("id"))    ←[41m pgdriver.Error: ERROR: relation "policies" does not exist (SQLSTATE=42P01) ←[0m
2022/02/25 18:55:13 cannot create table 0: ERROR: relation "policies" does not exist (SQLSTATE=42P01)

Request for change

bun/query_table_create.go

Line 105 in e6b99ea

func (q *CreateTableQuery) WithForeignKeys() *CreateTableQuery {

WithForeignKeys() produces FOREIGN KEYs even for bun:m2m... relationship declarations.
Since these relationships only exist "virtually", no actual FOREIGN KEY must be produced for Relation.Type == ManyToMany.

I'd suggest to adopt add the method to something like:

func (q *CreateTableQuery) WithForeignKeys() *CreateTableQuery {
	for _, relation := range q.tableModel.Table().Relations {
		// Do not produce FOREIGN KEY for m2m relationships
		if relation.Type == schema.ManyToManyRelation {
			continue
		}
		q = q.ForeignKey("(?) REFERENCES ? (?)",
			Safe(appendColumns(nil, "", relation.BaseFields)),
			relation.JoinTable.SQLName,
			Safe(appendColumns(nil, "", relation.JoinFields)),
		)
	}
	return q
}

Results

With the above fix applied, table creation works as expected.
It might be possible that table deletion has similar issues.

DROP TABLE IF EXISTS "policy_to_profiles"
DROP TABLE IF EXISTS "policies"
DROP TABLE IF EXISTS "profiles"
CREATE TABLE IF NOT EXISTS "profiles" ("id" BIGSERIAL NOT NULL, PRIMARY KEY ("id"))
CREATE TABLE IF NOT EXISTS "policies" ("id" BIGSERIAL NOT NULL, PRIMARY KEY ("id"))
CREATE TABLE IF NOT EXISTS "policy_to_profiles" ("profile_id" BIGINT NOT NULL, "policy_id" BIGINT NOT NULL, PRIMARY KEY ("profile_id", "policy_id"), 
FOREIGN KEY ("profile_id") REFERENCES "profiles" ("id"), 
FOREIGN KEY ("policy_id") REFERENCES "policies" ("id"))