Cannot get anything with primaryKey to work with Postgres (error: Uncaught (in promise) PostgresError: multiple primary keys for table "flights" are not allowed)

[ghost]

Imagine a scenario when the table is not created in the database yet. Running this code 2 times in a row should be fine, but in reality it throws an error which does not make any sense.

Code

import {
  Database,
  DataTypes,
  Model,
  PostgresConnector,
} from "https://deno.land/x/denodb/mod.ts";

const connection = new PostgresConnector({
  host: "censored",
  username: "censored",
  password: "censored",
  database: "censored",
});

const db = new Database(connection);

class Flight extends Model {
  static table = "flights";
  static timestamps = true;

  static fields = {
    ida: { primaryKey: true, type: DataTypes.BIG_INTEGER },
    departure: DataTypes.STRING,
    destination: DataTypes.STRING,
  };
}

db.link([Flight]);

await db.sync();

await Flight.create(
  { ida: 12345678, departure: "Paris", destination: "Tokyo" },
).catch(async () => {
  await Flight.create(
    { ida: 123456789, departure: "Prague", destination: "Japan" },
  );
});

Error in client

Error in database

2021-01-26 08:54:46.714 UTC [251] ERROR: multiple primary keys for table "flights" are not allowed

2021-01-26 08:54:46.714 UTC [251] STATEMENT: create table if not exists "flights" ("ida" bigint not null, "departure" varchar(255), "destination" varchar(255), "created_at" timestamptz not null default CURRENT_TIMESTAMP, "updated_at" timestamptz not null default CURRENT_TIMESTAMP);

alter table "flights" add constraint "ida" primary key ("ida")

[autsada]

I ran into this issue as well, after took some time to dig into the code I found the problem occurred because of db.sync(). When "drop" has not been set to "true" the table is re-created without dropping the old one. But dropping the table every time an app run is not practical as well.

So I solve this issue by manually comment the db.sync() out after the table has been created.

This is the code that causes an issue I think.

async sync(options: SyncOptions = {}) {
    if (options.drop) {
      for (const model of this._models) {
        await model.drop();
      }
    }

    for (const model of this._models) {
      await model.createTable();
    }
  }

I think it would be great if "SyncOptions" has something like re-created: boolean to check before attempt to create a table.

[envis10n]

Using db.sync() is NOT required to use the database. It's only required for creating the model schemas on the database (first run, etc).