How to insert into postgres temporal tables with kyselyjs?

Question

I have a database that has some temporal tables. I have zod schemas for those tables and I am using those zod schemas to define the tables in Kysely. The schemas can be as follows:

CREATE TABLE temporal (
  version_id    SERIAL PRIMARY KEY,
  some_id       TEXT,
  some_data     TEXT,
  valid_from    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  valid_to      TIMESTAMP DEFAULT NULL,
  is_current    BOOLEAN NOT NULL DEFAULT TRUE
);

const TemporalTableSchema = z.object({
  versionId: z.number(),
  someId: z.string(),
  someData: z.string(),
  validFrom: z.coerce.date(),
  validTo: z.coerce.date().optional().nullable(),
  isCurrent: z.boolean()
})

type TemporalTableSchema = z.infer<typeof TemporalTableSchema>

Now if I want to insert into a such table I would never pass the version id, is_current or the timestamps myself (I have a trigger to update the valid_to and is_current fields when needed). So how can I do this with Kysely?

If I create my Kysely instance using the type from the zod schema Kysely is complaining that the insert is missing some required fields. This is what I would like to do somehow:

const db = new Kysely<{ temporal: TemporalTableSchema }>(someConfig)

await db.insertInto('temporal').values({
  someId: someData.id,
  someData: someData.data,
})

however, here Kysely would complain as it sees that for example versionId field is required based on the zod schema. Then on the other hand when querying data I would like to be able to validate and parse the returned data with the given schema and now all those fields should be in the data

// Here I want all the fields including the values from the autogenerated fields
const result = await db.selectFrom('temporal').selectAll()

So is there a way to tell Kysely that when inserting we don't need all the fields from a schema? I wouldn't want to mark all those fields optional as it would be annoying to do all kind of checks when using the data after it is queried from the db.

I wonder is there any other option than to use the raw query which pretty much would void the purpose of using Kysely in the first place — zaplec
– zaplec, Commented Oct 8 at 6:31
From the Kysely .values() method doc: You must provide all fields you haven't explicitly marked as nullable or optional using Generated or ColumnType. I'm not sure if you can just wrap the zod methods version_id: Generated<z.number()> — Zegarek
– Zegarek, Commented Oct 8 at 9:03
@Zegarek I can't wrap the zod schema or shape directly with the Generated as it is a TS type whereas the zod schema is a runtime object with methods. However, one option might be to alter the inferred type with that Generated type where needed. I'll give it a try and come back later to tell whether it worked or not — zaplec
– zaplec, Commented Oct 8 at 9:33
Looks like it is possible to define the database type by utilizing the Generated utility type. I will write an actual answer explaining it — zaplec
– zaplec, Commented Oct 8 at 10:45

Zegarek · Accepted Answer · 2025-10-08 15:48:40Z

From the Kysely .values() method doc:

You must provide all fields you haven't explicitly marked as nullable or optional using Generated or ColumnType.

The type for each table having autogenerated values must be altered and one cannot directly use the type inferred by zod. Instead, the type needs to be modified. In my case all my temporal tables are following the same pattern, so I created a wrapper:

type TemporalTable<
  T extends {
    versionId: number
    validFrom: Date
    validTo: Date | null
    isCurrent: boolean
  },
> = Omit<T, 'versionId' | 'validFrom' | 'validTo' | 'isCurrent'> & {
  versionId: Generated<number>
  validFrom: Generated<Date>
  validTo: Generated<Date | null>
  isCurrent: Generated<boolean>
}

Now the type for each table is wrapped with this:

const TemporalTableSchema = z.object({
  versionId: z.number(),
  someId: z.string(),
  someData: z.string(),
  validFrom: z.coerce.date(),
  validTo: z.coerce.date().optional().nullable(),
  isCurrent: z.boolean()
})

type TemporalTableSchema = TemporalTable<z.infer<typeof TemporalTableSchema>>

When defining the database type to give to Kysely, I need to write it manually:

const MyDatabase = z.object({
  table1: Table1Schema,
  temporalTable: TemporalTableSchema
})

type MyDatabase = {
  table1: z.infer<typeof Table1Schema>,
  temporalTable: TemporalTableSchema,
  // alternatively you can wrap the type of the table into the temporal type wrapper here
  anotherTemporalTable: TemporalTable<z.infer<typeof AnotherTemporalTable>>
}

So basically you need to write the type for the database by hand, wrap the necessary table types with the wrapper. You can't just simply compose the zod object for the database, infer its type and then use that type as the type for your database.

Collectives™ on Stack Overflow

How to insert into postgres temporal tables with kyselyjs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related