DuckDB ULID

This repository is based on https://github.com/duckdb/extension-template, check it out if you want to build and ship your own DuckDB extension.

---

This extension adds a new ULID data type to DuckDB, based on this specification. A ULID is similar to a UUID except that it also contains a timestamp component, which makes it more suitable for use cases where the order of creation is important.

The extension provides the following functions:

• ulid(): Returns a new ULID value based on the current system time.
• ulid_timestamp(ulid): Returns the timestamp component of a ULID value.
• ulid_epoch_ms(ulid): Returns the timestamp component of a ULID value in milliseconds since the Unix epoch.

Additionally, the extension provides cast functions to convert between ULID and the VARCHAR and UHUGEINT types. A pair of ULIDs will always sort the same regardless if it is cast to VARCHAR or UHUGEINT.

You can also cast back to ULID from VARCHAR and UHUGEINT. When casting from VARCHAR the input string is validated to ensure it is a valid ULID. You can use TRY_CAST(str AS ulid) to avoid errors when casting invalid strings.

Internally, ULIDs are represented as UHUGEINT.

Building

To build the extension, run:

make

The main binaries that will be built are:

./build/release/duckdb
./build/release/test/unittest
./build/release/extension/ulid/ulid.duckdb_extension

• duckdb is the binary for the duckdb shell with the extension code automatically loaded.
• unittest is the test runner of duckdb. Again, the extension is already linked into the binary.
• ulid.duckdb_extension is the loadable binary as it would be distributed.

Running the extension

To run the extension code, simply start the shell with ./build/release/duckdb.

Now we can use the features from the extension directly in DuckDB, for example by creating a new ULID using the ulid() scalar function.

D select ulid() as result;
┌────────────────────────────┐
│           result           │
│            ulid            │
├────────────────────────────┤
│ 01J2BD0P4RMKJXQRC4YW2RJ441 │
└────────────────────────────┘

Running the tests

To run the SQL tests in ./test/sql, simpy invoke:

make test