migrations, serial columns and foreign keys (postgresql)

[till]

Hi,

I am still working backwards off of a database import but ran into another migration issue.

I built a small test case to reproduce it:

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
)

// Todo holds the schema definition for the Todo entity.
type Todo struct {
	ent.Schema
}

// Fields of the Todo.
func (Todo) Fields() []ent.Field {
	return []ent.Field{
		field.Int("id"),
		field.String("title"),
		field.Int("author"),
	}
}

// Edges of the Todo.
func (Todo) Edges() []ent.Edge {
	return []ent.Edge{
		edge.From("user", User.Type).
			Ref("todo").
			Required().
			Field("author").
			Unique(),
	}
}

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/dialect"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
)

// User holds the schema definition for the User entity.
type User struct {
	ent.Schema
}

// Fields of the User.
func (User) Fields() []ent.Field {
	return []ent.Field{
		field.Int("id").SchemaType(map[string]string{
			dialect.Postgres: "serial",
			dialect.SQLite:   "integer",
		}),
		field.String("name"),
	}
}

// Edges of the User.
func (User) Edges() []ent.Edge {
	return []ent.Edge{
		edge.To("todo", Todo.Type),
	}
}

Follow the tutorial, added a user entity with an edge. Then proceed to add versioned migrations (using atlas to the mix).

So, I execute the migration:

go run -mod=mod ent/migrate/main.go first

And have the following .sql file (I formatted it for easier reading on here):

-- create "users" table
CREATE TABLE "users" (
  "id" serial NOT NULL,
  "name" character varying NOT NULL,
  PRIMARY KEY ("id")
);
-- create "todos" table
CREATE TABLE "todos" (
  "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "title" character varying NOT NULL,
  "author" serial NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "todos_users_todo"
    FOREIGN KEY ("author")
    REFERENCES "users" ("id") 
    ON DELETE NO ACTION
);

When I run a migration, I expect:

• two tables (todos, users ✅)
• user.id being a serial (✅)
• todo.author as some kind of integer (🔴)

However, the todos.author is another serial and that seems unnecessary.

---

On top of that, something I haven't been able to repro with this yet is: in my older software, the initial import shows tables as integer, but with a definition which is equal to my example, it exhibits the same "problem": the first migration will try to convert them to serial and then it wants to add SEQUENCE for each of them.

So imagine this:

1. (import) todo.author = integer
2. first migration (on top of the import):

• alter column integer > serial
• CREATE SEQUENCE IF NOT EXISTS "todos_author_seq" OWNED BY "todos"."author";

[till]

I also tried forcing integer on it, but that didn't help either:

field.Int("author").SchemaType(map[string]string{
	dialect.Postgres: "integer",
})

[till]

OK, I guess one more:

I tested if the required made a difference:

• I made field.Int("author").Optional()
• removed Required() from the edge.From()
• error: 2022/09/25 13:05:40 failed generating migration file: %!w(*errors.errorString=&{alter table "todos": NOT NULL constraint is required for serial column "author"})

Doesn't seem to be related either.

And I think this is where the actual error is: ent will try to convert the column to serial to match the FK constraint.

This is probably a weird edge case with Postgres. It doesn't know that integer will satisfy the requirement. The sequence created after the column is changed to serial in my migration is just an effect of that.

[a8m]

Thanks for reporting this, @till. I’ll give it a look

[till]

@a8m Do you wanna give me a hint what to look for? I've been reading the code base, but can't pin it down. Maybe the builder?

[a8m]

Hey! Sorry for the late response. Saw it in delay as it's a holiday in Israel these days.

Anyway, I created a PR for fixing this. Will update the tests there later today and will merge it.

[till]

Saw the PR was merged:

go get entgo.io/ent@e02622a06401517d70b146378bdc105535f374f7

Fixed! ✅