Kysely•13mo ago

insert into with mix of static and table values

How might I execute an insert into that combines JS-side values with a select, like:

INSERT INTO some_table (a, b, c, d) SELECT o.a, o.b, 16, 'hello world' FROM other_table O WHERE somekey = 32;

INSERT INTO some_table (a, b, c, d) SELECT o.a, o.b, 16, 'hello world' FROM other_table O WHERE somekey = 32;

(Where, 16, 32 and 'hello world' are JS variables, not true literals)

4 Replies

Igal•13mo ago

Hey 👋 Something like this? https://kyse.link/?p=s&i=IEOBYyjLCvHjMa5JzUmN

import { sql } from "kysely"

const sixteen = 16
const thirthyTwo = 32
const helloWorld = "hello world"

const result = await kysely
  .insertInto("some_table")
  .columns(["a", "b", "c", "d"])
  .expression(
    kysely
      .selectFrom("other_table as O")
      .where("somekey", "=", thirthyTwo)
      .select([
        "O.a",
        "O.b",
        sql.val(sixteen).as("c"),
        sql.val(helloWorld).as("d"),
      ]),
  )
  .execute()

import { sql } from "kysely"

const sixteen = 16
const thirthyTwo = 32
const helloWorld = "hello world"

const result = await kysely
  .insertInto("some_table")
  .columns(["a", "b", "c", "d"])
  .expression(
    kysely
      .selectFrom("other_table as O")
      .where("somekey", "=", thirthyTwo)
      .select([
        "O.a",
        "O.b",
        sql.val(sixteen).as("c"),
        sql.val(helloWorld).as("d"),
      ]),
  )
  .execute()

djMax•13mo ago

Yes, like that! I thought it was kysely.val (as did GPT), but didn't see the sql one. Also weird that it needs the alias, but doable.

Igal•13mo ago

GPT doesn't know kysely. It always lies.

koskimas•13mo ago

Yep, Kysely didn't exist when the GPT training data was collected.

Gaming

Programming

insert into with mix of static and table values