1 reply

Use DB functions to format data on update e.g. DATE()

Is there a way to use database functions like DATE in SQLite to format a value on update to ensure it is formatted correctly? I have a text field where I store a person's date of birth and want it formatted as YYYY-MM-DD (which DATE does) and doing it on the client side with no verification on the server / db side seems error prone. As with schemas in general it is best to constrain data as much as possible and this data should never be in any other form. Is there a way to make it so? I already have a check that it is a valid date with

check(
  "date_of_birth_check",
  sql`(LENGTH(${table.date_of_birth}) <= 10 AND DATE(${table.date_of_birth}, '+0 days') IS ${table.date_of_birth})`,
),

check(
  "date_of_birth_check",
  sql`(LENGTH(${table.date_of_birth}) <= 10 AND DATE(${table.date_of_birth}, '+0 days') IS ${table.date_of_birth})`,
),

but it would be nice if it could use the

$onUpdate

$onUpdate

to format any string value date i pass in or is there some better approach?

Drizzle Team•12mo ago•

1 reply

Father Christmas

Use DB functions to format data on update e.g. DATE()

check(
  "date_of_birth_check",
  sql`(LENGTH(${table.date_of_birth}) <= 10 AND DATE(${table.date_of_birth}, '+0 days') IS ${table.date_of_birth})`,
),

check(
  "date_of_birth_check",
  sql`(LENGTH(${table.date_of_birth}) <= 10 AND DATE(${table.date_of_birth}, '+0 days') IS ${table.date_of_birth})`,
),

but it would be nice if it could use the

$onUpdate

$onUpdate

to format any string value date i pass in or is there some better approach?

Use DB functions to format data on update e.g. DATE()

Similar Threads

Use DB functions to format data on update e.g. DATE()

Similar Threads

Similar Threads

Similar Threads