Typeorm: Option to disable foreign keys creation

[alfaproject]

Issue type:

[x] question
[ ] bug report
[x] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Is there a way to disable foreign keys creation? I searched a bit and couldn't find it.

If not, is there a chance such option can be implemented? Maybe I can give it a try if you point me in the right direction. (:

[Kononnable]

Adding @pleerock
I'm not a fan of having relational database without foreign keys, but I'm concerned if introducing such change could break some of the functionalities like onUpdate, onDelete on relations.

[pleerock]

Yeah it can bring some issues. I would like to know what is the point of this feature request.

[alfaproject]

Our DBA told us to remove all foreign keys for performance reasons and to ease sharding, clustering and online migrations. It's actually the second company I work for where this was requested, but it's my first time using TypeORM, so I had to fork and get rid of the line that creates the foreign keys.

I actually don't see the point of enforcing them. It seems to me like it's up to the application to maintain consistency its own way or to use foreign keys where appropriate if desired, right? (The option is there if anyone wants to use them.)

[pleerock]

Think we can try to do it, but I'm not sure about all consequences

[alfaproject]

That would be awesome. Maybe I can help if I find some free time. A bit over-worked right now ):

[Diluka]

Oh, this is what we discussed in the beginning #1031

[vlapo]

As @rudism requested in https://github.com/typeorm/typeorm/issues/3382. It would be nice to have option to disable FK creation only for specific relation too.

[exeleon]

This feature would let to have "soft" relations across databases in different hosts. That is really my requirement now.

[ITJesse]

Maybe we should give user an option to choose whether to use a foreign key or not.

[david-eos]

Any news on this? Is it possible to disable it?

[brianschardt]

any news on this??

[eddieajau]

I have had a similar problem. The issue I had is with my unit testing where I use synchronise. My production code would be fine, but TypeORM wants to put the FK on the wrong table. In the example below I put the relation on the stats table, not the channel table.

In case it helps anyone else, I've just used leftJoinAndMapOne to work around this problem.

@Entity('channel')
export class Channel {
  @PrimaryGeneratedColumn()
  public id: number;

  // ...
  public stats?: ChannelStats;
}

@Entity('channel_stats')
export class ChannelStats {
  @PrimaryColumn()
  public channel_id: number;

  @Column({ unsigned, default: 0 })
  public members: number;
}

// Then something like
const users = await connection
    .getRepository(Channel)
    .createQueryBuilder("c")
    .leftJoinAndMapOne('c.stats', 'channel_stats', 's', 's.channel_id = c.id')
    .getMany();

[frankra]

@alfaproject can you please share the changes you did? I am having some super weird issues atm and I think they might be related to the auto-creation of FKs. Thanks!

[alfaproject]

Basically created a patch to comment out the method that creates the relations.

I've stopped using the library, so can't remember the exact file, so you have to search for it.

[chengjianhua]

@alfaproject Would you like to share your alternative to typeorm?

[alfaproject]

We stopped using relational databases, lol

[JohnnyDevNull]

any news on this? I have a scenario in which i want to setup a relation, but on a leagacy database where no foraign key's can be created without a more bigger migration...

[jituanlin]

config with synchronize: false will global disable auto synchronize.

[insanehong]

Our DBA told us to remove all foreign keys for performance reasons too.
I need option can be disable FK creation

[bundery]

Any news on this? It is very useful.

[v1d3rm3]

It could be useful in tests cases too.

[NDT36]

Any news on this?

[ceo-epiclions]

Really want this

[alumni]

Me too, or at least to provide [NOT] ENFORCED and [NOT] VALIDATED options for the foreign key (they are slightly different in each SQL dialect).

[eonae]

Really need this functinality too.

[alexkazantsev]

@pleerock
In order not to create a new issue, I want to ask as part of this.

We use TypeORM together with PostgreSQL and SQLite, and to implement synchronization between them, we decided to get rid of foreign keys. But, as I understand it on this topic, it is currently impossible to remove the ability to create foreign keys. So the question is:

Could difficulties arise when using TypeORM if you simply disable foreign key checking? Does TypeORM check for foreign keys and their validity, or is all validation performed on the database side?

[alumni]

Could difficulties arise when using TypeORM if you simply disable foreign key checking? Does TypeORM check for foreign keys and their validity, or is all validation performed on the database side?

Foreign Key checking/validation is an SQL feature and is performed by the database. Basically you can still have foreign keys in the database, but you can instruct it not to check for the correctness of the data. (Disabling FK validation is not currently supported by typeorm - it can't set the do not check attribute of the foreign key).

If you want to completely remove the foreign keys, at the moment you will have to remove the relations from the entities, since typeorm will always create FKs. If you want to have relations without FKs, you would have to create a wrapper (although you won't benefit from some optimizations like fetching related entities).

Alternatively, you could maintain the db yourself separately either with non validated foreign keys or without foreign keys.

Or let's hope we get some new developments on this topic :) I would be willing to submit PR to allow the user to disable FK validation, but I tried once to submit a PR and I didn't get a response...

[GaryChangCN]

anything new?

[Ives7]

anything new?

[guoguo0202]

anything new?

[ekifox]

anything new?

[tienne]

anything new?

[imnotjames]

Or let's hope we get some new developments on this topic :) I would be willing to submit PR to allow the user to disable FK validation, but I tried once to submit a PR and I didn't get a response...

The PR is still up? Happy to review it.

[cantremember]

we are sort of "constrained" in our schema as well by the enforcement of foreign key constraints. here's another (recent) Issue asking for a way to tell TypeORM not to auto-generate one

https://github.com/typeorm/typeorm/issues/5977

[alpharder]

I've added the #7112 PR but I don't have enough time to write the tests ATM, can someone assist me in that?