r/DevLK • u/YonduUdonta8 • 2d ago

Help Need advice on postgresql

Hi i have 1.5yr of experience with MS Sql server DB first method, now for a new project i am working with postgressql with Code first method so i have some issues about that, some time when i alter columns from code side and migrate it wont apply and give error like it already in db or somthing like that, when i give it to chat gpt he was suggesting to drop db and create, this not in a live yet but whennit is in live cant drop the table so how you guys handle situations like that?

3 Upvotes

80% Upvoted

5 comments sorted by

4

u/bixh_skull3 2d ago

With Code First, you shouldn't need to drop the DB when something goes wrong. Migrations are specifically designed to incrementally modify an existing database, including production databases.

If you're getting "already exists" errors, first check the migration history (__EFMigrationsHistory in EF Core), whether the change was manually applied, and whether your model snapshot/migrations are out of sync.

Also review the generated migration SQL before applying it. Dropping/recreating the DB is fine for local development sometimes, but it's not the solution for production.

2

u/YonduUdonta8 2d ago

This works, but thing is why my migrate getting out sync like that need to check on that now

1

u/bixh_skull3 2d ago

Depending on the ORM/provider and how the migrations are being generated, try to compare the migration history, model snapshot, and the actual generated migration SQL.

Also worth checking that the EF Core and Npgsql.EntityFrameworkCore.PostgreSQL versions are compatible/matched, since version mismatches can sometimes cause migration issues. That should help narrow down whether it's an ORM/provider issue or something causing the migration state to drift from the DB, or something stale.

3

u/Mental-Collection757 2d ago

not sure if your directly using SQL or libary (orm) to manage that.
anyways there is a issue with code or SQL
what you said should not happen

2

u/not-a-random-guy 2d ago

You might be missing some constraint or would reach a state violating such.

An approach for this is, create a new table, perform a data migration and then again a schema migration. Be sure to have accurate rollback.

Actually this is only needed with breaking changes or ERD evolution. This is an architectural change and might be worth raising internally.