goofysystem Posts - Answer Overflow

goofysystem

Explore posts from servers

CDCloudflare Developers DTDrizzle Team

Posts Comments

DTDrizzle Team

•Created by goofysystem on 10/12/2024 in #help

Orphaned Children tables after a successful migration

1 replies

DTDrizzle Team

•Created by goofysystem on 10/12/2024 in #help

Orphaned children tables after a successful migration

I was able to bypass sqlite constraint error by using PRAGMA foreign_keys option but it left my database in a corrupted state where children tables of ticket_order are referring to the temporary and now deleted ticket_order_old table

PRAGMA foreign_keys=off;--> statement-breakpoint
ALTER TABLE `ticket_order` RENAME TO `ticket_order_old`;--> statement-breakpoint
CREATE TABLE `ticket_order` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `nano_id` text NOT NULL,
    `event_id` text NOT NULL,
    `customer_id` text NOT NULL,
    `checkout_session_id` text NOT NULL,
    `order_status` text NOT NULL,
    `currency` text NOT NULL,
    `total_price` real NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now')),
    FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE no action ON DELETE no action
);--> statement-breakpoint
CREATE UNIQUE INDEX `ticket_order_nano_id_unique` ON `ticket_order` (`nano_id`);--> statement-breakpoint

INSERT INTO ticket_order (nano_id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at)
SELECT id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at
FROM ticket_order_old;--> statement-breakpoint

DROP TABLE `ticket_order_old`;--> statement-breakpoint
PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;--> statement-breakpoint
ALTER TABLE `ticket_order` RENAME TO `ticket_order_old`;--> statement-breakpoint
CREATE TABLE `ticket_order` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `nano_id` text NOT NULL,
    `event_id` text NOT NULL,
    `customer_id` text NOT NULL,
    `checkout_session_id` text NOT NULL,
    `order_status` text NOT NULL,
    `currency` text NOT NULL,
    `total_price` real NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now')),
    FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE no action ON DELETE no action
);--> statement-breakpoint
CREATE UNIQUE INDEX `ticket_order_nano_id_unique` ON `ticket_order` (`nano_id`);--> statement-breakpoint

INSERT INTO ticket_order (nano_id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at)
SELECT id, event_id, customer_id, checkout_session_id, order_status, currency, total_price, created_at, updated_at
FROM ticket_order_old;--> statement-breakpoint

DROP TABLE `ticket_order_old`;--> statement-breakpoint
PRAGMA foreign_keys=on;

1 replies

DTDrizzle Team

•Created by goofysystem on 7/28/2024 in #help

Getting SQLITE Constraint error with `foreign_keys off`

All I'm doing is dropping default fields from the tables. I had recently learned about PRAGMA foreign_key=off thing and had previously been manually moving over children tables every migration 💀 . But this non-cursed way seems to not work as expected as I'm running into foreign key constraint check. Any help would be greatly appreciated. Below is the generated sql file and I'm using Turso.

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

PRAGMA foreign_keys=off;
--> statement-breakpoint

ALTER TABLE team_member RENAME TO team_member_old;--> statement-breakpoint
ALTER TABLE tournament RENAME TO tournament_old;--> statement-breakpoint

CREATE TABLE `team_member` (
    `id` text PRIMARY KEY NOT NULL,
    `team_id` text NOT NULL,
    `position` text NOT NULL,
    `invite_status` text NOT NULL,
    `user_id` text NOT NULL,
    FOREIGN KEY (`team_id`) REFERENCES `team`(`id`) ON UPDATE no action ON DELETE no action,
    FOREIGN KEY (`user_id`) REFERENCES `dancer`(`user_id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint

INSERT INTO team_member (id, team_id, position, invite_status, user_id)
SELECT id, team_id, position, invite_status, user_id
FROM team_member_old;
--> statement-breakpoint

CREATE TABLE `tournament` (
    `id` text PRIMARY KEY NOT NULL,
    `version` text NOT NULL,
    `battle_id` text NOT NULL,
    `status` text NOT NULL,
    `created_at` text DEFAULT (strftime('%s', 'now')),
    `updated_at` text DEFAULT (strftime('%s', 'now'))
);
--> statement-breakpoint

INSERT INTO tournament (id, version, battle_id, status, created_at, updated_at)
SELECT id, version, battle_id, status, created_at, updated_at
FROM tournament_old;
--> statement-breakpoint

DROP TABLE team_member_old;--> statement-breakpoint
DROP TABLE tournament_old;--> statement-breakpoint

PRAGMA foreign_key_check;--> statement-breakpoint

PRAGMA foreign_keys=on;

4 replies

DTDrizzle Team

•Created by goofysystem on 3/5/2024 in #help

Error performing migration: LibsqlError: SQLITE_UNKNOWN: SQLite error: cannot rollback

I've already tried downgrading to drizzle kit v0.19 as advised in https://discord.com/channels/933071162680958986/933071163184283651/1204583958679912488 Migration works locally with sqld instance but fails when run against the remote turso database.

Error performing migration:  LibsqlError: SQLITE_UNKNOWN: SQLite error: cannot rollback - no transaction is active
    at mapHranaError (file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:286:16)
    at file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:170:37
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async HttpTransaction.rollback (file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:172:13)
    at LibSQLSession.transaction (/home/runner/work/utc/utc/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/libsql/session.ts:90:4)
    at SQLiteAsyncDialect.migrate (/home/runner/work/utc/utc/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/sqlite-core/dialect.ts:772:3)
    at main (/home/runner/work/utc/utc/infra/db/tournament/src/migrate.ts:14:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: cannot rollback - no transaction is active] {
    code: 'SQLITE_UNKNOWN',
    proto: {
      message: 'SQLite error: cannot rollback - no transaction is active',
      code: 'SQLITE_UNKNOWN'
    }
  }
}
 ELIFECYCLE  Command failed with exit code 1.
error: Recipe `migrate` failed on line 19 with exit code 1

Error performing migration:  LibsqlError: SQLITE_UNKNOWN: SQLite error: cannot rollback - no transaction is active
    at mapHranaError (file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:286:16)
    at file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:170:37
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async HttpTransaction.rollback (file:///home/runner/work/utc/utc/node_modules/.pnpm/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:172:13)
    at LibSQLSession.transaction (/home/runner/work/utc/utc/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/libsql/session.ts:90:4)
    at SQLiteAsyncDialect.migrate (/home/runner/work/utc/utc/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/sqlite-core/dialect.ts:772:3)
    at main (/home/runner/work/utc/utc/infra/db/tournament/src/migrate.ts:14:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: cannot rollback - no transaction is active] {
    code: 'SQLITE_UNKNOWN',
    proto: {
      message: 'SQLite error: cannot rollback - no transaction is active',
      code: 'SQLITE_UNKNOWN'
    }
  }
}
 ELIFECYCLE  Command failed with exit code 1.
error: Recipe `migrate` failed on line 19 with exit code 1

1 replies

DTDrizzle Team

•Created by goofysystem on 1/13/2024 in #help

How to fetch with multiple ids

    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id IN (3,4) AND event_id = ${eventId}`);

    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id IN (3,4) AND event_id = ${eventId}`);

This query gives me:

RangeError: Only finite numbers (not Infinity or NaN) can be passed as arguments

RangeError: Only finite numbers (not Infinity or NaN) can be passed as arguments

Hows everyone fetching with multiple ids?

8 replies

Gaming

Programming