Handle tables with no primary keys

[Sytten]

It it quite common to have tables with no primary key, here are a few use cases:

• Time series data (specially with TimescaleDB)
• One-to-many where individual elements are never references

It would be nice it was supported by sea-orm. At first I would be totally fine by just rendering the Column so we can use it with sea-query and later down the line do a better integration. Currently it is a bit annoying because we cannot ignore it in the CLI so effectively either you remove the entity after each run or you add a primary key.

[billy1624]

Currently it is a bit annoying because we cannot ignore it in the CLI so effectively either you remove the entity after each run or you add a primary key.

Hey @Sytten, do you mean generating entities of table without primary key will cause errors?

[Sytten]

Yeah currently if you generate entities for tables without a PK it won't compile

[tyt2y3]

That might be a bit tricky. I always have an assumption that for a table without primary key, the DB engine implicitly adds one and hides it from you anyway. A table without primary key breaks many assumptions about the concept of "Entity", and hence methods like find_by_id is not possible. If we need to support it within SeaORM, we might have to invent a new concept, Datum I guess? (just some random name popping up in my mind)

[Sytten]

You are partially right. Databases in fact have a row ID no matter if you have a primary key or not.

Yeah it is probably hard to support in an ORM. I think my wish would mostly be to be able to still generate some entities for it (at least the columns and models) so they are usually with sea-query and dont break the build.

This would fit with my other issue on providing something in between the raw query and the orm.

[billy1624]

Hey @Sytten, you can select the Datum like below.

#[derive(Debug, Clone, FromQueryResult)]
struct JoinedResult {
    ...
}

let builder = self.db.get_database_backend();

let mut stmt = sea_query::Query::select();

stmt.columns([stuff::Column::Name, stuff::Column::CreatedAt])
    .column(status::Column::Status)
    .from(stuff::Entity)
    .join(JoinType::InnerJoin, status::Entity, Cond::any());

JoinedResult::find_by_statement(builder.build(&stmt))
    .all(&self.db)
    .await

Adapted from a Discord message by jochen#5064

[Sytten]

It would still fail to build when compiling the codegen code directly without any manual modification. Maybe we could add settings in the codegen CLI to either ignore the tables or treat them differently (like only build the columns enum).

[billy1624]

Good idea! We could simply generate a Model struct and Column enum.

[alper]

That might be a bit tricky. I always have an assumption that for a table without primary key, the DB engine implicitly adds one and hides it from you anyway.

In my case it was very easy to create a Sqlite table without a pk using sqlite-utils and then it would still have a ROWID. It's also easily fixed, but there's nothing in sqlite that requires you to explicitly have a primary key.

[billy1624]

I'm trying to produce a clearer error message at compile time.

• https://github.com/SeaQL/sea-orm/pull/1020

[joshpostel]

It would still fail to build when compiling the codegen code directly without any manual modification. Maybe we could add settings in the codegen CLI to either ignore the tables or treat them differently (like only build the columns enum).

+1 for this solution, as I ran into the same issue today.

Should I open a new issue to track the cli changes or can this be reopened? It is unclear to me why this was closed by #1020.

[databasedav]

what was the solution here? i would like to avoid creating another index if possible, is it possible to tell the entity that it should treat some other index as a "primary" key?

adding this to the generated entity struct makes it stop complaining, but i'm not sure of its implications, and of course as mentioned above this would need to be manually done after every post migration entity regeneration

#[sea_orm(primary_key, auto_increment = false, column_type = "Boolean")]
_fake_primary_key: bool,

[billy1624]

Hey @databasedav, every table is assumed to have a primary key. For table without a primary key, I would suggest doing one of the following:

• Adding a autoincrement primary key column (if it's possible to alter the schema)
• Or, let all column in the table be a composite primary key, i.e.

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "cake_filling")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub cake_id: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub filling_id: i32,
}

[vablings]

Hey @databasedav, every table is assumed to have a primary key. For table without a primary key, I would suggest doing one of the following:

• Adding a autoincrement primary key column (if it's possible to alter the schema)
• Or, let all column in the table be a composite primary key, i.e.

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "cake_filling")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub cake_id: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub filling_id: i32,
}

Although this works, what about for users who have hundreds of Models. Is there scope to add this into sea-orm-cli

[tyt2y3]

let all column in the table be a composite primary key

@vablings thank you for the suggestion! actually it seemed to be a very smart way, and the only drawback probably is the max. number of column is 12

[gitmalong]

I am trying to make use of https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto.md#simple-time-based-1-partition-per-day which wants me to create a parent table without having a primary key.

But in that case I am getting a lot of

 the trait `sea_orm::IdenStatic` is not implemented for `entities::transfer::PrimaryKey`

errors even for other entities that have primary keys.

The above mentioned workarounds do not help me.