goofysystem Comments - Answer Overflow

goofysystem

Explore posts from servers

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

Getting SQLITE Constraint error with `foreign_keys off`

It seems that is no way to manipulate PRAGMA during migration with drizzle: https://github.com/drizzle-team/drizzle-orm/issues/1813 I even tried doing this to my migration setup to set foreign_keys as off but found no success:

import { migrate } from 'drizzle-orm/libsql/migrator';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

export const client = createClient({
    url: process.env.TOURNAMENT_DB_URL as string,
    authToken: process.env.TOURNAMENT_DB_AUTH_TOKEN as string,
});

export const db = drizzle(client);

async function main() {
    try {
        await client.execute('PRAGMA foreign_keys = OFF');
        await migrate(db, {
            migrationsFolder: './migrations',
        });
        await client.execute('PRAGMA foreign_keys = ON');
        console.log('Tables migrated!');
        process.exit(0);
    } catch (error) {
        console.error('Error performing migration: ', error);
        process.exit(1);
    }
}

main();

import { migrate } from 'drizzle-orm/libsql/migrator';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

export const client = createClient({
    url: process.env.TOURNAMENT_DB_URL as string,
    authToken: process.env.TOURNAMENT_DB_AUTH_TOKEN as string,
});

export const db = drizzle(client);

async function main() {
    try {
        await client.execute('PRAGMA foreign_keys = OFF');
        await migrate(db, {
            migrationsFolder: './migrations',
        });
        await client.execute('PRAGMA foreign_keys = ON');
        console.log('Tables migrated!');
        process.exit(0);
    } catch (error) {
        console.error('Error performing migration: ', error);
        process.exit(1);
    }
}

main();

2 replies

DTDrizzle Team

•Created by romulan on 7/8/2024 in #help

how to set pragma foreign_keys = off, during migrations?

I'm having the same issue. Were you able to get this fixed? Upon digging maybe it is not possible with drizzle? Hope it gets solved bc I am tired of having to copy over the data of children tables when I alter table on a parent table: Github Issue on this: https://github.com/drizzle-team/drizzle-orm/issues/1813

2 replies

DTDrizzle Team

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

How to fetch with multiple ids

@Angelelz thank you!

8 replies

DTDrizzle Team

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

How to fetch with multiple ids

The solution:

    const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in ${sql.raw(`(${ticketListingIds.join(',')})`)} and event_id = ${eventId}`);

    const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in ${sql.raw(`(${ticketListingIds.join(',')})`)} and event_id = ${eventId}`);

8 replies

DTDrizzle Team

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

How to fetch with multiple ids

The first code block works now. I just need to make everything lowercase. But now...

// Returns two rows
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (3,4) and event_id = ${eventId}`);

// Returns no rows
const ticketListingIds = [3,4];    
const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (${ticketListingIds.join(',')}) and event_id = ${eventId}`);

// Returns two rows
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (3,4) and event_id = ${eventId}`);

// Returns no rows
const ticketListingIds = [3,4];    
const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (${ticketListingIds.join(',')}) and event_id = ${eventId}`);

8 replies

DTDrizzle Team

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

How to fetch with multiple ids

and this does not work :/

const ticketListingIds = [3,4]
    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (${ticketListingIds.join(', ')}) and event_id = ${eventId}`,
    );

const ticketListingIds = [3,4]
    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (${ticketListingIds.join(', ')}) and event_id = ${eventId}`,
    );

8 replies

DTDrizzle Team

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

How to fetch with multiple ids

So this works as expected

    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (3, 4) and event_id = ${eventId}`,
    );

    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (3, 4) and event_id = ${eventId}`,
    );

8 replies

Gaming

Programming