Ignore tables without a primary key

[yangyuqian]

Will sqlboiler allow ignoring tables without a primary key?

Asking this because I'm working with a large legacy system with hundreds of tables and complicated foreign key settings.

So it's difficult for me to provide a correct whitelist, and I have to generate the orm agaist the whole schema.

I guess a "--ignore-tables-without-primary-keys" may help this kind of usage.

[nullbio]

Hi. What database are you using? I'd wager there would be query you could do to return that list of tables to feed into the white list or black list. Anyway, to answer your question, this not something we currently support through a flag like that. Our reasoning for the error is that we rely on the existence of primary keys to generate your relationships properly and in reality every table should have a primary key and a failed generation is a good reminder that the database schema needs to be adjusted. I realize this is a bit of a nuisance in your case since you're dealing with a legacy system. @aarondl what are your thoughts?

[aarondl]

So my thoughts are somewhat along the same lines. I wonder what the real use case is. SQLBoiler has SIGNIFICANTLY reduced functionality when you don't have a primary key, and as such we don't even generate structs for them.

To workaround this issue you can:

1. Blacklist all your tables programatically (what database are you using?)
2. Add primary keys to your tables with a script

We could probably make this work by changing a lot of the templates to ignore tables without a primary key. It would probably only be able to generate a struct, since even insert/update/delete/find is all ID based. It could also do select I suppose.

Can you tell us more about this use case? And why you can't change the database?

[yangyuqian]

Hi, I'm working on a large legacy system based on MySQL(old version too...).

There are many foreign keys across hundreds of tables, and some of them are missing primary keys and violating regular thinkings on the schema design.

i.e. below example shows a common use case in this legacy system.

CREATE TABLE user {
  id int
  name varchar
}

CREATE TABLE user_role {
  user_id int
  role_id int
}

CREATE TABLE role {
  id int
  name varchar
}

And yes, sometimes, blacklist/whitelist are helpful to this kind cases, but it could be a blocker when generating models against tables(a whitelist created on foreign keys constraints) without primary keys.

[aarondl]

We've decided we're not going to support tables without primary keys still. It really ruins any advantage you're getting from using sqlboiler. For dealing with legacy databases I suggest things like:
https://github.com/jmoiron/sqlx/
And maybe a struct generator (there are a few out there that exist).

I'd also consider a pull request if it were clean enough. Or if enough people ask for this I may reconsider.

[gitstercode]

Hi, I have a DB with Read-only access.

There are 20 to 30 tables in it and I need data from 5 tables which have primary keys in it.

I like to use sqlboiler and I am trying to generate models and getting no primary key error.

Is there any way that I can handle this ?

[aarondl]

It would probably work if you whitelisted the tables you needed.

[emwalker]

My use case: I would like to create a view that will not be used by golang code. Right now sqlboiler is complaining that the view is missing a primary key; I would like to tell sqlboiler to not worry about the view.

Seems like having sqlboiler know to skip this view would be a reasonable thing.

[aarondl]

Yep. Blacklisting or Whitelisting tables should work?

[emwalker]

Either should do the trick in my case. (I see now that there is table whitelisting and blacklisting.)