say we have an expiration date on a verification email table using astro:db, we were relying on its default to correctly update meaning whenever we inserted a row and we did Date.now() + IN_HOUR we thought that would be an hour from now
but in prod we found that we actually are not getting that but a constant date, why is that?
#astro db Date column default
meager lichen
latent nymphBOT
Hi @meager lichen. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
The issue you're experiencing is due to how default values for date columns are handled in Astro DB. When you set a default value for a date column in your table schema, such as using JavaScript's Date.now() or a similar expression, that value is evaluated once—at the time the schema is generated or the migration is run—not each time a new row is inserted.
In Astro DB, to set a dynamic default (like "one hour from now" at the time of insertion), you cannot rely on the schema's default property for the column. The default property is static and does not re-evaluate per row insert. If you set a default like Date.now() + IN_HOUR in your schema, every inserted row will get the same timestamp—the one from when the schema was created or last pushed—not the current time at insertion.
To achieve "one hour from now" for each inserted row, you need to calculate the expiration date in your application code at the time you insert the row, not in the schema. For example:
const expiration = new Date(Date.now() + 60 * 60 * 1000); // 1 hour from now
await db.insert(VerificationEmail).values({ ..., expiration });
This ensures each row gets a unique expiration date based on the actual insertion time.
Summary:
Astro DB's column default values are static and set at schema/migration time, not dynamically at row insertion. For per-row dynamic defaults (like "one hour from now"), calculate the value in your application code before inserting the row, rather than relying on the schema default.
You can find more about column defaults and their behavior in the Astro DB table configuration reference.
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
meager lichen
@latent nymph we removed the default from expiration date now and are tr ying to remote push it to the turso pnpm astro db push --remote but we are getting SQLITE_CONSTRAINT: SQLite error: FOREIGN KEY constraint failed when the only thing we changed has been removing the default from expiration date
latent nymphBOT
Let me check the knowledge base for you again. One moment! :books:
The error SQLITE_CONSTRAINT: SQLite error: FOREIGN KEY constraint failed during pnpm astro db push --remote is not directly related to removing the default from the expiration date column. Instead, this error indicates that there is a problem with your database's foreign key relationships—specifically, some rows in your table reference a key in another table that does not exist, or the schema change is causing a violation of an existing foreign key constraint.
Key points from the knowledge sources:
- When you push schema changes with
astro db push --remote, Astro DB checks for data loss and schema conflicts, and will report errors if constraints (like foreign keys) are violated by the change or by existing data in the database Astro DB: Pushing changes to your database. - The
isDbError()utility can help you identify and handle database errors, including foreign key constraint violations, in your application code Astro DB utility reference.
Why is this happening?
- Removing a default from a column should not, by itself, cause a foreign key constraint error.
- However, if your schema or seed data relies on the default value to create valid foreign key references, removing it could result in rows being inserted or updated with
NULLor missing values, which may violate foreign key constraints. - Alternatively, the error may be unrelated to the
expirationcolumn change and instead be due to existing data in your database that does not satisfy the foreign key constraints.
What can you do?
- Review your table definitions and ensure that all foreign key relationships are valid and that all referenced keys exist in the related tables.
- Check your seed data or any migration scripts to ensure they are not inserting rows with missing or invalid foreign key references.
- If you are certain your production data is not needed, you can use the
--force-resetflag to reset the database, but this will destroy all existing data Astro DB: Pushing breaking schema changes.
If you need to debug further, you can use the astro db verify command to check for differences and issues between your local and remote database configurations Astro DB CLI reference.
Summary:
The error is due to a foreign key constraint violation, not the removal of the default value itself. Check your data and schema for foreign key issues, especially in relation to any changes in how rows are inserted or updated after removing the default value. If you need to reset the database, use the --force-reset flag, but be aware this will delete all data.
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
meager lichen
@latent nymph so if default's column date acts like this, we should also pass on createdAt everytime we insert something because if not it is going to be whenever the schema was created?
latent nymphBOT
Let me check the knowledge base for you again. One moment! :books:
If you define a date column in Astro DB with a static default value (such as a specific timestamp), that value will be used for every new row, not the current time at insertion. However, Astro DB provides a special value, NOW, which you can use as the default for a date column. When you use NOW, the current timestamp will be set automatically at the time of insertion, not at schema creation.
Example from the documentation:
import { defineTable, column, NOW } from 'astro:db';
const Comment = defineTable({
columns: {
id: column.number({ primaryKey: true }),
author: column.text(),
content: column.text({ optional: true }),
published: column.date({ default: NOW }),
},
});
In this example, the published column will be set to the current date and time whenever a new row is inserted, thanks to the NOW default. You do not need to manually pass createdAt (or published) every time you insert a row if you use NOW as the default. If you do not set a default or use a static value, then you would need to provide the value yourself on every insert, or else it will be NULL (if optional) or cause an error (if required) Table configuration reference.
Summary:
- Use
NOWas the default for date columns to automatically set the current time at insertion. - If you do not use
NOWor do not set a default, you must provide the value yourself on every insert.
If you have not set NOW as the default, then yes, you should pass createdAt (or your date column) every time you insert a row.
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
meager lichen
@latent nymph could we use NOW for expiration date if we need to manipulate NOW with some other time like NOW + IN_HOUR ?
latent nymphBOT
Let me check the knowledge base for you again. One moment! :books:
Based on the knowledge sources, you can use NOW as the default for a date column to set the current timestamp at insertion time. However, the documentation does not mention support for expressions like NOW + INTERVAL or NOW + IN_HOUR directly in the column default configuration. The examples only show using NOW as a default, not arithmetic or interval manipulation Table configuration reference.
Therefore, if you need an expiration date that is, for example, one hour from now, you should calculate that value in your application code at the time of insertion, rather than relying on the schema default.
Summary:
You cannot use NOW + IN_HOUR or similar expressions as a column default in Astro DB according to the current documentation. You should compute the expiration date in your application code before inserting the row.