Currently, I am utilizing the Kysely SQL builder for JS based on Vercel's recommendation, despite the limited documentation and community support. This SQL builder is fully typed, allowing you to create a db object with a schema that recognizes table names and attributes automatically.
import 'dotenv/config'
import { createKysely } from '@vercel/postgres-kysely'
import { DB } from 'kysely-codegen'
export const db = createKysely<DB>()
export { sql } from 'kysely'
The DB is generated directly from the PostgreSQL schema and stored in the kysely-codegen node_modules folder. Here is a brief snippet of how it looks:
export interface MyTable {
id: string
foo: string
bar: boolean
}
export interface DB {
my_table: MyTable
}
My query revolves around the select function, which requires an array of keys from MyTable when querying my_table.
const record = await db
.selectFrom('my_table')
.select(['foo', 'id'])
.executeTakeFirst()
While this works well, I encounter an issue when attempting the following:
// simulate not knowing what the input is
// as if you were making a browser JSON API request
const json = JSON.parse(fs.readFileSync('test.json'))
const selectKeys = Object.keys(json)
const record = await db
.selectFrom('my_table')
.select(selectKeys)
.executeTakeFirst()
This results in an error:
Argument of type 'string[]' is not assignable to parameter of type 'SelectArg<DB, "my_table", SelectExpression<DB, "my_table">>'.
To fix this, I modify the code as follows:
const record = await db
.selectFrom('my_table')
.select(selectKeys as Array<keyof MyTable>)
.executeTakeFirst()
To ensure the selectKeys array aligns with the keys of my_table, I have created a function:
const MY_TABLE_KEYS: Array<keyof MyTable> = ['id', 'foo', 'bar']
function getKeys(json) {
const keys = []
for (const key in json) {
if (MY_TABLE_KEYS.includes(key)) {
keys.push(key)
}
}
return keys
}
While this approach works, there are some challenges with the getKeys function. I have provided a TypeScript playground here to highlight the challenges faced.
My main query is on how to properly type the keys to be passed to the .select method.