DT
Drizzle Team•3w ago
Father Christmas

Non hardcoded strings in sql()

This: 
import { sql } from 'drizzle-orm';
import { check, int, sqliteTable, text } from 'drizzle-orm/sqlite-core';


const SOME_KIND_ENUM  = ["win", "sbst", "tbst", "chop"] as const;

export const someTable = sqliteTable("some_table", {
  id: int().primaryKey({ autoIncrement: true }),
  kind: text({ enum: SOME_KIND_ENUM }).notNull(),
}, (table) => [
  check(
            "is_valid",
      sql`${table.kind} IN ('${SOME_KIND_ENUM.join(`', '`)}')`
        ),
]);
import { sql } from 'drizzle-orm';
import { check, int, sqliteTable, text } from 'drizzle-orm/sqlite-core';


const SOME_KIND_ENUM  = ["win", "sbst", "tbst", "chop"] as const;

export const someTable = sqliteTable("some_table", {
  id: int().primaryKey({ autoIncrement: true }),
  kind: text({ enum: SOME_KIND_ENUM }).notNull(),
}, (table) => [
  check(
            "is_valid",
      sql`${table.kind} IN ('${SOME_KIND_ENUM.join(`', '`)}')`
        ),
]);
produces: 
CREATE TABLE `some_table` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `kind` text NOT NULL,
    CONSTRAINT "is_valid" CHECK("some_table"."kind" IN ('?'))
);
CREATE TABLE `some_table` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `kind` text NOT NULL,
    CONSTRAINT "is_valid" CHECK("some_table"."kind" IN ('?'))
);
I would really prefer to have one source of truth for this enum. This way I have one place from where I can drive the type and all the other stuff.
4 Replies
oke
oke•3w ago
Your code looks okay. What is the issue here ?
Father Christmas
Father ChristmasOP•3w ago
Well i'd like the CHECK to actually check for the values not for ?. I'd like the resulting CHECK to be CONSTRAINT "is_valid" CHECK("some_table"."kind" IN ('win', 'sbst', 'tbst', 'chop')) - without having to hardcode those string values which is what I am forced to do now. It makes maintaining and keeping things in sync more ddifficult and error prone instead of having one single source of truth.
oke
oke•2w ago
Ah I see the problem This is also a new discovery for me too. I realized you would need to use sql.raw to actually transfer your JS strings directly into the SQL statement If not, the JavaScript injection seems to be treated as parameterized statements/placeholder-things, which are not appropriate in a CHECK statement So your solution is 
sql`${table.kind} IN ('${sql.raw(SOME_KIND_ENUM.join(`', '`))}')`
sql`${table.kind} IN ('${sql.raw(SOME_KIND_ENUM.join(`', '`))}')`
Father Christmas
Father ChristmasOP•5d ago
oh, thanks a lot man!

Did you find this page helpful?