Drizzle Team•9mo ago

SQLean's uuid4() in SQLite CREATE TABLE

I'm using Turso, enabled SQLean uuid extension and I would like to generate a table via drizzle-kit with a following id:

id: text("id")
      .primaryKey()
      .default(sql`uuid4()`),

id: text("id")
      .primaryKey()
      .default(sql`uuid4()`),

and the output:

`id` text PRIMARY KEY DEFAULT uuid4() NOT NULL,

`id` text PRIMARY KEY DEFAULT uuid4() NOT NULL,

If I'm not mistaken, this should be possible but throws an error. Is there any workaround?

6 Replies

Sillvva•9mo ago

I believe it should look like this:

`id` text PRIMARY KEY DEFAULT(uuid4()) NOT NULL

`id` text PRIMARY KEY DEFAULT(uuid4()) NOT NULL

Try this:

id: text("id")
      .primaryKey()
      .default(sql`DEFAULT(uuid4())`),

id: text("id")
      .primaryKey()
      .default(sql`DEFAULT(uuid4())`),

PramusOP•9mo ago

Unfortunately this results in a broken syntax (note I mistakenly removed one DEFAULT from my initial output, fixed it):

    `id` text PRIMARY KEY DEFAULT DEFAULT(uuid4()) NOT NULL,

    `id` text PRIMARY KEY DEFAULT DEFAULT(uuid4()) NOT NULL,

AFAIK the syntax for default values is DEFAULT + value rather than DEFAULT(value)

Sillvva•9mo ago

Gotcha. Try this, then:

id: text("id")
      .primaryKey()
      .default(sql`(uuid4())`),

id: text("id")
      .primaryKey()
      .default(sql`(uuid4())`),

I had tested both syntaxes DEFAULT func() and DEFAULT(func()) in a sandbox environment. The first threw a syntax error. https://sqliteonline.com/

SQL Online Compiler - Next gen SQL Editor

SQL OnLine - Next gen SQL Editor: SQLite, MariaDB / MySQL, PostgreSQL, MS SQL Server. User-friendly interface for data science. No registration for start, No DownLoad, No Install. Online test SQL script. Online Open/Save SQLite file. Online view all table DB. Fiddle link SQL text and DB file. SQL Test, SQLite in Browser, Data for World, online s...

PramusOP•9mo ago

I tried that as well. It parses correctly but creating a new row results in the string literal being inserted into a field but I imagine it's a Turso/SQLite issue rather than Drizzle. In fact the same thing happens in the sandbox you shared. If you create a table with such fields:

`id` text PRIMARY KEY DEFAULT (uuid4()) NOT NULL,
     `created_at` text DEFAULT (current_timestamp) NOT NULL

`id` text PRIMARY KEY DEFAULT (uuid4()) NOT NULL,
     `created_at` text DEFAULT (current_timestamp) NOT NULL

and then create a row, you'll see that the "uuid4()" and "current_timestamp" are string literals. Strange...

Sillvva•9mo ago

Yeah, that's odd. Not sure then

PramusOP•9mo ago

I think I might've figured out what is the problem. Not sure if it exists in code but definitely in Drizzle Studio. I created an issue on GitHub: https://github.com/drizzle-team/drizzle-orm/issues/2198

GitHub

[BUG]: SQLite/Turso libsql: Drizzle (Studio) not running functions ...

What version of drizzle-orm are you using? 0.30.9 What version of drizzle-kit are you using? 0.20.17 Describe the Bug For a following table: export const organizations = sqliteTable( "organiza...

Gaming

Programming

SQLean's uuid4() in SQLite CREATE TABLE