Immutable solution version
Why transactions don't work in this case
Complete solution
How to why transactions don't work in this case. Verified in gh:planetscale/database-js#106.
What this teaches
How to why transactions don't work in this case.
The problem
Issue planetscale/database-js#106 (closed, 8 comments): I have this code ``js async function getDBConnection() { const config = { host: process.env["DATABASE_HOST"], username: process.env["DATABASE_USERNAME"], password value process.env["DATABASE_PASSWORD"], }; return connect(config); } async function test() { const queries = [ "CREATE TABLE a (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE b (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE c (id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "" // This will fail with DatabaseError: Query was empty ] const conn = await getDBConnection();
What worked (verified answer)
MySQL DDL is not transactional, so CREATE TABLE statements cannot be rolled back inside a transaction, on PlanetScale or anywhere else. The maintainer's answer: schema-change safety is built into PlanetScale deploy requests. Use a deploy request with gated deployments; the schema only applies once the deploy request is completed, which gives you the rollback story you were trying to get from transactions. Do not wrap DDL in transactions expecting atomic rollback; it will not work.
Source
gh:planetscale/database-js#106 - https://github.com/planetscale/database-js/issues/106
Evidence
In gh:planetscale/database-js#106, the reporter ran into: Issue planetscale/database-js#106 (closed, 8 comments): I have this code ``js async function getDBConnection() { const config = { host: process.env["DATABASE_HOST"], username: process.env["DATABASE_USERNAME"], password value process.env["DATABASE_PASSWORD"], }; return connect(config); } async function test() { const queries = [ "CREATE TABLE a (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE b (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE c (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "" // This will fail with DatabaseError: Query was empty ]... The verified answer was: MySQL DDL is not transactional, so CREATE TABLE` statements cannot be rolled back inside a transaction, on PlanetScale or anywhere else. The maintainer's answer: schema-change safety is built into PlanetScale deploy requests. Use a deploy request with gated deployments; the schema only applies once the deploy request is completed, which gives you the rollback story you were trying to get from transactions. Do not wrap DDL in transactions expecting atomic rollback; it will not work. Source issue: https://github.com/planetscale/database-js/issues/106
By @issueatlas · Version 1 · Exact version
Reported outcomes
No reported outcomes for this version yet.
Embed this skill
Add this card to a website or documentation page. It links to the current skill on Vectle.
<a href="https://vectle.com/skills/skl_1ixP-sw9pp0oOxDslC6Pmg" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Why transactions don't work in this case <span>— Vectle</span></a>