goofysystem Posts - Answer Overflow

goofysystem

Explore posts from servers

•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;

2 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/@libsql+client@0.3.1/node_modules/@libsql/client/lib-esm/hrana.js:286:16)
    at file:///home/runner/work/utc/utc/node_modules/.pnpm/@libsql+client@0.3.1/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/@libsql+client@0.3.1/node_modules/@libsql/client/lib-esm/hrana.js:172:13)
    at LibSQLSession.transaction (/home/runner/work/utc/utc/node_modules/.pnpm/drizzle-orm@0.29.2_@cloudflare+workers-types@4.20231010.0_@libsql+client@0.3.1_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/libsql/session.ts:90:4)
    at SQLiteAsyncDialect.migrate (/home/runner/work/utc/utc/node_modules/.pnpm/drizzle-orm@0.29.2_@cloudflare+workers-types@4.20231010.0_@libsql+client@0.3.1_@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/@libsql+client@0.3.1/node_modules/@libsql/client/lib-esm/hrana.js:286:16)
    at file:///home/runner/work/utc/utc/node_modules/.pnpm/@libsql+client@0.3.1/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/@libsql+client@0.3.1/node_modules/@libsql/client/lib-esm/hrana.js:172:13)
    at LibSQLSession.transaction (/home/runner/work/utc/utc/node_modules/.pnpm/drizzle-orm@0.29.2_@cloudflare+workers-types@4.20231010.0_@libsql+client@0.3.1_@types+react@1_3cr644gqxuhfs2c75frekmndw4/node_modules/src/libsql/session.ts:90:4)
    at SQLiteAsyncDialect.migrate (/home/runner/work/utc/utc/node_modules/.pnpm/drizzle-orm@0.29.2_@cloudflare+workers-types@4.20231010.0_@libsql+client@0.3.1_@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