Migrating DB to new schemas does not work

[jamiehannaford]

trafficstars

My k8s pod recently pulled in v1.122.0 and now the DB is out of date. In the README it says to run yarn database:migrate but this is not working for me:

$ kubectl -n finances exec -it ghostfolio-7dbbbf65c-lww6f yarn database:migrate
kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.
yarn run v1.22.17
$ prisma migrate deploy
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "ghostfolio", schema "public" at "ghostfolio-postgres:5432"

28 migrations found in prisma/migrations

Error: P3005

The database schema for `ghostfolio-postgres:5432` is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
command terminated with exit code 1

Am I doing something wrong?

[dtslvr]

I have asked a similar question here: https://github.com/prisma/prisma/discussions/10905

[casitu]

Same problem here when migrating to latest release published!

[AuthorShin]

@dtslvr Same problem, no fix?

[DocBrown101]

Same problem with version 1.174.0 Apply data migration (yarn database:migrate)

Always leads to the same error:

Error: P3005 The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

@dtslvr is there a workaround to use the latest version?

[dtslvr]

Could you please follow the Baseline your production environment guide or ask directly in the prisma community?

A (unsustainable) workaround would be to run the SQL commands of the (missing) database migrations manually.

[DocBrown101]

When I try to baseline the production database, I always get this error here: unknown or unexpected option: --applied

I have used this command for it: docker-compose -f docker-compose.yml exec app yarn database:migrate resolve --applied 20210604190809_initial_migration

Source: https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/baselining#baselining-a-database

It would be very helpful if you could create a small upgrade tutorial. It seems I am not the only one with this problem.

[ramesh-lingappan]

Facing similar issue, unable to use the application or upgrade the database,

[Nest] 1  - 07/31/2022, 2:10:10 PM   ERROR [ExceptionsHandler] 
Invalid `this.prismaService.authDevice.findUnique()` invocation in
/ghostfolio/apps/api/main.js:1:299761
→ 1 (()=>
  The column `Order.comment` does not exist in the current database.
Error: 
Invalid `this.prismaService.authDevice.findUnique()` invocation in
/ghostfolio/apps/api/main.js:1:299761
→ 1 (()=>
  The column `Order.comment` does not exist in the current database.
    at RequestHandler.request (/ghostfolio/apps/api/node_modules/@prisma/client/runtime/index.js:49022:15)

would appreciate on some guide to resolve this issue.

[dtslvr]

It would be very helpful if you could create a small upgrade tutorial. It seems I am not the only one with this problem.

This is what I do: I have the repository locally (git clone https://github.com/ghostfolio/ghostfolio.git) with installed dependencies (yarn install) and an adapted .env file. Before deploying the new docker image, I run the following command to migrate the database schema: yarn database:migrate. That's it. I don't understand what the difference could be.

By the way, this is how the table _prisma_migrations looks like:

[DocBrown101]

The difference is probably that your database is empty and can therefore be easily updated with "yarn database:migrate".

That's why we always get this error message: Error: P3005 The database schema IS NOT EMPTY

A database that contains records can NOT be migrated this way, so we need a migration guide.

[dtslvr]

The difference is probably that your database is empty and can therefore be easily updated with "yarn database:migrate".

No, I'm referring to the migration of the Ghostfol.io production database (SaaS).

[DocBrown101]

Then it can only be the type of migration itself. We don't have a development environment and have to do it ourselves in the docker image.

Probably one or more parameters are still missing in this command: docker-compose -f docker-compose.yml exec app yarn database:migrate

[fabricemrchl]

Hello, I made tests and I think the issue is database initialisation, not upgrade.

Base on prisma documentation when production database is created we need to "baseline" it. Basically we need to inform prisma which migration scripts are already applied.

I install a fresh ghostfolio v1.170.0 and I applied manually all 42 existing migration scripts with command docker-compose -f docker-compose.yml exec app yarn database:migrate resolve --applied after that I upgraded to v1.174.0 and run yarn database:migrate prisma successfully applied 2 new migrations scripts.

I think the best is to run a script during yarn database:setup, so with a fresh install prisma will create database and our script will mark all existing migration scripts as applied.

Please find bellow a quick and dirty script that I used during my test prisma_applied.txt

[dtslvr]

Hey @Fabrice M

Cool, thanks for your investigations. It looks promising. Is it possible to put your script to ./prisma/baseline.sh and then run it somehow in database:setup(together with yarn database:push && yarn database:seed)?

Edit: What happens if database:setup would be changed to yarn database:push && yarn database:migrate && yarn database:seed, would it apply all migrations?

[fabricemrchl]

yarn database:push && yarn database:migrate && yarn database:seed this one failed, same error database:migrate can't be applied because prisma don't know about baseline.

Here the command line that succeed for me yarn database:push && yarn database:seed && sh ./prisma/baseline.sh , database is initialized then my script baseline it. I changed it in package [baseline.sh.txt](https://github.com/ghostfolio/ghostfolio/files/9245207/baseline.sh.txt) .json then I run yarn database:setup no more error with yarn database:migrate.

Please find in attachement new script adapted (use sh instead of bash and command line changed to work inside container). Deploy script baseline.sh in ./prisma/ folder then edit package.json like bellow : "database:setup": "yarn database:push && yarn database:seed && sh ./prisma/baseline.sh",

Sorry I should create a pull request but i need to setup a git env before I can't do it right now. Also sed command to get migration name is not optimize in my script, it works but I think there some best way to do it.

baseline.sh.txt

[dtslvr]

Sorry I should create a pull request but i need to setup a git env before I can't do it right now.

Maybe https://github.dev/ghostfolio/ghostfolio is useful, if you are familiar with Visual Studio Code 🙂

Also sed command to get migration name is not optimize in my script, it works but I think there some best way to do it.

It doesn't have to be optimized in the first place, done is better than perfect.

[fabricemrchl]

Thanks about github.dev, I created a branch and before crate a pull request I tested it, but I found another bug then I raise an issue #1123

[dtslvr]

From Ghostfolio 1.177.0, database baselining is now part of the database:setup script. Thank you @fabricemrchl for your contribution!

If you have started with an earlier version of Ghostfolio, you need to baseline your database once:

docker-compose --env-file ./.env -f docker/docker-compose.yml exec ghostfolio yarn database:baseline

[DocBrown101]

The baseline of the database has worked, but the application does not run and returns the error: [Nest] 1 - 08/04/2022, 9:06:32 PM ERROR [ExceptionsHandler] Invalidthis.prismaService.authDevice.findUnique()invocation in /ghostfolio/apps/api/main.js:1:299865 → 1 (()=> The columnOrder.commentdoes not exist in the current database.

I have already tried with database:migrate, but then I only get displayed that there are no more migrations. No pending migrations to apply.

[fabricemrchl]

Database schema is the same from version 1.174.0 so if you upgrade from 1.174.0 it's OK to run database:baseline .

If you upgrade from an older version than 1.174.0 and run database:baseline you will mark some database update as applied and so prisma will never apply it.

Migration process : From 1.174.0 and newer, update container to 1.177.0 and run database:baseline only once Earlier than 1.174.0 :

• option 1 : update database manually to reach 1.174.0 then, update to 1.177.0 and run database:baseline only once
• option 2 : export data from user interface, start from scratch on 1.177.0, run database:setup and import data

[DerAndereJohannes]

oof, Thanks for the work and guide! I was attempting to upgrade from 1.160 with the same issue (going from 1.160 to 1.171 db migration). Looks like I will probably just go for option 2! :-)

[dtslvr]

There is a new release available including a schema migration to check if you can successfully migrate after the baseline.

[fabricemrchl]

databse:migrate works fine on my instance, upgraded from 1.176.2 to 1.178.0

Logs :

47 migrations found in prisma/migrations

Applying migration 20220808181846_added_url_to_symbol_profile_overrides Applying migration 20220808191912_added_default_value_for_countries_of_symbol_profile_overrides Applying migration 20220808192002_added_default_value_for_sectors_of_symbol_profile_overrides

The following migrations have been applied:

migrations/ └─ 20220808181846_added_url_to_symbol_profile_overrides/ └─ migration.sql └─ 20220808191912_added_default_value_for_countries_of_symbol_profile_overrides/ └─ migration.sql └─ 20220808192002_added_default_value_for_sectors_of_symbol_profile_overrides/ └─ migration.sql

All migrations have been successfully applied.

[dtslvr]

database:migrate works fine on my instance, upgraded from 1.176.2 to 1.178.0

Thanks for your feedback @fabricemrchl!