Database seeds problems with Postgresql reference fields

[Emiel]

From a remote Docker target I created database seeds as initial content and schema on a new target.

1. First problem I came across is that it’s not possible to select all tables and create a single seed containing the full schema and all data, so seeds have to be created table by table.

2. Secondly, the import doesn’t work on my other target and also not on my local development target (Docker), because of reference field dependencies.

3. Therefore I tried to change the On Update and On Delete properties to Cascade (also tried other options), but also applying this change returns an error. Please see the knex file and error message below:
   

   

How can I make this work?? Thanks for any help.

[Emiel]

Can anyone help me with this issue please? @Teodor? @George? @patrick?
Thanks in advance!

[George]

Well this error simply means that you can’t create a reference to a table that doesn’t exists yet.

You should always first create your tables in one “change” and then later on add the relations reference in a different change set.

How did you create it now and added the reference? All at once?

[Emiel]

@George

Well this error simply means that you can’t create a reference to a table that doesn’t exists yet.

Okay, but these tables do exist already, that’s the weird thing about it.

@George

You should always first create your tables in one “change” and then later on add the relations reference in a different change set.

How can I do this when the seeds I want to use from the remote target already contain these reference fields?

@George

How did you create it now and added the reference? All at once?

The database was created on a local development target long time ago and then used as initial schema and data on the remote target.
The tables are created one by one and reference fields were added later on, after saving the new table first. However, the changed have been reset in the meantime and removed as well. So how can I copy the remote target’s schema and data to the local target now?

Thanks for your help in advance again!

[Emiel]

I am still stuck with these problems and it appears they are not all reference field based.

I do understand that reference fields can only be imported/migrated/created if the reference table exists and took that into account, so that’s not the issue. The issues I do still have are:

1. Can’t edit reference fields’ On Update and On Delete values
2. Tables/fields can’t be removed
3. Imports of seeds on existing or of new tables doesn’t work.

All of the above actions I tried in 1 single change but still the errors are shown.

So again I am asking for help on this since I tried everything within my knowledge to find a solution already.

[Emiel]

Exploring the database schema with 3rd party software shows that the Delete Rule and Update Rule differ from the rules shown by Wappler. Could that be causing issues?
I tried changing these rules from the 3rd party software by the way to see if that helps, but that’s not possible unfortunately.

[Emiel]

Still having trouble with this, please see these migration files and the thrown errors:
Apply admin_seed:

Apply cms_seed:

Seeds were created from remote target and trying to import them to local target.
No reference fields involved, super simple cms table contains 3 string columns only.

Why is this not working??

[George]

I think this is more a problem with your database schema.

Was it all created in Wappler database manager or with an external tool?

And how are your keys named?

Maybe you can list the exact create sql for the both tables involved.

[Emiel]

All created in Wappler, here’s the schema of the 2 tables:

[Emiel]

Weird thing is that the admin_seed seems to be applied when I click on cms_seed-> Apply this seed.
(See 2nd screenshot above)

[George]

not sure how this public. got in front all over the place. Maybe try refreshing the scheme.
Also you can remove it from the seeds node.

Also in the admin seed you are inserting empty record? not sure if that is the point.

[Emiel]

@George

not sure how this public. got in front all over the place. Maybe try refreshing the scheme.

I don’t know either, but even after refreshing the scheme it’s still there.

Removing the “public” part from the cms seed still throws the error about the admin_seed.js:

@George

Also in the admin seed you are inserting empty record? not sure if that is the point.

That’s right, I only included it here in the post to show it’s contents because the error on the cms_seed has admin_seed details in it.

[George]

well this error just says that your public.admin does not exists - did you created it first?

[George]

You should always first apply the database changes - so all tables and relations are created/updated.

And then you should apply the seeds - but also make sure the reference id’s are populated of course

[Emiel]

Yes it exists indeed on both targets (target where the seed was created and target where the seed is applied).

[George]

well seems it doesn’t otherwise you won’t be getting this error.

Try to explore you exact database structure and reference keys with pgAdmin - to see where is the difference and how are the keys named.

[George]

With pgAdmin you can connect to your live database and explore the exact native structure. It is a very handy tool. So check the definition of you table like:

[Emiel]

Hi George, thanks.

I did this already with a tool called DBeaver.
The schema and all tables are exactly identical, so that’s also not the issue.

I ended up rebuilding the database schema on a new local target, because so much seemed to be wrong with it. The fact that a simple update on On Delete and On Update properties of reference fields was not even possible anymore underlines that, don’t you think?

Thanks for your help anyways.

BTW: It’s necessary to refresh tables every time changes have been applied. Maybe changes tables or the complete schema could be refreshed automatically after changes are applied? Just a suggestion.

[Emiel]

I did a full rebuild from within Wappler, which solved the issues.

However, still some properties are not read well by Wappler’s database manager. On Delete and OnUpdate properties were all set, but still displayed as set to No Action, which is not the case for sure (also checked with DBeaver).

And still if any changes are made, I have to refresh the schema or table manually to see the applied changes and be able to apply new changes.

[George]

Please file separate bug topics for the above,