DiamondDragon Posts - Answer Overflow

DiamondDragon

Explore posts from servers

CDCloudflare Developers TtRPC TTCTheo's Typesafe Cult HHono DTDrizzle Team

Posts Comments

DTDrizzle Team

•Created by DiamondDragon on 9/6/2024 in #help

Drizzle Zod refining not working?

Shouldn't this code for zod make an optional field firstName required? https://drizzle.run/t2zowcjw5f1xbb5ny7zq2j3n

3 replies

DTDrizzle Team

•Created by DiamondDragon on 8/31/2024 in #help

Dynamic function get use typed column names using getTableConfig

I have both UUIDs and NanoIDs on every one of my tables. I am considering adding utils to map from uuid -> nanoID & vice versa. how would i make a dynamic function like this? basically under the assumption every table has a id and shortId column which is the nanoid

export async function getUuidFromShortId(table: PgTable, shortId: string) {
    const { columns, name, schema } = getTableConfig(table)
    const [result] = await db
        .select({ id: columns.id })
        .from(name)
        .where(eq(columns.shortId, shortId))
        .limit(1)
    return result[0]?.id ?? null
}

export async function getUuidFromShortId(table: PgTable, shortId: string) {
    const { columns, name, schema } = getTableConfig(table)
    const [result] = await db
        .select({ id: columns.id })
        .from(name)
        .where(eq(columns.shortId, shortId))
        .limit(1)
    return result[0]?.id ?? null
}

1 replies

DTDrizzle Team

•Created by DiamondDragon on 6/20/2024 in #help

Use results of a column to pass where conditions elsewhere in the query?

I have a field that stores filters that I parse into the SQL conditions for a where clause. Or at least, that's the goal. That field is in my views table. Is it possible to pass the result of that field in views into a nested where condition?

export async function getTableData(opts: { db: Db; tableShortId: string; viewShortId: string }) {

    const { db, tableShortId, viewShortId } = opts

    const tableData = await db.query.views.findFirst({
        where: and(eq(views.shortId, viewShortId), eq(views.tableId, tableShortId)),
        columns: {
            shortId: true,
        },
        with: {
            // Get the view for the table
            table: {
                columns: { shortId: true },
                with: {
                    fields: {
                        columns: { shortId: true },
                    },
                    records: {
                                                //! is it possible to filter records by view.jsonColumn after being parsed
                        columns: { shortId: true },
                        with: {

export async function getTableData(opts: { db: Db; tableShortId: string; viewShortId: string }) {

    const { db, tableShortId, viewShortId } = opts

    const tableData = await db.query.views.findFirst({
        where: and(eq(views.shortId, viewShortId), eq(views.tableId, tableShortId)),
        columns: {
            shortId: true,
        },
        with: {
            // Get the view for the table
            table: {
                columns: { shortId: true },
                with: {
                    fields: {
                        columns: { shortId: true },
                    },
                    records: {
                                                //! is it possible to filter records by view.jsonColumn after being parsed
                        columns: { shortId: true },
                        with: {

1 replies

DTDrizzle Team

•Created by DiamondDragon on 6/10/2024 in #help

construction a dynamic query builder generically ?

Ok i'm not really sure how to ask this but I'm trying to allow a user the ability in UI to build out a query builder where they can add many filters with potentially nested filters with AND/OR conjuctions. Like airtable. Anyone have any patterns how to do this? I had attempted to try to construct a mapping on the SQL operators and the drizzle functions but I can't get the types to work . I'm playing aroung just with the sql operator to try making ti work with more raw SQL. trying to wrap my head around this and its a pattern i've seen few apps implement and never seen any code online to use as a guide Ideally a user can do a query in the UI like below and the queries can be constructed dynamically.

Filter employes where 
  name = john AND
  salary >= 50,000 AND
  relatedDepartment contains 'billing' AND
  ( department_city does not contain NYC OR
    whatever... equals blah
  )

Filter employes where 
  name = john AND
  salary >= 50,000 AND
  relatedDepartment contains 'billing' AND
  ( department_city does not contain NYC OR
    whatever... equals blah
  )

const operators: Record<OperatorNames, Operator> = {
    contains: {
        label: 'Contains',
        name: 'contains',
        sql: 'LIKE',
        drizzle: (column: Column, value: string | SQLWrapper): typeof like => {
            return like(column, value)
        },
    },
        // other operators...
}

const operators: Record<OperatorNames, Operator> = {
    contains: {
        label: 'Contains',
        name: 'contains',
        sql: 'LIKE',
        drizzle: (column: Column, value: string | SQLWrapper): typeof like => {
            return like(column, value)
        },
    },
        // other operators...
}

1 replies

DTDrizzle Team

•Created by DiamondDragon on 5/19/2024 in #help

Losing custom $types in pg jsonb using drizzle-zod

Not sure if this is an expected limitation of zod here

export const records = pgTable('records', {
    id: uuid('id')
        .primaryKey()
        .$defaultFn(() => uuidv7()),
    tableId: uuid('table_id')
        .references(() => tables.id, { onDelete: 'cascade' })
        .notNull(),
    organizationId: uuid('organization_id')
        .references(() => organizations.id, { onDelete: 'cascade' })
        .notNull(),
    recordByFieldId: jsonb('record_by_field_id').$type<RecordData>(),

})

export const records = pgTable('records', {
    id: uuid('id')
        .primaryKey()
        .$defaultFn(() => uuidv7()),
    tableId: uuid('table_id')
        .references(() => tables.id, { onDelete: 'cascade' })
        .notNull(),
    organizationId: uuid('organization_id')
        .references(() => organizations.id, { onDelete: 'cascade' })
        .notNull(),
    recordByFieldId: jsonb('record_by_field_id').$type<RecordData>(),

})

produces using $inferInsert

type Record = {
    organizationId: string;
    tableId: string;
    id?: string;
    recordByFieldId?: RecordData | null;
    createdAt?: Date;
    updatedAt?: Date | null;
}

type Record = {
    organizationId: string;
    tableId: string;
    id?: string;
    recordByFieldId?: RecordData | null;
    createdAt?: Date;
    updatedAt?: Date | null;
}

But when using drizzle zod, i lose RecordData

export const insertRecordSchema = createInsertSchema(records)
export type InsertRecord = z.infer<typeof insertRecordSchema>

// results in
type InsertRecord = {
    organizationId: string;
    tableId: string;
    id?: string;
    recordByFieldId?: any;
    createdAt?: Date;
    updatedAt?: Date | null;
}

export const insertRecordSchema = createInsertSchema(records)
export type InsertRecord = z.infer<typeof insertRecordSchema>

// results in
type InsertRecord = {
    organizationId: string;
    tableId: string;
    id?: string;
    recordByFieldId?: any;
    createdAt?: Date;
    updatedAt?: Date | null;
}

1 replies

DTDrizzle Team

•Created by DiamondDragon on 4/17/2024 in #help

Generated SQL error / type error with operations on unique index

1 replies

DTDrizzle Team

•Created by DiamondDragon on 4/16/2024 in #help

Drizzle studio shows as blank page on Arc browser

1 replies

DTDrizzle Team

•Created by DiamondDragon on 4/7/2024 in #help

Missing nested where clause

23 replies

DTDrizzle Team

•Created by DiamondDragon on 3/21/2024 in #help

bun:sqlite - why is my sqlite3 version mismatched?

I'm trying to better understand JSONB functionality in sqlite so i replaced the 3.43 version on my mac by default with 3.45 from homebrew. but noticed some version mismatch the code I'm running using the bun:sqlite driver is console.log(db.all(sqlSELECT sqlite_version())) which returns 3.43 but as you can see below, in my terminal i have 3.45 installed. But i can't figure out why it's not working

❯ which sqlite3
/opt/homebrew/opt/sqlite/bin/sqlite3

❯ sqlite3 --version
3.45.2 2024-03-12 11:06:23 d8cd6d49b46a395b13955387d05e9e1a2a47e54fb99f3c9b59835bbefad6af77 (64-bit)
❯ bun run src/scripts/save-jsonb.ts
[
  {
    version: "3.43.2",
  }
]

❯ which sqlite3
/opt/homebrew/opt/sqlite/bin/sqlite3

❯ sqlite3 --version
3.45.2 2024-03-12 11:06:23 d8cd6d49b46a395b13955387d05e9e1a2a47e54fb99f3c9b59835bbefad6af77 (64-bit)
❯ bun run src/scripts/save-jsonb.ts
[
  {
    version: "3.43.2",
  }
]

❯ sqlite3 src/db/db.db
SQLite version 3.45.2 2024-03-12 11:06:23
Enter ".help" for usage hints.
sqlite>

❯ sqlite3 src/db/db.db
SQLite version 3.45.2 2024-03-12 11:06:23
Enter ".help" for usage hints.
sqlite>

when i go into the db from my project in vscode

1 replies

DTDrizzle Team

•Created by DiamondDragon on 12/2/2023 in #help

createInsertSchema does not infer notNull() correclty?

"drizzle-orm": "^0.28.6", "drizzle-zod": "^0.5.1",

export const testTable = pgTable('accounts', {
    id: varchar('id').primaryKey(),
    optional: varchar('optional'),
    notOptional: varchar('not_optional').notNull(),
    unique: varchar('unique').unique().notNull(),
})

const testSchema = createInsertSchema(testTable)
type TestTypeWrong = z.infer<typeof testSchema>
type TestTypeCorrect = typeof testTable.$inferInsert

export const testTable = pgTable('accounts', {
    id: varchar('id').primaryKey(),
    optional: varchar('optional'),
    notOptional: varchar('not_optional').notNull(),
    unique: varchar('unique').unique().notNull(),
})

const testSchema = createInsertSchema(testTable)
type TestTypeWrong = z.infer<typeof testSchema>
type TestTypeCorrect = typeof testTable.$inferInsert

Results

type TestTypeWrong = {
    id?: string;
    optional?: string;
    notOptional?: string;
    unique?: string;
}
type TestTypeCorrect = {
    id: string;
    notOptional: string;
    unique: string;
    optional?: string;
}

type TestTypeWrong = {
    id?: string;
    optional?: string;
    notOptional?: string;
    unique?: string;
}
type TestTypeCorrect = {
    id: string;
    notOptional: string;
    unique: string;
    optional?: string;
}

2 replies

DTDrizzle Team

•Created by DiamondDragon on 9/27/2023 in #help

Help with proper types (values)

2 replies

DTDrizzle Team

•Created by DiamondDragon on 9/23/2023 in #help

TypeError: client.unsafe is not a function

7 replies

DTDrizzle Team

•Created by DiamondDragon on 9/7/2023 in #help

No transactions support in neon-http driver even though neon provides a transaction function

https://neon.tech/docs/serverless/serverless-driver#when-to-use-the-neon-function-vs-pool-or-client I tried to execute a db operation such as below. I am getting throw new Error('No transactions support in neon-http driver'); I'm not sure if the kind of code below is an "interactive" or "non-interactive" transaction, but wondering if maybe I am missing something. Does drizzle support the neon(...) transaction() function?

await db.transaction(async tx => {
        // Insert New Contact
        const insertedContact = await tx
            .insert(contacts)
            .values(newContact)
            .returning()
        if (newEmails) {
            // Take in array of email objects
            for (const email of newEmails) {
                // Insert or update New Email
                const insertedEmail = await tx
                    .insert(emails)
                    .values(email)
                    .onConflictDoUpdate({ target: emails.emailId, set: email })
                    .returning({ id: emails.emailId })
                // For each email, add relationship to join table
                await tx.insert(emailsToContacts).values({
                    contactId: insertedContact[0].contactId,
                    emailId: insertedEmail[0].id,
                })
            }
        }

await db.transaction(async tx => {
        // Insert New Contact
        const insertedContact = await tx
            .insert(contacts)
            .values(newContact)
            .returning()
        if (newEmails) {
            // Take in array of email objects
            for (const email of newEmails) {
                // Insert or update New Email
                const insertedEmail = await tx
                    .insert(emails)
                    .values(email)
                    .onConflictDoUpdate({ target: emails.emailId, set: email })
                    .returning({ id: emails.emailId })
                // For each email, add relationship to join table
                await tx.insert(emailsToContacts).values({
                    contactId: insertedContact[0].contactId,
                    emailId: insertedEmail[0].id,
                })
            }
        }

20 replies

DTDrizzle Team

•Created by DiamondDragon on 8/28/2023 in #help

Column Unique name appears to be incorrect when spreading common fields to multiple tables

25 replies

DTDrizzle Team

•Created by DiamondDragon on 8/23/2023 in #help

Check Constraint not working?

3 replies

DTDrizzle Team

•Created by DiamondDragon on 8/22/2023 in #help

Custom type not inferring correct zod schema?

3 replies

DTDrizzle Team

•Created by DiamondDragon on 7/6/2023 in #help

Proper Way to Deal with Migration Failures and Rolling Back?

So I added an Enum and made a few minor changes and generated 3 SQL files. But when running a migration I got the error

node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
                              ^
PostgresError: type "market_segment_enum" does not exist

node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
                              ^
PostgresError: type "market_segment_enum" does not exist

on one of my created enums. It seemed like something bugged out where it wasn't creating the enum in the proper order? Anyway, I had to delete my snapshot json, SQL, and _journal files back to last working migration (#5) vs #8 where i was. If i didn't manually delete the migrations in the array in _jounral i would get throw new Error(No file ${migrationPath} found in ${migrationFolderTo} folder) error. Not sure if there was a proper way of rolling back migrations but hopefully this manual approach helps anyone

4 replies

DTDrizzle Team

•Created by DiamondDragon on 6/30/2023 in #help

Issue running migrations to DB

I am having an issue running migrations to Neon. I checked that everything is exporting const variables and tried add the cjs version of drizzle kit and reinstalling node modules.. but no luck fixing it I also tried to run a migration on a new db and its still giving me the same error. not sure whats going on

v-app git:(drizzle-orm) ✗ pnpm generate

> v-app-v2@0.0.0 generate v-app
> drizzle-kit generate:pg

drizzle-kit: v0.19.2
drizzle-orm: v0.27.0

No config path provided, using default 'drizzle.config.ts'
Reading config file 'v-app/drizzle.config.ts'
36 tables
features 3 columns 0 indexes 0 fks
plans 4 columns 0 indexes 0 fks
plans_features 3 columns 0 indexes 1 fks
teams 2 columns 0 indexes 0 fks
users 9 columns 0 indexes 0 fks
...
views 11 columns 0 indexes 3 fks

6 enums
plans_enum [free, basic, pro, enterprise]
...
view_type [dynamic, static]

[✓] Your SQL migration file ➜ src/db/migrations/0010_material_lilandra.sql 🚀
➜  v-app git:(drizzle-orm) ✗ pnpm migrate 

> v-app-v2@0.0.0 migrate 
> tsx -r dotenv/config -r tsconfig-paths/register src/db/index.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
                              ^
PostgresError: type "plans" already exists

v-app git:(drizzle-orm) ✗ pnpm generate

> v-app-v2@0.0.0 generate v-app
> drizzle-kit generate:pg

drizzle-kit: v0.19.2
drizzle-orm: v0.27.0

No config path provided, using default 'drizzle.config.ts'
Reading config file 'v-app/drizzle.config.ts'
36 tables
features 3 columns 0 indexes 0 fks
plans 4 columns 0 indexes 0 fks
plans_features 3 columns 0 indexes 1 fks
teams 2 columns 0 indexes 0 fks
users 9 columns 0 indexes 0 fks
...
views 11 columns 0 indexes 3 fks

6 enums
plans_enum [free, basic, pro, enterprise]
...
view_type [dynamic, static]

[✓] Your SQL migration file ➜ src/db/migrations/0010_material_lilandra.sql 🚀
➜  v-app git:(drizzle-orm) ✗ pnpm migrate 

> v-app-v2@0.0.0 migrate 
> tsx -r dotenv/config -r tsconfig-paths/register src/db/index.ts

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
                              ^
PostgresError: type "plans" already exists

3 replies

DTDrizzle Team

•Created by DiamondDragon on 6/29/2023 in #help

Migrations not working with Neon.Tech?

3 replies

Gaming

Programming