Drizzle Team•5mo ago

defaultFn when using magic sql is not applying

When using "magic sql" and Inserting a row, the id isn't generated automatically. It only seem to work for me when calling the default Function manually

sql`INSERT INTO ${reminder} VALUES (${reminder.id.defaultFn()}, ${userId}, ${text}, ${dueDate})`

sql`INSERT INTO ${reminder} VALUES (${reminder.id.defaultFn()}, ${userId}, ${text}, ${dueDate})`

Is this an intended behaviour or am i missing something?

7 Replies

francis•5mo ago

not sure what sql flavor you are using but at least for postgres, you have to explicitly list the columns you are inserting into if you want to skip some and have it filled by default: "The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right."

MoschOP•5mo ago

I am using postgres. The query:

  sql`INSERT INTO "reminder" ("userId", "text", "dueDate") VALUES (${userId}, ${text}, ${dueDate})`;

  sql`INSERT INTO "reminder" ("userId", "text", "dueDate") VALUES (${userId}, ${text}, ${dueDate})`;

fails because it violates the not null constraint of id. That why i think the default function isnt working when using magic sql Its also working when using the normal query builder

francis•5mo ago

oh, do you not have a default defined at the sql level that would be why check your table DDL to see if you have a default defined

MoschOP•5mo ago

That part of the drizzle schema is doing exactly this right? For other inserting ways its also working. Only Magic SQL seems to not work

id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),

id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),

Darren•5mo ago

Because your dB has no idea what that function is. Try

sql gen_random_uuid()

sql gen_random_uuid()

sql uuid_generate_v4()

sql uuid_generate_v4()

if you have uuid-ossp extension installed

MoschOP•5mo ago

oh makes sense. So $defaultFn seems to be useless in that case thanks for your help

Darren•5mo ago

inside a magic SQL yes, since its jsut sending whats there to the db, but on your defintition you could have

id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()

id: uuid("id")
      .default(sql`uuid_generate_v4()`)
      .primaryKey()

and then it wouldnt matter

Gaming

Programming

defaultFn when using magic sql is not applying