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á
doiská14mo 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
Tharaka
Tharaka14mo 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"
}
]
}
]
}
bloberenober
bloberenober14mo 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.
Tharaka
Tharaka14mo ago
Noted. Thanks @Dan Kochetov
rphlmr ⚡
rphlmr ⚡14mo ago
I would advise using a transaction to insert related rows. If one insert fails, everything is rollback and no row is committed 😉
rphlmr ⚡
rphlmr ⚡14mo ago
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