The column "foo.bar_id" does not exist in the current database.

[maxh]

Bug description

We had an error reported in production during a recent migration when dropping an unused column with a foreign key constraint:

The column "foo.bar_id" does not exist in the current database.

I'm not aware of anything we could have done differently at the application level to prevent this, so I'm wondering if this is a bug or our team is missing a technique for avoiding these errors.

How to reproduce

1. Remove an unused field with a foreign constraint from an existing Prisma table.
2. Generate and run the migration, but don't update Prisma client or application code.
3. Hit an endpoint that queries the table.

(Not sure if this a 100% reliable repro -- we noticed this issue a few times in production.)

Expected behavior

I would expect this migration to be safe during deploy. It's possible I'm missing some functionality within Prisma that makes these migrations safe. Perhaps there is a feature like ignored_columns in Rails that we could use here?

https://api.rubyonrails.org/classes/ActiveRecord/ModelSchema/ClassMethods.html#method-i-ignored_columns

The pattern I'm used to from Rails is:

• Remove all application usages of column to drop
• Add column to ignored_columns
• Wait for deploy
• Migrate to remove column

With this approach, there is no downtime.

Prisma information

Something like:

model Foo {
  id @id
  bars Bar[]
}

model Bar {
  id @id
  fooId @string
  foo Foo @relation(fields: [fooId], references: [id])
}

I'm not sure if the foreign key constraint is important or a red herring here.

Environment & setup

• OS: Alpine Linux (in production)
• Database: PostgreSQL
• Node.js version: v16.13.0

Prisma Version

prisma                  : 3.15.2
@prisma/client          : 3.15.2
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 461d6a05159055555eb7dfb337c9fb271cbd4d7e
Studio                  : 0.462.0
Preview Features        : extendedIndexes, filterJson, interactiveTransactions

[janpio]

You removed a column, but did not update the Client or application code that uses the column, then get an error. Is that correct? What is the bug here?

The only way to prevent this currently would be to remove all uses of the column/field from your code first, update your schema to remove the column, then deploy the updated Client (that does not know about the column any more), and then migrate the database. This will only work if the removed column is not required for INSERT operations.

Is that what is possible via ignored_columns in Rails? If so, this sounds more like a feature request.

[maxh]

Thanks. It looks like @ignore already does what we need:

"Ignored fields are excluded from the generated Prisma Client"
https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#attributes

I manually tried this and confirmed the field doesn't appear in the generated client. Is that consistent with your understanding?

Perhaps all that's needed here is a page along the lines of "Safely contracting a schema" that suggests a two-step change of @ignore and then actually removing the field:

https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate

[janpio]

I am not sure if that achieves all the things you need or were hoping for. If so, it is mostly unintentional I guess - the feature was meant to be able to migrate specific columns that are not supported in Prisma Client.

[AndrewSouthpaw]

Yeah, I just bumped into this issue, and we're lucky we have a staging environment so we caught this before it hit production.

You removed a column, but did not update the Client or application code that uses the column, then get an error. Is that correct? What is the bug here?

The problem is that we migrate before deploying application code, which is correct, but that means there's some time between the schema change and the updated application code. There needs to be a way to safely remove the column from the schema before the app code gets updated.

With Rails, as long as you're not referencing a column, you can safely delete it from the schema, and it won't cause any issues. I didn't expect Prisma to detect the column was deleted and complain about it.

So while Prisma's behavior is fine, it doesn't align with what a lot of people might expect coming from other ORMs. I definitely agree with @maxh here about the value of adding to the guide.

[AndrewSouthpaw]

For future Googlers, there's currently a bug where create is lost when adding @ignore to a not-null field.

Until this bug is fixed, this is how I'm dropping a column with zero downtime, each is a separate release:

1. Mark the column optional
2. Mark the column @ignore and remove all uses of the column
3. Drop the column

[maxh]

My team wrote a safe schema change checker to prevent us from deleting models and fields that hadn't been previously been ignored:

https://github.com/loop-payments/prisma-safety

Might be useful to others who find this discussion!

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!