Drizzle Team•2y ago

Inserting records into related tables

I have a few of tables that are related. I just need to know if there's a better approach when inserting records into these. I'm using PlanetScale so no FK constraints. Here's my approach.

export const product = mysqlTable('product', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }),
    description: varchar('description', { length: 256 }),
});

export const productVariant = mysqlTable('product_variant', {
    id: serial('id').primaryKey(),
    productId: int('product_id'),
    name: varchar('name', { length: 256 }),
    description: varchar('description', { length: 256 }),
});

export const variantAsset = mysqlTable('variant_asset', {
    id: serial('id').primaryKey(),
    variantId: int('variant_id'),
    assetName: varchar('asset_name', { length: 256 })
});

export const product = mysqlTable('product', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }),
    description: varchar('description', { length: 256 }),
});

export const productVariant = mysqlTable('product_variant', {
    id: serial('id').primaryKey(),
    productId: int('product_id'),
    name: varchar('name', { length: 256 }),
    description: varchar('description', { length: 256 }),
});

export const variantAsset = mysqlTable('variant_asset', {
    id: serial('id').primaryKey(),
    variantId: int('variant_id'),
    assetName: varchar('asset_name', { length: 256 })
});

Insert statements

const newProduct = await db.insert(product).values({
  name: 'Product name',
  description: 'Product description',
});

const newProductVariant = await db.insert(productVariant).values({
 ** productId: newProduct.insertId**,
  name: 'Variant name',
  description: 'Variant description',
});

const newVariantAsset = await db.insert(variantAsset).values({
 ** variantId: newProductVariant.insertId,**
  assetName: 'Asset Name',
});

const newProduct = await db.insert(product).values({
  name: 'Product name',
  description: 'Product description',
});

const newProductVariant = await db.insert(productVariant).values({
 ** productId: newProduct.insertId**,
  name: 'Variant name',
  description: 'Variant description',
});

const newVariantAsset = await db.insert(variantAsset).values({
 ** variantId: newProductVariant.insertId,**
  assetName: 'Asset Name',
});

I just need to know if there's a better approach to this? Instead of writing three, can we batch them?

6 Replies

doiská•2y ago

Hi, i don't know if its a better approach, but you could predict the uniqueId in server-side, then make the three inserts. This way you dont need to wait three responses

TharakaOP•2y ago

I see. Thanks for the suggestion @doiská It gets a bit trickier when your payload is an array. Ex 👇 . Prisma has a way of nesting the inserts. Just wondering if I can do the same with Drizzle

const payload = {
   "products":[
      {
         "name":"Product 1",
         "description":"Product description 1",
         "variant":[
            {
               "name":"Variant 1",
               "description":"Variant description 1"
            },
            {
               "name":"Variant 2",
               "description":"Variant description 2"
            }
         ]
      },
      {
         "name":"Product 2",
         "description":"Product description 2",
         "variant":[
            {
               "name":"Variant 3",
               "description":"Variant description 3"
            },
            {
               "name":"Variant 4",
               "description":"Variant description 4"
            }
         ]
      }
   ]
}

const payload = {
   "products":[
      {
         "name":"Product 1",
         "description":"Product description 1",
         "variant":[
            {
               "name":"Variant 1",
               "description":"Variant description 1"
            },
            {
               "name":"Variant 2",
               "description":"Variant description 2"
            }
         ]
      },
      {
         "name":"Product 2",
         "description":"Product description 2",
         "variant":[
            {
               "name":"Variant 3",
               "description":"Variant description 3"
            },
            {
               "name":"Variant 4",
               "description":"Variant description 4"
            }
         ]
      }
   ]
}

Dan•2y ago

No, you can't insert related rows in 1 query. It's not possible with SQL in general, so Prisma splits it into multiple inserts under the hood. Honestly, I'm unsure if there's a way to do this efficiently, so my best guess is to insert the products one by one and use the insertId field returned from the query. I suspect Prisma does something similar.

TharakaOP•2y ago

Noted. Thanks @Dan Kochetov

rphlmr ⚡•2y ago

I would advise using a transaction to insert related rows. If one insert fails, everything is rollback and no row is committed 😉

rphlmr ⚡•2y ago

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/README.md#transactions https://github.com/planetscale/database-js#transactions

GitHub

drizzle-orm/README.md at main · drizzle-team/drizzle-orm

TypeScript ORM for SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

GitHub

GitHub - planetscale/database-js: A Fetch API-compatible PlanetScal...

A Fetch API-compatible PlanetScale database driver - GitHub - planetscale/database-js: A Fetch API-compatible PlanetScale database driver

Gaming

Programming

Inserting records into related tables