Robin Posts - Answer Overflow

Robin

•Created by Robin on 1/4/2024 in #help

Type errors after 0.27.1 upgrade

After upgrading to the lastest verision, I've got a couple of type errors that I'm not sure how to resolve. They are both the same kind of error, I have a custom WHERE IN expression:

eb(
  "objects.type",
  "in",
  sql.raw(
    `(${ALLOWED_OBJECT_TYPES.map((o) => `'${o}'`).join(",")})`,
  ),
),

eb(
  "objects.type",
  "in",
  sql.raw(
    `(${ALLOWED_OBJECT_TYPES.map((o) => `'${o}'`).join(",")})`,
  ),
),

Which gives me this error:

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'OperandValueExpressionOrList<DB & { target_objects: { object_id: string; }; } & { objects: { id: string; oid: number; schema_id: string; name: string; type: string; owner_id: string; privileges: string[] | null; }; } & { transitive_dependencies: { ...; }; } & { ...; } & { ...; } & { ...; } & { ...; }, "objects" | "s...'.
  Property 'isSelectQueryBuilder' is missing in type 'RawBuilder<unknown>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

60               sql.raw(
                 ~~~~~~~~
61                 `(${ALLOWED_OBJECT_TYPES.map((o) => `'${o}'`).join(",")})`,
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
62               ),
   ~~~~~~~~~~~~~~~

Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'OperandValueExpressionOrList<DB & { target_objects: { object_id: string; }; } & { objects: { id: string; oid: number; schema_id: string; name: string; type: string; owner_id: string; privileges: string[] | null; }; } & { transitive_dependencies: { ...; }; } & { ...; } & { ...; } & { ...; } & { ...; }, "objects" | "s...'.
  Property 'isSelectQueryBuilder' is missing in type 'RawBuilder<unknown>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

60               sql.raw(
                 ~~~~~~~~
61                 `(${ALLOWED_OBJECT_TYPES.map((o) => `'${o}'`).join(",")})`,
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
62               ),
   ~~~~~~~~~~~~~~~

I assume I need to pass a type to sql.raw, but I haven't been able to figure out what that type should be.

15 replies

KKysely

•Created by Robin on 12/29/2023 in #help

Arbitrary insert statement

I have a bit of an edge case where I want to build a query that is dynamic based on user input (it's part of an interactive tutorial). Is there a way to build an insert statement where the object name is an arbitrary string? Alternatively, is there some way to do this with sql framemgents, but still make the value a parameter? I'm imagining a sql.parameter function, but I don't see anything like that in the docs.

6 replies

KKysely

•Created by Robin on 9/20/2023 in #help

New type error in 0.26.3

I recently upgraded from 0.26.1 to 0.26.3 and I'm getting a type error trying to call a reusable expression builder that used to work.

export function isOwner() {
  return (eb: ExpressionBuilder<DB & Record<"r", MzCatalogMzRoles>, "r">) =>
    eb
      .or([
        eb.fn("mz_is_superuser", []),
        eb.fn("has_role", [sql.ref("current_user"), `r.oid`, sql.lit("USAGE")]),
      ])
      .$castTo<boolean>()
      .as("isOwner");
}

export function isOwner() {
  return (eb: ExpressionBuilder<DB & Record<"r", MzCatalogMzRoles>, "r">) =>
    eb
      .or([
        eb.fn("mz_is_superuser", []),
        eb.fn("has_role", [sql.ref("current_user"), `r.oid`, sql.lit("USAGE")]),
      ])
      .$castTo<boolean>()
      .as("isOwner");
}

They query I'm calling it from has a bunch of other tables joined, but it have MzCatalogMzRoles as r so I would expect this to work The type error is pretty unfortunate, I can't make much sense of it. I've attached it to the post in case it is useful. Any suggestions appreciated!

42 replies

KKysely

•Created by Robin on 8/3/2023 in #help

Join using a json value with postgres

I have this table:

export interface MzCatalogMzAuditEvents {
  id: Generated<string>;
  event_type: Generated<string>;
  object_type: Generated<string>;
  details: Generated<Json>;
  user: Generated<string | null>;
  occurred_at: Generated<Timestamp>;
}

export interface MzCatalogMzAuditEvents {
  id: Generated<string>;
  event_type: Generated<string>;
  object_type: Generated<string>;
  details: Generated<Json>;
  user: Generated<string | null>;
  occurred_at: Generated<Timestamp>;
}

I'm trying to write this join:

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(eb.ref("ae.details", "->>").key("id"), "=", "o.id")
  )
)

.innerJoin("mz_catalog.mz_audit_events as ae", (join) =>
  join.on(
    (eb) => eb(eb.ref("ae.details", "->>").key("id"), "=", "o.id")
  )
)

But I get this error on the key(id):

Argument of type 'string' is not assignable to parameter of type 'never'.

Argument of type 'string' is not assignable to parameter of type 'never'.

and this one the o.id:

Argument of type '"o.id"' is not assignable to parameter of type 'OperandValueExpressionOrList<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 121 more ...; ae: MzCatalogMzAuditEvents; }, "o" | ... 2 more ... | "ae", TraversedJSONPathBu...'. (tsserver 2345)

Argument of type '"o.id"' is not assignable to parameter of type 'OperandValueExpressionOrList<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 121 more ...; ae: MzCatalogMzAuditEvents; }, "o" | ... 2 more ... | "ae", TraversedJSONPathBu...'. (tsserver 2345)

26 replies

KKysely

•Created by Robin on 8/3/2023 in #help

How to use coalesce in a join

I'm selecting disparate object types, and then looking up details in per-type lookup tables, then I want to join on another table based on an ID in the type tables, so I need to coalesce the ID columns. Currently I'm just doing a cast to make it work:

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

Is there a better way to express this?

22 replies

KKysely

•Created by Robin on 6/9/2023 in #help

Limit with a literal value

Hi folks, thanks for all your work on Kysely, it's great! I'm wondering how I can add a limit clause with a literal value, rather than a parameter. I have a strange use case, I'm querying a https://materialize.com/ database, which presents as postgres, but has different features. It also has some limitations, one of which is limit must be a literal value, not an expression or parameter. I figured out I can do this

query.modifyEnd(sql`limit ${sql.raw(limit.toString())}`);

query.modifyEnd(sql`limit ${sql.raw(limit.toString())}`);

Which works, but it's a bit unwieldy. Is there a nice way to add extension methods to the query builder? The docs seem to discourage this, but I'm curious if there are any other ideas to make this nicer. I would love to have an api like this:

query.rawLimit(limit)

query.rawLimit(limit)

7 replies

Gaming

Programming