Inconsistency between db.select and db.sql for date type

[nomocas]

Hello All,

CREATE TABLE public.myTable (
    ...
    date date NOT NULL
    ...
}

INSERT INTO myTable(..., date, ...) VALUES(..., '2023-03-23', ...);

db.select('myTable', {}).run(pool);  
//=> [{ ..., date: '2023-03-23', ... }]

// but

db.sql<myTable.SQL, myTable.JSONSelectable[]>`select * from ${'myTable'};`.run(pool);
// => [{..., date: 2023-03-22T23:00:00.000Z, ...}]

(Note that the second date is a js Date, and not a string - (printed as this because .toString()))

I would really prefer that both return a string (DateString).

Any idea ? :)

Info: "typescript": "^4.9.5", "zapatos": "^6.1.4"

[nomocas]

If we check src/generate/pgTypes.ts from zapatos source: we found:

const baseTsTypeForBasePgType = (pgType: string, enums: EnumData, context: TypeContext) => {
   // ...
    case 'date':
      return context === 'JSONSelectable' ? 'db.DateString' :
        context === 'Selectable' ? 'Date' :
          '(db.DateString | Date)';
   //...
};

And if we check how typescript is seeing the date in:

const results = await db.sql<myTable.SQL, myTable.JSONSelectable[]>`select * from ${'myTable'};`.run(pool);

Typescript sees the date as a DateString, which is correct (because we ask some JSONSelectable). So the static typing is correct.

But at run-time it's a js date (and it should not).

console.log(results[0].date instanceof Date); //=> true
console.log(results[0].date); //=> 2023-03-22T23:00:00.000Z

[jawj]

Using JSONSelectable in the type signature doesn't affect what will be returned. The signature just needs to match what will be returned, and that won't be a DateString (via JSON) because there's nothing in your query to turn the results to JSON.

So, for this query, you'll get back an actual Date, and you need:

const results = await db.sql<myTable.SQL, myTable.Selectable[]>`select * from ${'myTable'};`.run(pool);

[jawj]

If you want a timestamp[tz] back as a DateString from a manual query, you'll probably need to use json_agg like the shortcut functions do. That would be something like:

const results = await db.sql<myTable.SQL, { rows: [{ result: myTable.JSONSelectable }] }>
  `SELECT coalesce(jsonb_agg(t), '[]') AS result FROM ${"myTable"} t;`.run(pool);
const { result } = results.rows[0];

[nomocas]

Hello @jawj,

Thank you for your answers. Clearly I was missing something.

We managed to get what we want with something as :

  ...
  const results = await db.sql<myTable.SQL, [{ result: myTable.JSONSelectable }]>`
  SELECT to_jsonb(${'myTable'}.*) as result
  FROM ...
  `.run(pool);
  return results.map((r) => r.result);

Which also works when results set is empty.

(Btw, thank you for this lib : we love it ;))

Cheers.

[jawj]

OK, great. And good to hear! :)