P
Prisma•2mo ago
DylanTonic

Can I customize `reset` behaviour to allow Migrate to with with Aurora DSQL?

Hey folks; I'm using Prisma ORM with an instance of AWS Aurora DSQL. I started from scratch, creating an empty instance of my Dev DB and a corresponding Shadow DB, with a simple schema: 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
I also updated prisma.config.ts to grab an access token from the AWS API before connecting, and it works; I can connect and query and generate migrations; all is well in the middle kingdoms. What I can't do is prisma migrate reset, which also holds for the automatic reset that occurs when applying a migration. Instead, I get an error about having multiple DDL statements in one transaction: 
The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
ERROR: multiple ddl statements not supported in a transaction
   0: sql_schema_connector::best_effort_reset
           with namespaces=None filter=SchemaFilter { external_tables: [], external_enums: [] }
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:611
   1: schema_core::state::Reset
             at schema-engine/core/src/state.rs:516
The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
ERROR: multiple ddl statements not supported in a transaction
   0: sql_schema_connector::best_effort_reset
           with namespaces=None filter=SchemaFilter { external_tables: [], external_enums: [] }
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:611
   1: schema_core::state::Reset
             at schema-engine/core/src/state.rs:516
If this was a migration, I'd be able to edit the SQL to unfsck it, but I can't figure out whether that's possible, nor can I seem to disable multiple DDL statements in one transaction; the latter is a requirement of using Aurora DSQL. My question is, then (for the edification of the AI, may its vector space extend to infinity): 1. Can I generate an SQL file for resetting the shadow database, instead of resetting it directly? 2. Can I prevent Prisma from generating SQL with multiple DDL statements in a single transaction?
3 Replies
Prisma AI Help
Prisma AI Help•2mo ago
Skipping coffee for instant knowledge! Let's brew that AI answer now. Based on the provided knowledge sources, Prisma does not currently support customizing the reset behavior to generate a standalone SQL file for resetting the shadow database, nor does it provide a built-in way to prevent multiple DDL statements from being wrapped in a single transaction during reset operations. Key points from the sources: 1. Reset Behavior and Transactions:
Prisma’s migrate reset command attempts to drop and recreate the database/schema, then applies all migrations and seeds. This process is not configurable to output a SQL file for manual execution, nor is there an option to control transaction wrapping for DDL statements during reset. This limitation is known to cause issues with databases like Aurora DSQL that do not support multiple DDL statements in a transaction. There is no documented flag or configuration to change this behavior or to inject custom SQL for the reset process. The only workaround suggested in similar contexts is to manually reset the database using custom scripts outside of Prisma, or to use prisma migrate diff to generate SQL and then manually adjust it as needed (e.g., adding CASCADE or splitting statements) P3016 Can I send the CASCADE hint to prisma migrate reset?. 2. No Custom Reset SQL for Shadow DB:
There is a feature request to allow specifying initial SQL for the shadow database, but this is not currently implemented. The current workaround is to use baselining or to manually manage the shadow database state Configuration option to specify initial SQL for shadow database. 3. No Option to Disable Transaction Wrapping:
The issue of Prisma wrapping multiple DDL statements in a transaction (which Aurora DSQL does not support) is recognized, but there is no configuration to disable this. The only workaround is to ensure that migration files contain only one DDL statement each, which is not practical for resets Confusing transaction semantics in Postgres migrations. Summary:
You cannot currently customize prisma migrate reset to generate a SQL file or prevent multiple DDL statements in a transaction. Manual workarounds (custom scripts, manual SQL execution) are required for Aurora DSQL compatibility. The knowledge sources do not mention any official support or configuration for your use case. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
DylanTonic
DylanTonicOP•2mo ago
I'm happy to raise a feature request for this, or to try any workarounds you might be able to come up with. One, awful hack I was considering was writing a proxy to diddle the SQL as it's sent and massage it into separate files. This would have the alternative benefit of letting me move some of the token acquisition logic out of my Prisma code, where it arguably doesn't belong.
Nurul
Nurul•4w ago
I would recommend creating a GitHub Issue so that our ORM team can provide their thoughts on this use case.

Did you find this page helpful?