Getting error: 'Uncaught PostgresError: relation "id" already exist'

[drewisdorner]

First off, thanks for this awesome project, it seems very nice so far! I stumbled upon a strange error with my current codebase and don't know how I can resolve it. For explanation: I want three tables where the relationships are as following:

places < 1 ------- n > boxes < 1 ------ n > items

I have the following model-declarations:

export class Place extends Model {
	static table = "places";

	static fields = {
		id: {
			type: DataTypes.INTEGER,
			primaryKey: true,
		},
		name: DataTypes.TEXT,
	};

	static boxes() {
		return this.hasMany(Box);
	}
}

export class Box extends Model {
	static table = "boxes";

	static fields = {
		id: {
			type: DataTypes.INTEGER,
			primaryKey: true,
		},
		name: DataTypes.TEXT,
		placeId : Relationships.belongsTo(Place),
	};

	static place() {
		return this.hasOne(Place);
	}

	static items() {
		return this.hasMany(Item);
	}
}

export class Item extends Model {
	static table = "items";

	static fields = {
		id: {
			type: DataTypes.INTEGER,
			primaryKey: true,
		},
		name: DataTypes.TEXT,
		boxId : Relationships.belongsTo(Box),
	};

	static box() {
		return this.hasOne(Box);
	}
}

And when I try to do db.sync(); with these models already linked together with db.link([Place, Box, Item]); I'm getting this error:

error: Uncaught PostgresError: relation "id" already exists
  return new PostgresError(errorFields);
         ^
    at parseError (error.ts:106:10)
    at Connection._processError (connection.ts:434:19)
    at Connection._simpleQuery (connection.ts:340:22)
    at async Connection.query (connection.ts:546:16)
    at async Client.query (client.ts:25:12)
    at async PostgresConnector.query (postgres-connector.ts:60:22)
    at async Database.query (database.ts:161:21)
    at async Function.createTable (model.ts:117:5)
    at async Database.sync (database.ts:131:7)

As I'm self-hosting my Postgres database I'm also seeing the logs from my database where it says this:

[143] ERROR:  relation "id" already exists
[143] STATEMENT:  create table if not exists "boxes" ("id" integer not null, "name" text, "place_id" integer);
alter table "boxes" add constraint "id" primary key ("id");
alter table "boxes" add constraint "boxes_place_id_foreign" foreign key ("place_id") references "places" ("id") on delete CASCADE

Can anybody spot the error? I think from my side everything is fine with my code, so probably something wrong with denodb? Help is very much appreciated!

[bashovski]

The problem is caused by having both fields named id. Same happened to me today. You could rename it so it has wildcard/prefix equal to the model name or something relevant to the model.

@eveningkid Any update on this?

[bashovski]

@drewisdorner You can drop all databases and run the queries manually whilst removing db.sync() from code. As you have already been observing logs, it's going to be very simple to retrieve them.

Image as a reference confirming it's working normally if you manually run queries:

[drewisdorner]

The problem is caused by having both fields named id. Same happened to me today. You could rename it so it has wildcard/prefix equal to the model name or something relevant to the model.

@bashovski Sounds like a great idea, why didn't I come up with this. I'll try to implement this and see if it works. Thanks very much!

[DerNamenlose]

I experienced the same thing. Three tables (users, tasks and events), all with their respective id column. events and tasks reference the users table via a foreign key field. sync() fails with error: Uncaught PostgresError: relation "id" already exists. After renaming the id columns of each table to include the table name, everything works as expected. Anything I could try to further debug the issue?