KyselyK
Kysely•2y ago
decho

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
);


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();


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');


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();


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
Was this page helpful?