0%
December 23, 2023

Run Kysely Generated SQL in Tableplus

sql

In express if with log the query built by kysely via the config:

export const db = new Kysely<DB>({
    dialect,
    log(event) {
        if (event.level === "query") {
            console.log(event.query.sql);
            console.log(event.query.parameters);
        }
    }
})

then we eventually get the following kind of queries to prevent sql injection:

select *, (select to_json(obj) from (select count("MessagesSession"."id") as "count" from "MessagesSession" where "MessagesSession"."channelId" = "Channel"."id" and "MessagesSession"."hostUserId" = $1 and "MessagesSession"."isDeleted" != $2 and "MessagesSession"."isDraftInstantIssue" = $3) as obj) as "draft" from "Channel" where "Channel"."projectId" = $4 and "Channel"."isDeleted" = $5
[
  '77af0d8c-46aa-44c1-bb2b-730033cbf188',
  true,
  true,
  'ff515aff-d53f-407c-9c11-602998b74ba8',
  false
]

To exeucute this in our ordinary SQL shell we can prepare and execute it:

prepare plan(uuid, bool, bool, uuid, bool) as 
	select *, (select to_json(obj) from (select count("MessagesSession"."id") as "count" from "MessagesSession" where "MessagesSession"."channelId" = "Channel"."id" and "MessagesSession"."hostUserId" = $1 and "MessagesSession"."isDeleted" != $2 and "MessagesSession"."isDraftInstantIssue" = $3) as obj) as "draft" from "Channel" where "Channel"."projectId" = $4 and "Channel"."isDeleted" = $5;

execute plan(
  '77af0d8c-46aa-44c1-bb2b-730033cbf188',
  true,
  true,
  'ff515aff-d53f-407c-9c11-602998b74ba8',
  false
)

From this we get:

Now we can investigate what's wrong with our query.