Comments (8)
One possible solution is to tag each migration to the corresponding Prisma schema. Then a newly generated migration would diff the changes in the Prisma schema, as opposed to diff against what is in the current database. This, I believe, makes a bit more sense and is in my opinion a better system overall, but I understand it is likely time consuming to implement.
Yes, but that would be pretty much a completely different migration tool than what Prisma Migrate currently is.
Still, we are aware of the current challenges and are considering far reaching changes like that - but that will definitely take some time.
As for your solution, how can I add the column/index to the Prisma schema? Since
tsvector
and related functions aren't supported.
I was thinking about adding it as a supported field, and then manipulate the migration that would create it. But you are correct, in the details this might not actually work as hoped :/
from prisma.
@ApocalypseCalculator, you might be interested in #24248, in which I suggest we make diffs based on changes to the Prisma schema in different git versions. You might be able to use this for your use case.
from prisma.
What exactly is the destructive migration that is being generated here?
Fundamentally the problem is that Prisma does not support what you are trying to use in Prisma Schema (a generated column I think), and hence will of course try to clean up your database to bring it into the state that the Prisma schema represents. But that is also why --create-only
exists so you can review and clean up any generated migration.
from prisma.
The "destructive actions" generated by Prisma that I mentioned are
-- DropIndex
DROP INDEX "idx_search";
-- AlterTable
ALTER TABLE "Video" DROP COLUMN "search";
I realize that --create-only
exists, but I think the expectation is that I shouldn't need to go through every generated migration afterwards manually. If I need to add more custom features, or edit my Prisma schema further, it's not hard to see how it can become extremely messy. I had assumed (perhaps incorrectly) that it was a one and done sort of thing.
from prisma.
We would love to be able to offer that, but how should Prisma know what is expected in the database (because of a manually modified migration) and what is not (and hence needs to be fixed to get to the desired state)?
If I was you, I would probably add the search
column and the Gin index on it to the Prisma schema, then you just need to see how Prisma deals with the generated column definition (it will possibly try to remove it, which then is something you need to modify with each future migration).
from prisma.
One possible solution is to tag each migration to the corresponding Prisma schema. Then a newly generated migration would diff the changes in the Prisma schema, as opposed to diff against what is in the current database. This, I believe, makes a bit more sense and is in my opinion a better system overall, but I understand it is likely time consuming to implement.
As for your solution, how can I add the column/index to the Prisma schema? Since tsvector
and related functions aren't supported.
from prisma.
I'm trying to do something similar, but using the Unsupported()
column type.
But it seems to be hitting a bug in Prisma's "shadow database" parser?
I add the following:
textSearch Unsupported("GENERATED ALWAYS AS (to_tsvector('english', text)) STORED")
//...
@@index([textSearch], type: Gin)
Which seems entirely reasonable, yes? And it generates:
/*
Warnings:
- Added the required column `textSearch` to the `TextRevision` table without a default value. This is not possible if the table is not empty.
*/
-- AlterTable
ALTER TABLE "TextRevision" ADD COLUMN "textSearch" GENERATED ALWAYS AS (to_tsvector('english', text)) STORED NOT NULL;
-- CreateIndex
CREATE INDEX "TextRevision_textSearch_idx" ON "TextRevision" USING GIN ("textSearch");
This looks perfect!
But then before ever calling PostgreSQL (I checked the logs), it gets:
Database error:
ERROR: syntax error at or near "ALWAYS"
Position:
3
4 - Added the required column `textSearch` to the `TextRevision` table without a default value. This is not possible if the table is not empty.
5
6 */
7 -- AlterTable
8 ALTER TABLE "TextRevision" ADD COLUMN "textSearch" GENERATED ALWAYS AS (to_tsvector('english', name text)) STORED NOT NULL;
DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42601), message: "syntax error at or near \"ALWAYS\"", detail: None, hint: None, position: Some(Original(244)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("scan.l"), line: Some(1241), routine: Some("scanner_yyerror") }
Obviously the alternative approach of adding the migration manually doesn't work either, since Prisma will see it and kill it, as demonstrated by the bug report above. :|
This is a pretty serious bug/shortcoming/missing feature in Prisma. It's not working as expected, and this is a mission-critical feature for the app I'm using. More than half the reason I use Prisma is for the migrate feature, which could absolutely work even without being able to parse every field simply by skipping to the end of that column definition once it recognizes that it's an Unsupported()
column.
If you need to know a type name, give Unsupported()
another parameter so we can call it like:
Unsupported("ts_vector", "GENERATED AS ...")
Then internally it can treat the column as a ts_vector column, and maybe the full text search would even be able to work on it without us needing to create custom queries? I can dream...
from prisma.
@TimMensch The error message you are getting is straight from your database, not from Prisma. The generated SQL does not seem to be valid. As what you describe is also not about Prisma removing the results of custom migrations in the next mgirate dev
run, I suggest you open a new discussion so we can dig into this. Thank you.
from prisma.
Related Issues (20)
- `wasm-edge-light-loader.js` importing `node:crypto` instead of using `globalThis.crypto` HOT 11
- Insert or update an item without returning it HOT 3
- Performance problems with batched `findUnique()` HOT 6
- Can't use Prisma Client in Next.js middleware with `@prisma/adapter-pg` and `pg`, even locally HOT 9
- Docs include incorrect information regarding Vercel deployment fix HOT 2
- Add support for `pre`/`post` validation hooks in Prisma schema HOT 3
- Introspecting error with "Microsoft SQL Server 2019" database HOT 1
- Batched `findUnique()` is not chunked and exceeds parameter binding limits HOT 2
- Accelerate: "Invalid client engine type, please use `library` or `binary`" on Edge HOT 18
- Undefined "meta" when handling "PrismaClientKnownRequestError"
- Queries are getting executed sequentially even when using Interactive transactions API using Promise.all(). HOT 2
- Webstorm does not support prisma 5.15.0 version of preview functionality
- Is the time taken by a complex query in sql run using queryRaw and normal raw sql query the same or does Prisma have to run anything under the hood?
- `update`: Compile-time error is missing if suppliying non-objects to `data` argument
- Add full MongoDB queries to query spans
- findUnique behavior
- Accelerate breaks "PrismaClient is unable to run in this browser environment, or has been bundled for the browser" HOT 3
- Prisma (v5.12 & v5.14 & v5.15) client has errors accessing `cockroachdb` when using with cloudflare workers and the new `driverAdapter` preview feature
- Update behavior inconsistent between 1-n and 1-1 relation queries HOT 2
- Custom output path behaves different with `prismaSchemaFolder` preview feature HOT 10
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from prisma.