Issue: Peewee+PSQL: create_tables for complex object relationships broken when DeferredRelations in play
Author: josefdlange
State: closed
Created: 2018-02-25 23:58:34

As in the docs, circular FK dependencies are a code smell, and I agree, but here we are. I've got a big object domain with some of these objects having circular foreign keys.

In `peewee` versions in the 2.8 line, `create_tables` seemed to be able to resolve any `DeferredRelation`s used for circular FK relationships and tell PSQL to create the tables appropriately. I see in the [docs](http://docs.peewee-orm.com/en/2.10.2/peewee/models.html#circular-foreign-key-dependencies) that there's a more manual approach documented, but I've never encountered need for it until upgrading from 2.8.x to 2.10.x.

Two questions:

1) Am I correct in finding that the behavior of automatically doing this was done by `peewee` prior, and is now no longer supported? (otherwise, what magic could it have been that made this work before?)
2) Do you have any recommended methods for enabling a more opinionated methodology to creating tables in this way, or automating the process as documented? My CI environment does a build/teardown of tables every run (sometimes every test) and I'm thinking that having a special codepath for each `DeferredRelation`'s setup is a little unclean.

I know it's a way uncommon use-case (and a noncurrent version on top of that), but I'm hoping you might have some top-of-the-head insight as to what in particular changed; the changelog between the versions don't really show anything that appears related.

As always, thanks for your continued work and support on this tool -- it's a wonderful piece of tech.

I'm not sure about the answer to 1, but I'm pretty sure that before the only approach that worked was to substitute the circular instance of the `ForeignKeyField` with an `IntegerField` (or whatever), e.g.:

```python

class User(Model):
    favorite_tweet_id = IntegerField()

class Tweet(Model):
    user = ForeignKeyField(User)
```

In the above instance no constraint is created for the "favorite_tweet_id" column.

The approach described in the docs for 2.10 allows you to declare the foreign-key *and* create the constraint:

```python

# Create a reference object to stand in for our as-yet-undefined Tweet model.
DeferredTweet = DeferredRelation()

class User(Model):
    username = CharField()
    # Tweet has not been defined yet so use the deferred reference.
    favorite_tweet = ForeignKeyField(DeferredTweet, null=True)

class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, related_name='tweets')

# Now that Tweet is defined, we can initialize the reference.
DeferredTweet.set_model(Tweet)

# Foreign key constraint from User -> Tweet will NOT be created because the
# Tweet table does not exist yet. `favorite_tweet` will just be a regular
# integer field:
User.create_table()

# Foreign key constraint from Tweet -> User will be created normally.
Tweet.create_table()

# Now that both tables exist, we can create the foreign key from User -> Tweet:
# NOTE: this will not work in SQLite!
db.create_foreign_key(User, User.favorite_tweet)
```

In 3.0 it has gotten somewhat easier:

```python

class User(Model):
    username = CharField()
    # Tweet has not been defined yet so use the deferred reference.
    favorite_tweet = DeferredForeignKey('Tweet', null=True)

class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, backref='tweets')

db.create_tables([User, Tweet])
User._schema.create_foreign_key(User.favorite_tweet)
```

I think I'll reopen this to add the "create_foreign_key()" API back.

See commit c0c5a0af9ee985bbcb1c4350d992925c1d5b7799 -- updated above comments to reflect changes.

Documentation for 3.x updated:

* http://docs.peewee-orm.com/en/latest/peewee/models.html#circular-foreign-key-dependencies
* http://docs.peewee-orm.com/en/latest/peewee/api.html#SchemaManager.create_foreign_key

My goodness you're fantastic. Thanks for the quick turnaround and thoughts. Any thoughts as to why it was just working before? 

I was using the `test_database` context manager / decorator from `playhouse` which in turn uses `create_model_tables`. It hadn't had any problems or complaints until I upgraded from `2.8.0` (woah, just realized how far behind I'd been; that's like 2 years!) just recently.

By "working before" do you mean that the constraints were all set-up in the database schema? I'm not sure that that's the case. I'm also not sure what specific problems/complaints you ran into after upgrading?

Sorry, to clarify:

2.8.0: using `test_database` decorator had no problem building the entire graph of tables (circular FK relationships included) from scratch in my test suite, for each test.

>2.8.0: some tables just fail to create because their depended-upon tables don't yet exist.

> On Feb 26, 2018, at 4:03 PM, Charles Leifer <notifications@github.com> wrote:
> 
> By "working before" do you mean that the constraints were all set-up in the database schema? I'm not sure that that's the case. I'm also not sure what specific problems/complaints you ran into after upgrading?
> 
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub <https://github.com/coleifer/peewee/issues/1516#issuecomment-368696079>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABA3s8ahuaZcJIQ7DsBbxAi1rm-DjhYwks5tY0ZEgaJpZM4SShgX>.
> 



You're positive that *both* foreign key constraints are being created? Hmm... and what version are you using now? It'd be great if you could show me the versions you're using and the exact issues you're experiencing with each.

I'll have to pull together an example for you without proprietary info. I'm not certain the actual FK constraint is being generated, frankly, but at least the tables get successfully created. I am certain that `2.8.0` it "works" on -- insofar that at least the tables don't fail to be created.

If you're okay with waiting a bit, I'll try to come up with a simplified example and find the version breakpoint.

Thanks, that'd be great. I'm willing to bet that the constraint isn't being created in the 2.8 example, so it appears to work, but if you wanted you would be able to break referential integrity.

You could test this with your existing code:

```python
class User(Model):
    favorite_tweet = DeferredForeignKey('Tweet')
    username = TextField()

class Tweet(Model):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()

db.create_tables([User, Tweet])
# If the constraint exists, the following will raise an error since the
# tweet with the corresponding ID (12345 in the example) wouldn't exist:
u = User.create(favorite_tweet=12345, username='foo')
```

Re-reading the issue, I'm a little unclear what exact issue you're encountering? Is there a specific error you're running into after upgrading? Is this error related to `create_tables()` or does it only happen when using some test setup?

For what it's worth, peewee 3.x removes `test_database`. There are a few alternatives described in #1440 and the peewee tests themselves.

Okay, after more monkeying around, I've narrowed it down to `2.10.x`, here's the difference of what occurs.

When using `create_model_tables` (in my case the usage of it embedded within `test_database`), I have determined that, given models `Foo` and `Bar` with a circular FK dependency:

* `< 2.10.x`: tables create okay, but as you suspect, `Foo`'s FK to `Bar` doesn't actually have the constraint (but at least the table gets created with the right type on the column for `bar_id`).

* `>= 2.10.x`: tables fail to create right away; creation of `Foo` complains that relation `bar` doesn't yet exist.

I'll poke around the 2.10.x changelog and commits to see what might have happened, but I'd guess we're zeroing in on some change that more strictly enforces the creation of the constraint straightaway.

Hmm... it looks like changes to the way deferred foreign-keys were resolved, although there are quite a few changes so I'm not positive.

At any rate, I don't think there's a whole lot I can offer, as Peewee is now at 3.x and any fixes would pertain to how the code functions in 3.x (which is somewhat different from 2.10). Sorry not to be of more help.

I appreciate your help with the investigation. I'm looking to get to 3.x eventually, but wanted to understand what was going wrong here before I opened that can of worms.