mariojuanaaa Posts - Answer Overflow

mariojuanaaa

•Created by mariojuanaaa on 4/6/2024 in #help

Return on insert.onConflictDoNothing()

Is there a way to return data if insert.onConflictDoNothing() has conflicts? For example

            const insertedProducts = await db
                .insert(Product)
                .values(productData)
                .returning({
                    id: Product.id,
                    product_num: Product.product_num,
                    storeId: Product.storeId,
                })
                .onConflictDoNothing();

            const updatedProducts = await doSomething(
                insertedProducts,
            );

            const insertedPrices = await db
                .insert(Price)
                .values(updatedProducts) // i want to use insertedProducts here
                .returning({
                    id: Price.id,
                    productId: Price.productId,
                    storeId: Price.storeId,
                })
                .onConflictDoNothing();

            const insertedProducts = await db
                .insert(Product)
                .values(productData)
                .returning({
                    id: Product.id,
                    product_num: Product.product_num,
                    storeId: Product.storeId,
                })
                .onConflictDoNothing();

            const updatedProducts = await doSomething(
                insertedProducts,
            );

            const insertedPrices = await db
                .insert(Price)
                .values(updatedProducts) // i want to use insertedProducts here
                .returning({
                    id: Price.id,
                    productId: Price.productId,
                    storeId: Price.storeId,
                })
                .onConflictDoNothing();

I want to use insertedProducts again, and when insertedProducts returns something, it all works fine. However, when insertedProducts doesnt return something due to a conflict, it doesnt return anything and my code fails with

D:\..\sg-backend\node_modules\src\sqlite-core\query-builders\insert.ts:53
                        throw new Error('values() must be called with at least one value');
         ^
Error: values() must be called with at least one value
    at SQLiteInsertBuilder.values (D:\..\sg-backend\node_modules\src\sqlite-core\query-builders\insert.ts:53:10)
    at D:\..\sg-backend\app\common\db\writeToDB.ts:113:6
    at Generator.next (<anonymous>)
    at fulfilled (D:\..\sg-backend\app\common\db\writeToDB.ts:5:58)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)

D:\..\sg-backend\node_modules\src\sqlite-core\query-builders\insert.ts:53
                        throw new Error('values() must be called with at least one value');
         ^
Error: values() must be called with at least one value
    at SQLiteInsertBuilder.values (D:\..\sg-backend\node_modules\src\sqlite-core\query-builders\insert.ts:53:10)
    at D:\..\sg-backend\app\common\db\writeToDB.ts:113:6
    at Generator.next (<anonymous>)
    at fulfilled (D:\..\sg-backend\app\common\db\writeToDB.ts:5:58)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)

The only other solution i can think of is to read existing values from db if insertedProducts.length === 0

1 replies

DTDrizzle Team

•Created by mariojuanaaa on 4/5/2024 in #help

foreign key mismatch

how do i create a unique constraint on product_num, Store.store_num, Store.chainName

export const Store = sqliteTable(
    "Store",
    {
        id: text("id").primaryKey().unique(),
        store_num: text("store_num").notNull(),
        chain_name: text("chain_name").notNull(),
                ...
    },

    (t) => ({
        unique: unique().on(t.store_num, t.chain_name),
    })
);

export const Product = sqliteTable(
    "Product",
    {
        id: text("id").primaryKey().unique(),
        product_num: text("product_num").notNull(),
        store_num: text("store_num")
            .notNull()
            .references(() => Store.store_num),
        chainName: text("chainName")
            .notNull()
            .references(() => Store.chain_name),
                 ...
    },

    (t) => ({
        unique: unique().on(t.product_num, t.store_num, t.chainName), // how do i create a unique contraint on product_num, Store.store_num, Store.chainName
    })
);

export const Store = sqliteTable(
    "Store",
    {
        id: text("id").primaryKey().unique(),
        store_num: text("store_num").notNull(),
        chain_name: text("chain_name").notNull(),
                ...
    },

    (t) => ({
        unique: unique().on(t.store_num, t.chain_name),
    })
);

export const Product = sqliteTable(
    "Product",
    {
        id: text("id").primaryKey().unique(),
        product_num: text("product_num").notNull(),
        store_num: text("store_num")
            .notNull()
            .references(() => Store.store_num),
        chainName: text("chainName")
            .notNull()
            .references(() => Store.chain_name),
                 ...
    },

    (t) => ({
        unique: unique().on(t.product_num, t.store_num, t.chainName), // how do i create a unique contraint on product_num, Store.store_num, Store.chainName
    })
);

When i do drizzle-kit push:sqlite

LibsqlError: SQLITE_UNKNOWN: SQLite error: foreign key mismatch - "Product" referencing "Store"
    at mapHranaError (file:///D:/../../node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.execute (file:///D:/../../node_modules/@libsql/client/lib-esm/http.js:56:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async TursoSqlite.run (D:\..\\node_modules\drizzle-kit\bin.cjs:25414:9)
    at async Command.<anonymous> (D:\gitpersonal\sg-backend\node_modules\drizzle-kit\bin.cjs:63360:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: foreign key mismatch - "Product" referencing "Store"] {
      message: 'SQLite error: foreign key mismatch - "Product" referencing "Store"',

LibsqlError: SQLITE_UNKNOWN: SQLite error: foreign key mismatch - "Product" referencing "Store"
    at mapHranaError (file:///D:/../../node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpClient.execute (file:///D:/../../node_modules/@libsql/client/lib-esm/http.js:56:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async TursoSqlite.run (D:\..\\node_modules\drizzle-kit\bin.cjs:25414:9)
    at async Command.<anonymous> (D:\gitpersonal\sg-backend\node_modules\drizzle-kit\bin.cjs:63360:9) {
  code: 'SQLITE_UNKNOWN',
  [cause]: [ResponseError: SQLite error: foreign key mismatch - "Product" referencing "Store"] {
      message: 'SQLite error: foreign key mismatch - "Product" referencing "Store"',

6 replies

Gaming

Programming