Raw SQL builder transforms camelCase to lowerCase

[bitkidd]

Hello! First of all thank you for a great library, I find it very intuitive and nice.

The only problem so far that I have with it is that for some reason raw SQL builder transforms my camelCase db column names into lowercase and this leads to an error with this column does not exist.

For example:

await db.selectFrom('User').selectAll().where(sql`createdAt BETWEEN 2023-09-10 AND 2023-09-12`)

In this case the query will look like this:

SELECT * FROM User WHERE createdat BETWEEN '2023-09-10' AND '2023-09-12'

Is there a way to prevent this?

The only way I see how I can fix it, is to rename all the columns to snake_case but this is a last resort way.

[koskimas]

Kysely doesn't parse SQL so we don't do any transformations for raw SQL. We'd need to bundle SQL parsers for all supported dialects if we did and that'd be insane.

The issue comes from the fact that identifiers in SQL are case insensitive unless you wrap them.

This would work (assuming you're using postgres. On mysql you need to use backticks):

await db
  .selectFrom('User')
  .selectAll()
  .where(sql`"createdAt" BETWEEN 2023-09-10 AND 2023-09-12`)

But you can use sql.ref or eb.ref.

await db
  .selectFrom('User')
  .selectAll()
  .where(sql`${sql.ref('createdAt')} BETWEEN 2023-09-10 AND 2023-09-12`)

if you use the expression builder's ref method you also get type-safety and autocompletion:

await db
  .selectFrom('User')
  .selectAll()
  .where(eb => sql`${eb.ref('createdAt')} BETWEEN 2023-09-10 AND 2023-09-12`)

But in this particular case, you can simply use the between method on the expression builder

await db
  .selectFrom('User')
  .selectAll()
  .where(eb => eb.between('createdAt', '2023-09-10', '2023-09-12'))