Functions & Defaults

Functions are a good way to add some logic to your database. SQLite comes with a lot of core functions that you can learn more about here. Functions can be used when querying to manipulate dates, numbers and a bunch of other things, you can also use functions as part of your default values when creating tables.

Write your own defaults

Defaults are values that are automatically assigned to a column when a new row is created and no value was interested for that row. You can use a literal value that's fixed or an expression as a default value for dynamic values generated on insert.

You can write a literal value or a expression as a default value.

• Literal value is any value after the DEFAULT keyword.

• Expression has to be enclosed (expression).

Frequently used functions for defaults

We have implemented some custom function for defaults based on the most common use cases. You can access these default functions in the dashboard when creating or editing a table columns.

UUID v4

SQLite doesn't have a built-in function to generate UUIDs, inspired by PostgreSQL, we included a custom function using the built in core functions of SQLite. The following expressions generates a UUID v4 and can used as a default value for a column. This is already available in the dashboard as a default function so you can use it directly without having to write the expression.

Reference the expression from here

Unfortunately unlike in PostgreSQL, SQLite doesn't have a uuid type which doesn't validate the text is actually a UUID. While you are able to store UUIDs in a TEXT column with a unique constraint, you could still add any value that's not a valid UUID and SQLite will accept it.

Time

You could also use the built in default key word CURRENT_TIMESTAMP to get the current datetime but we decided to use the datetime() function directly as it's something you can adjust to your needs. The datetime function also allows for subseconds, following the ISO 8601 format.

Current datetime

Current date

Current time

Unix timestamp

If there any common expressions you would like to see added to the dashboard, please let us know.