Jim Posts - Answer Overflow

Jim

Explore posts from servers

CDCloudflare Developers RRailway TTCTheo's Typesafe Cult DTDrizzle Team

•Created by Jim on 3/19/2024 in #help

Defining websocket for NeonDB connection

Hey all, I'm having issues with using neondb on a playwright/test package in my monorepo. The connections work fine in my nextjs app but fail when running tests.

Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless/blob/main/CONFIG.md#websocketconstructor-typeof-websocket--undefined. Details: fetch failed

Error: All attempts to open a WebSocket to connect to the database failed. Please refer to https://github.com/neondatabase/serverless/blob/main/CONFIG.md#websocketconstructor-typeof-websocket--undefined. Details: fetch failed

The error recommends defining a websocket constructor to the config of the neon client like so:

import { neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;

import { neonConfig } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws;

But currently I'm using the Pool as recommended by drizzle like so:

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';
import * as schema from "./schema";
import dotenv from "dotenv";
dotenv.config({ path: ".env.local" });

const pool = new Pool({ connectionString: process.env.DB_URL });
export const db = drizzle(pool, { schema })

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';
import * as schema from "./schema";
import dotenv from "dotenv";
dotenv.config({ path: ".env.local" });

const pool = new Pool({ connectionString: process.env.DB_URL });
export const db = drizzle(pool, { schema })

Pool doesn't accept a webSoctetConstructor, so I'm pretty stuck now lol. Any ideas? Thanks for reading!

4 replies

DTDrizzle Team

•Created by Jim on 12/3/2023 in #help

avoiding conflicts in mySQL

Hi im trying to avoid conflicts in my app by checking the 'lastModified' column on the database against the client. But I also need to add successfully updated records to a status log table to record the history of changes. Because I'm using mySQL there's no way of returning specific rows effected. Is there a common approach for this?

export async function updateStatusByIds({
  status,
  records,
}: {
  status: Statuses;
  records: SelectRecordWithUserData[];
}) {

  // assemble sql where clause to match id and current lastModified date
  const sqlWhereChunks: SQL[] = [];

  records.forEach((r, i) => {
    sqlWhereChunks.push(
      sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
    );
    if (i < records.length - 1) {
      sqlWhereChunks.push(sql` OR `);
    }
  });

  const sqlWhere = sql.join(sqlWhereChunks);

  try {
    await db.transaction(async (tx) => {
      let update = await tx
        .update(recordTable)
        .set({ statusId: status })
        .where(sqlWhere);

      console.log("update:", update);

      await tx.insert(statusLog).values(
        records.map((r) => ({
          recordId: r.id,
          statusId: status,
          message: "bulk",
        }))
      );
    });
  } catch (error) {
    console.error(error);
    throw new Error(
      `Failed to update status ids: ${
        error instanceof Error ? error.message : "Unknown error"
      }`
    );
  }
}

export async function updateStatusByIds({
  status,
  records,
}: {
  status: Statuses;
  records: SelectRecordWithUserData[];
}) {

  // assemble sql where clause to match id and current lastModified date
  const sqlWhereChunks: SQL[] = [];

  records.forEach((r, i) => {
    sqlWhereChunks.push(
      sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
    );
    if (i < records.length - 1) {
      sqlWhereChunks.push(sql` OR `);
    }
  });

  const sqlWhere = sql.join(sqlWhereChunks);

  try {
    await db.transaction(async (tx) => {
      let update = await tx
        .update(recordTable)
        .set({ statusId: status })
        .where(sqlWhere);

      console.log("update:", update);

      await tx.insert(statusLog).values(
        records.map((r) => ({
          recordId: r.id,
          statusId: status,
          message: "bulk",
        }))
      );
    });
  } catch (error) {
    console.error(error);
    throw new Error(
      `Failed to update status ids: ${
        error instanceof Error ? error.message : "Unknown error"
      }`
    );
  }
}

33 replies

DTDrizzle Team

•Created by Jim on 11/20/2023 in #help

json column type mismatch when using db.query

6 replies

DTDrizzle Team

•Created by Jim on 11/7/2023 in #help

enumerating tables

I have a table of possible 'statuses' for a column in my records table and would like my zod schema to see what statuses are available to set. Is this possible? I'm aware we can create an ENUM column on my records, but it 'feels' better to have a separate table for this. I guess I'm looking for an 'in-between' an enum column and a separate table. Maybe it's possible to predefine/lock-in the statuses in the application layer, is it bad practise? I'm new to db stuff so unsure on the best approach.

6 replies

DTDrizzle Team

•Created by Jim on 9/26/2023 in #help

id on transaction conflict

hey all, using mysql (planetscale). when a transaction conflict happens over an insert with the same 'serial' id i believe that mysql will resolve this by changing one of the inserts to the next available. my concern is that within a transaction i want to reference the returned id to use as a reference. will this id match the 'new' id after the conflict has resolved, or will it reference the original? here is some code to better explain maybe:

async function test() {
  await db.transaction(async (tx) => {
    const insertRecord = await tx.insert(record).values({
      foo: "bar",
    });

    await tx
      .insert(user)
      .values({
        foo: "bar",
        recordId: parseInt(insertRecord.insertId)  // is insertId ok on conflict?
       });
  });
}

async function test() {
  await db.transaction(async (tx) => {
    const insertRecord = await tx.insert(record).values({
      foo: "bar",
    });

    await tx
      .insert(user)
      .values({
        foo: "bar",
        recordId: parseInt(insertRecord.insertId)  // is insertId ok on conflict?
       });
  });
}

This is my first time trying to make something production ready so want to make sure all situations are covered, any help is appreciated thank you!

5 replies

DTDrizzle Team

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

Help understanding relations

Hey all, just trying to get my head around relations. In this example we have a one-to-many relationship:

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 120 }),
});

export const blocks = mysqlTable("blocks", {
  id: serial("id").primaryKey(),
  block_type: int("type"),
  user_id: int("user_id"),
});

export const usersRelations = relations(users, ({ many }) => ({
  pagebuilder: many(blocks),
}));

(async () => {
  const user = await db.query.users.findFirst({
    where: eq(users.username, "johnsmith"),
    with: {
      pagebuilder: true,
    },
  });
  // user.pagebuilder[0].block_type ☑️
})();

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 120 }),
});

export const blocks = mysqlTable("blocks", {
  id: serial("id").primaryKey(),
  block_type: int("type"),
  user_id: int("user_id"),
});

export const usersRelations = relations(users, ({ many }) => ({
  pagebuilder: many(blocks),
}));

(async () => {
  const user = await db.query.users.findFirst({
    where: eq(users.username, "johnsmith"),
    with: {
      pagebuilder: true,
    },
  });
  // user.pagebuilder[0].block_type ☑️
})();

How is the query 'joining' the tables when I havent defined which columns are related. Or in this case do 'all' block rows get attached?

3 replies

DTDrizzle Team

•Created by Jim on 9/4/2023 in #help

Type error inserting 'new Date()' into 'time' type schema.

Driving me up the wall this a bit. my schema

import { mysqlTable, serial, varchar, float, time } from 'drizzle-orm/mysql-core';
 
export const metalPrices = mysqlTable('metals', {
  id: serial('id'),
  metal: varchar('metal',  { length: 5 }).primaryKey(),
  value: float('value'),
  time: time('time'),
});

import { mysqlTable, serial, varchar, float, time } from 'drizzle-orm/mysql-core';
 
export const metalPrices = mysqlTable('metals', {
  id: serial('id'),
  metal: varchar('metal',  { length: 5 }).primaryKey(),
  value: float('value'),
  time: time('time'),
});

my insert

    await db.insert(metalPrices).values({
      metal: "FOO",
      time: Date.now(), // type error when adding this line
    });

    await db.insert(metalPrices).values({
      metal: "FOO",
      time: Date.now(), // type error when adding this line
    });

type error:

The expected type comes from property 'time' which is declared here on type '{ metal: string | SQL<unknown> | Placeholder<string, any>; value?: number | SQL<unknown> | Placeholder<string, any> | null | undefined; id?: number | SQL<unknown> | Placeholder<...> | undefined; time?: string | ... 3 more ... | undefined; }'

The expected type comes from property 'time' which is declared here on type '{ metal: string | SQL<unknown> | Placeholder<string, any>; value?: number | SQL<unknown> | Placeholder<string, any> | null | undefined; id?: number | SQL<unknown> | Placeholder<...> | undefined; time?: string | ... 3 more ... | undefined; }'

🫠 Any ideas?

11 replies

DTDrizzle Team

•Created by Jim on 6/26/2023 in #help

TypeError when adding subquery to `drizzle.insert.values`

Hey all, I'm a bit new to drizzle so not sure if this is even possible or if I'm just doing something wrong. I'm getting type errors when using a subquery to select for companyId based on a company name:

    return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: db  // using subquery
      .select({ id: company.id })
      .from(company)
      .where(eq(
        company.name,
        userCompany
        )),
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

    return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: db  // using subquery
      .select({ id: company.id })
      .from(company)
      .where(eq(
        company.name,
        userCompany
        )),
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

but this checks out ok:

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: BigInt(2),  // hardcoded
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

  return await db.insert(widgetItem).values({
    name: "New Widget",
    companyId: BigInt(2),  // hardcoded
    typeId: widgetTypeId,
    slug: generateSlug(),
  });

wondering if this is possible or will I have to send multiple queries? Cheers

6 replies

Gaming

Programming