`npx directus schema-sync import` tries to insert data first

martin-braun commented 1 month ago

Describe the Bug

It does not create the schema first, thus failing, I had to import-schema first and foremost.

To Reproduce

npx directus schema-sync import after running npx directus bootstrap --skipAdminInit on an empty database.

Some additional mentions

I had to figure several things out painfully by trial and error (documentation?):

Directus won't load extensions until I bootstrap an empty database.
Directus was complaining about missing mv_ fields in my directus_settings despite successful bootstrapping. I don't know where those columns came from, but I figured those columns are part of my exported schema, so I would just go ahead and recover my schema using this extension.
directus_files should be part of the template (commented), because it's most likely needed when setting a project logo etc.

The bootstrapping with this extension basically is npx directus bootstrap --skipAdminInit, followed by npx directus schema-sync import-schema and finally npx directus schema-sync import.

Version

1.6.4

Installed Extension Via

NPM Custom

u12206050 commented 1 month ago

Did you run the npx directus schema-sync install after bootstrapping as per the documentation?

martin-braun commented 1 month ago

@u12206050 This went over my head. I thought the install step is only necessary when installing schema-sync for the first time, not when wanting to import the schema and data during a deployment. That also explains why Directus didn't want to start, because schema-sync install seems to install the mv_ columns which are necessary for the plugin to work.

This section really just sounds like a guide to setup the entire thing locally. The overall steps for bootstrapping are different when it comes to Directus. It was not even clear if npx directus bootstrap was necessary until I realized that it won't load any extensions until this step is done.

I kinda got away with it by importing the schema without the install step and in retrospect are very surprised that this has worked. My only guess is that you skip the mv_hash check, when the column is missing?

Anyways I think it would be good to lay out the steps that are necessary when deploying:

npx directus bootstrap --skipAdminInit (flag only if users were exported)
npx directus schema-sync install (as you mentioned)
npx directus schema-sync import --merge (to setup schema and data based on your local export, the flag is nice for future use)

I also followed your Tips about exporting and importing passwords. The hashes are identical both on local and my deployed instance, but I couldn't login. My .env was fairly similar. I even tested same APP_KEY and APP_SECRET, yet I was unable to login. Maybe you know why?

Anyways, I think it's much better to simply set new unique passwords, so I then directus users passwd --email xxx --password xxx to update my vanilla users and I was good to go.

Lastly, I would like to know if schema-sync will properly handle changes in the model and data when used with the --merge flag? How far can I go with this? I'm a bit confused, since there are also database migrations for Directus that I can opt-in. So I can write my own database migrations, but they are less convenient, whereas this extension allows to simply modify the model in the web UI and then simply re-export. I will definitely have a 2nd non-production instance of my backend to test this process in advance, but I would love to have your thoughts on this.

I will close this, because the issue is solved. Thanks so much for everything, looking forward for your response.

u12206050 commented 1 month ago

Glad you solved it.

Firstly the --merge flag is purely for merging data, it basically just skips the deleting step (which would remove all items that are not in the exported files)

As for the passwords, especially when you say the hashes are identical I really have no idea idea why it wouldn't work as it should. Different DB version? Different user id/email?

The schema-sync install is only required once per environment, I hope Directus will soon expose a onInstall option on the extension config so that we can automate it when the extension gets installed the first time.

martin-braun commented 1 month ago

Hi @u12206050,

thanks for the heads up.

Firstly the --merge flag is purely for merging data, it basically just skips the deleting step (which would remove all items that are not in the exported files)

I figured. Could you then please tell me what would happen if I remove columns and add new columns in my data model, do a new export and run the import-schema on a different environment. Will schema-sync be so smart to modify everything?

I see how it would fail to do so, if the new column can't be NULL, but there is already data in the table. If I need a new column that cannot be NULL, I would first create the column nullable, then update all data in such column to be not NULL and then I would change the column to not be nullable.

As for the passwords, especially when you say the hashes are identical I really have no idea idea why it wouldn't work as it should. Different DB version? Different user id/email?

These users were imported by schema-sync and their ID, email and password hash matched. I have no explanation for this, but since I only plan to deploy with pre-defined admin accounts, I don't bother with it, because passwords should be reset regardless.

Thanks a lot!

bcc-code / directus-schema-sync