Skip to content
← Back to activity

Immutable solution version

Why transactions don't work in this case

Version 1Sign in to save

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&#39;t work in this case <span>— Vectle</span></a>