How is DateTime automatically handled when saving and reading from a database

[vinaysshenoy]

Context: I am building a backend application. This is what my stack looks like:

• Language/Platform: Typescript/NodeJS
• Framework: Express (https://expressjs.com/)
• Database: PostgreSQL (https://www.postgresql.org/docs/14/index.html)

Disclaimer: I am not very familiar with the inner workings of JS/TS. I have a working knowledge of the languauge(s).

I currently use this library called pg-promise to connect to my PostgreSQL instance for querying my database. I noticed something interesting when I save and load DateTime types into my database.

This is the SQL statement I used to create the database:

CREATE TABLE "PROJECT_TEMPLATES" (
    "id" UUID,
    "name" TEXT NOT NULL,
    "url" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL,
    "updated_at" TIMESTAMPTZ NOT NULL,
    PRIMARY KEY ("id")
);

And the code snippet I use to save and query this table:

saveProjectTemplate = (project) => {
    const query = `INSERT INTO "PROJECT_TEMPLATES" AS pt
    ("id", "name", "url", "created_at", "updated_at")
    VALUES
    ($(id), $(name), $(url), $(timestamps.createdAt), $(timestamps.updatedAt))
    ON CONFLICT ("id") DO UPDATE
    SET "name" = $(name), "url" = $(url), "updated_at" = $(timestamps.updatedAt)
    WHERE pt."id" = $(id)`;

    return obj.none(query, project);
}

findProjectTemplate = (id) => {
    const query = `SELECT * FROM "PROJECT_TEMPLATES"
    WHERE "id" = $1`;

    return obj.oneOrNone(query, [id]);
}

Here, project is a ProjectTemplate class:

class ProjectTemplate {
    public readonly id: string
    public readonly name: string
    public readonly url: string
    public readonly timestamps: Timestamps

    constructor(id: string, name: string, url: string, timestamps: Timestamps) {
        this.id = id
        this.name = name
        this.url = url
        this.timestamps = timestamps
    }
}

class Timestamps {
    public readonly createdAt: DateTime
    public readonly updatedAt: DateTime

    constructor(createdAt: DateTime, updatedAt: DateTime) {
        this.createdAt = createdAt
        this.updatedAt = updatedAt
    }
}

And the query returns a type of ProjectTemplateRecord which is transformed after querying:

interface ProjectTemplateRecord {
    id: string
    name: string
    url: string
    created_at: DateTime
    updated_at: DateTime
}

I noticed that the DateTime instances get handled automatically and correctly whenever I save and read from the database without the need for me to manually use the toISO and fromISO methods. How does this work? I just need to know this so I don't get surprised by some behaviour that I am not aware of.

Thanks!

[icambron]

The toISO part is easy: ISO is also the toString() for a DateTime, and that's likely getting called somewhere on the update/insert/query path.

What's more surprising is the fromISO on the hydration side. For example, DateTime doesn't accept ISO strings in its constructor, so it isn't a constructor injection. Luxon does have a fromSQL method, but I'd be surprised if that were being used automagically. So...beats me

[vinaysshenoy]

Hmm, alright. I looked at the source code for DateTime and that has a deprecated fromString method. Do you think this is getting called? Is there a way to verify this?

[vinaysshenoy]

I tried manually converting using the fromSQL method to verify this while parsing and it failed because it wasn't an acceptable format.

So the only way this would work would be if the pg-promise author explicitly added support for the Luxon types in the library which seems unlikely.

[icambron]

fromString is an old alias for fromFormat, so it couldn't be that.

[vinaysshenoy]

Hmm, ok. Thanks for the info. I will crosspost this on the pg-promise repo and see if I can get more clarity!

[vinaysshenoy]

Crossposted vitaly-t/pg-promise#858.

pg-promise delegates to https://github.com/brianc/node-pg-types for type parsing, which in turn delegates to https://github.com/bendrucker/postgres-date for parsing date/time strings. Interestingly, this converts timestamps to JS Date objects and has no notion of any other types.

I am assuming that it would have failed if I had tried to access methods which are accessible only on DateTime. They have support for manually setting a parser for a specific postgres type so I overrode it to explicitly parse DateTime instances.

[vinaysshenoy]

Answer in this comment: #1345 (reply in thread)