Kysely•6mo ago

Question about ColumnTypes and dates.

I have a simple yet confusing problem. In postgres I have a table with a column of type DATE (yyyy-mm-dd).

CREATE TABLE IF NOT EXISTS person(
    id INT NOT NULL,
    birthday DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS person(
    id INT NOT NULL,
    birthday DATE NOT NULL
);

Kysely's PostgresDialect is using the node-postgres/pg driver, which transforms date columns as javascript Date objects when it returns rows, however, you can use strings and/or Date objects as query parameters, both are valid ways to query the database. This works both in node and when you write raw SQL:

-- both are valid queries
SELECT * FROM person WHERE birthday < '2020-01-01';
SELECT * FROM person WHERE birthday < NOW();

-- both are valid queries
SELECT * FROM person WHERE birthday < '2020-01-01';
SELECT * FROM person WHERE birthday < NOW();

The problem is that I'm not sure how to define my database types to account for that. If I create the following type I will get type error if I use string in my .where clauses:

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date, Date, Date>;
}

// ❌ will give a type error because birthday is a string
await db.selectFrom('person').where('birthday', '=', '2020-01-01');

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date, Date, Date>;
}

// ❌ will give a type error because birthday is a string
await db.selectFrom('person').where('birthday', '=', '2020-01-01');

and, if I change the ColumnType to be a union of Date | string as acceptable values for ColumnType -> SelectType, then the inferred result for birthday in all queries will also be the same union, but we obviously know node-postgres only returns JS Date objects:

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date | string, Date | string, Date | string>;
}

// ❌ res.birthday will be inferred as Date | string instead of just Date
const res = await db.selectFrom('person').selectAll().executeTakeFirstOrThrow();

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date | string, Date | string, Date | string>;
}

// ❌ res.birthday will be inferred as Date | string instead of just Date
const res = await db.selectFrom('person').selectAll().executeTakeFirstOrThrow();

So my question is, is there any way to deal with this problem? Here is a kyse.link as well: https://kyse.link/2CJ6I In a certain way, the SelectType in ColumnType is more like select type (how you can select) and also a return type (how the result is inferred).

Solution:

And yes, the "select type" is currently used as the return type AND the query type. We could add a fourth "query" type to ColumnType which would be used in where statements (and other similar statements). But then subqueries in where statements would break if the select type and the query type don't match....

Jump to solution

3 Replies

koskimas•6mo ago

Easy solution is to always return your dates as strings. Javascript dates suck. Use the pg-types package to transform dates into strings (or rather keep them as strings instead of parsing them to Date instances).

Solution

koskimas•6mo ago

dechoOP•6mo ago

I think node-postgres does this DATE->JS Date transformation out of the box, and yes I'm aware I can tweak it: pg.types.setTypeParser(pg.types.builtins.DATE, v => String(v)); (something like this), but then if i need to work with a Date object in my JS application, I would then need to convert it back once more, it becomes ugly so I was just wondering if there was a workaround within kysely but yeah i can understand if you add a 4th type that can cause some issues, so it's probably more trouble than it's worth I think this answers my question, appreciate the reply as always I will settle for sticking with date objects and do all the transformations in the app itself

Gaming

Programming

Question about ColumnTypes and dates.