search

kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.44k stars 266 forks source link

Add a recipe for automatic data type conversions #624

Closed koskimas closed 9 months ago

koskimas commented 1 year ago

Since Kysely doesn't touch the data types returned by the DB in any way, a common problem is getting a "correct" JS type for things like MySQL "booleans" (tinyint(1)) and Postgres bigints.

By default, MySQL returns a number for tinyint and postgres returns a string for bigint.

Most db drivers provide a way to configure the mapping from DB data types to JS types. We should create a recipe that shows how to do this for each built-in dialect. The recipe should also explain why we don't automatically convert the data types.

jlarmstrongiv commented 10 months ago

@koskimas I saw your example with the SqliteBooleanPlugin, do you have a SQLite example going the other direction?

koskimas commented 9 months ago

Done https://kysely.dev/docs/recipes/data-types

jlarmstrongiv commented 9 months ago

Is there a SQLite example for datatypes? It would be great to convert the 1/0 to true/false

agnauck commented 8 months ago

also looking for some Sqlite docs on this

Bessonov commented 5 months ago

Hi @koskimas! 👋

I find myself in a situation where I'm migrating a Scala/Play Framework/squeryl application to nodejs/kysely. The previous team used tiny(1) and tiny(4) for boolean values. This, along with some JSON aggregates (jsonBuildObject), can be mapped in typeCast.

However, they also employed a mapping between int (in the database) and an enum (in the API) with values like ['available', 'taken'], where 1 corresponds to available and 2 to taken. While I can convert from int to the enum in typeCast for the select clause, this doesn't work if the enum is used in other places like where clauses.

What's the best way to handle this conversion transparently? (Of course, my typings already use the enum values). I've thought about using a Plugin. However, I'm not sure how to reliably get the necessary information. Currently, I'm considering storing processed table names and aliases I can obtain from places like transformFrom. Then, match relevant columns in transformReference and finally map the value in transformValue. However, this is a very stateful approach and is prone to errors on my part.

What's your advice? I tried using a Proxy to observe all method invocations of OperationNodeTransformer, but this doesn't work due to private properties.