rphlmr ⚡ Posts - Answer Overflow

rphlmr ⚡

•Created by rphlmr ⚡ on 6/8/2023 in #help

[Postgres] prepared with inArray & placeholder

Hello there, I can't see why It ends with an error :/

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    inArray(Table.userId, placeholder("userIds"))
  .prepare("sum_query");

// later
const userIds = ['real', 'uuid', 's']
await sumQuery.execute({ userIds });

export const sumQuery = db
  .select({
    sum: sql<number>`cast(sum(${Table.amount}) as integer)`,
  })
  .from(Table)
  .where(
    inArray(Table.userId, placeholder("userIds"))
  .prepare("sum_query");

// later
const userIds = ['real', 'uuid', 's']
await sumQuery.execute({ userIds });

select cast(sum("amount") as integer) from "table" where "table"."user_id" in $1
[
  [
    '54a7291c-7ab5-4ff0-b4a6-612cc5397a57',
    '69312f6a-6eab-4b67-b4d2-be1800f40d73',
    'a80d62dd-b623-42eb-a853-b8caaddd0d1e'
  ]
]

select cast(sum("amount") as integer) from "table" where "table"."user_id" in $1
[
  [
    '54a7291c-7ab5-4ff0-b4a6-612cc5397a57',
    '69312f6a-6eab-4b67-b4d2-be1800f40d73',
    'a80d62dd-b623-42eb-a853-b8caaddd0d1e'
  ]
]

PostgresError: syntax error at or near "$1"

PostgresError: syntax error at or near "$1"

Any idea? It only fails with array kind placeholder 😢 Looks like () are missing?

12 replies

DTDrizzle Team

•Created by rphlmr ⚡ on 4/5/2023 in #help

Check

Hello there, from this discussion: https://discord.com/channels/1043890932593987624/1090486363251544124/1093092996200796200 Is it the way to add check constraint?

const companySize = ["tpe", "pme", "eti", "ge"] as const;

export const Company = pgTable(
  "company",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    companySize: text("company_size", { enum: [...companySize] }).notNull()
  },
  (Company) => ({
    onlyTheseSize: check(
      "only_these_size",
      sql`${Company.companySize} in(${companySize.join(",")})`
    ),
  })
);

const companySize = ["tpe", "pme", "eti", "ge"] as const;

export const Company = pgTable(
  "company",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    companySize: text("company_size", { enum: [...companySize] }).notNull()
  },
  (Company) => ({
    onlyTheseSize: check(
      "only_these_size",
      sql`${Company.companySize} in(${companySize.join(",")})`
    ),
  })
);

It produces nothing in the generated migration 🧐 drizzle-kit: v0.17.4 drizzle-orm: v0.23.5

10 replies

DTDrizzle Team

•Created by rphlmr ⚡ on 4/1/2023 in #help

Custom SQL function (json_agg & json_build_object)

Hello there. I'm new to Drizzle and I love it 😍. I have successfully made a jsonAgg helper.

function jsonAgg<T extends PgTable<TableConfig>>(table: T) {
  return sql<InferModel<T>[]>`json_agg(${table})`;
}

function jsonAgg<T extends PgTable<TableConfig>>(table: T) {
  return sql<InferModel<T>[]>`json_agg(${table})`;
}

Now I have trouble to make a jsonAggBuildObject 😅 that should produce something like this:

sql`json_agg(json_build_object('key1', ${table.col1}, 'key2', ${table.col2}, ...))`

sql`json_agg(json_build_object('key1', ${table.col1}, 'key2', ${table.col2}, ...))`

I try something but it end with an error error: could not determine data type of parameter $1

function jsonAggBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const shapeString = Object.entries(shape)
    .map(([key, value]) => {
      return `'${key}', ${value}`;
    })
    .join(",");

  return sql<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${shapeString}))`;
}

function jsonAggBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const shapeString = Object.entries(shape)
    .map(([key, value]) => {
      return `'${key}', ${value}`;
    })
    .join(",");

  return sql<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${shapeString}))`;
}

Any idea ?

31 replies

Gaming

Programming