sevenwestonroads Posts - Answer Overflow

Topics

sevenwestonroads

•Created by sevenwestonroads on 3/6/2024 in #help

How to convert sql.raw`` to camelCase?

I have queries I need to run with sql.raw but I'd like the result to come out neatly just how it comes with the typical Drizzle driver. Is it possible? Or do I have to do this manually? Actually, before relational queries, I did this when using json_agg and had to both convert snake_case to camelCase and handle date strings to date object (as it's how it's formatted in my schema). Many thanks!

1 replies

•Created by sevenwestonroads on 2/11/2024 in #help

Wrong drizzle generate with an array of text() in PSQL

I've been trying to create an array of text() using PSQL for ex. when I add the below column, drizzle generates this "custom_selected_rules" text[] DEFAULT NOT NULL but here's the schema definition

customSelectedRules: text("custom_selected_rules")
      .array()
      .default([])
      .notNull()

customSelectedRules: text("custom_selected_rules")
      .array()
      .default([])
      .notNull()

It should be "custom_selected_rules" text[] DEFAULT '{}'::text[] NOT NULL Why Drizzle doesn't do this by default? Thanks!

3 replies

•Created by sevenwestonroads on 1/21/2024 in #help

Using json_agg does not format correctly

I've been querying my PSQL DB with the following query

const enrichedUser = await client
    .select({
      user: usersTable,
      integrations: sql<Integration[]>`json_agg(integrations.*)`,
      websites: sql<Website[]>`json_agg(websites.*)`,
    })
    .from(usersTable)
    .where(eq(usersTable.id, userId))
    .leftJoin(integrationsTable, eq(integrationsTable.userId, usersTable.id))
    .leftJoin(websitesTable, eq(websitesTable.userId, usersTable.id))
    .orderBy(desc(websitesTable.createdAt))
    .groupBy(usersTable.id, websitesTable.createdAt)
    .execute()
    .then((r) => r[0]);

const enrichedUser = await client
    .select({
      user: usersTable,
      integrations: sql<Integration[]>`json_agg(integrations.*)`,
      websites: sql<Website[]>`json_agg(websites.*)`,
    })
    .from(usersTable)
    .where(eq(usersTable.id, userId))
    .leftJoin(integrationsTable, eq(integrationsTable.userId, usersTable.id))
    .leftJoin(websitesTable, eq(websitesTable.userId, usersTable.id))
    .orderBy(desc(websitesTable.createdAt))
    .groupBy(usersTable.id, websitesTable.createdAt)
    .execute()
    .then((r) => r[0]);

However the array of integrations and websites, as queried with json_agg returns me objects with snake_case and Dates that are formatted as string instead of Date. It looks like Drizzle do this transformation by default on the usersTable by default (ie. when the table is called directly) but not on my json_agg tables which forces me to do this extra formatting step below

    formattedWebsites = formattedWebsites
    .map((w) => snakeCaseToCamelCase<Website>(w))
    .map((w) => convertDatesStringToDate<Website>(w));

  formattedIntegrations = formattedIntegrations
    .map((i) => snakeCaseToCamelCase<Integration>(i))
    .map((i) => convertDatesStringToDate<Integration>(i));

    formattedWebsites = formattedWebsites
    .map((w) => snakeCaseToCamelCase<Website>(w))
    .map((w) => convertDatesStringToDate<Website>(w));

  formattedIntegrations = formattedIntegrations
    .map((i) => snakeCaseToCamelCase<Integration>(i))
    .map((i) => convertDatesStringToDate<Integration>(i));

Is there another way of doing my json_agg but that lets Drizzle format correctly the output? Thank you very much !

8 replies

•Created by sevenwestonroads on 1/19/2024 in #help

ENUM not detected

Using Drizzle ORM

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

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

Whenever I make a change to one of my PG ENUM like add or remove a value to an existing ENUM in my schema, the drizzle generate command doesn't detect it.

2 replies

•Created by sevenwestonroads on 12/18/2023 in #help

What work is required to support ClickHouse?

I'm a big fan of Drizzle. I'm using Clickhouse on tons of projects, it's one of the best if not the best database for high analytical workload across speed, efficiency, easy of use... They have a Node.js driver that's typed but no schema typing. I'd love to contribute - what can I do to help you support ClickHouse? cc @Andrew Sherman Looks like it'd need a new Driver. Thank you !

5 replies

•Created by sevenwestonroads on 10/10/2023 in #help

Psql Array of text and `createSelectSchema`, `createInsertSchema`

It seems that drizzle-zod - createInsertSchema and createSelectSchema cannot infer correctly an array of text. My column;

colName: text('col_name').array()

colName: text('col_name').array()

Indeed, the type of the corresponding column of createSelectSchema or createInsertSchema results as z.ZodString instead of z.ZodArray<z.ZodString>. My workaround is to overwrite the inferred string of the corresponding column with an augment from zod. Any idea?

7 replies

•Created by sevenwestonroads on 10/1/2023 in #help

createSelectSchema & createInsertSchema does not infer array properly

Hey there, I have a table with an array of text text('col_name').array() The issue? Well when I try to call my drizzle-zod create and insert schemas, TS does not understand and tell my the col_name is a string instead of string() Any idea on how to solve this?

1 replies

•Created by sevenwestonroads on 9/20/2023 in #help

Best practice on connections for PostgreSQL

I got the following error; remaining connection slots are reserved for non-replication superuser connections So I'm currently the only person using my DB in dev mode - and only 2 apps in a Turborepo are connected to it. One is a Next.js back-end and one is an SST AWS stack - they both are connected through the code below;

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

Drizzle is my ORM and postgres (also known as postgres.js) is my PG client. Whenever, either in my Next.js app or in my SST app, I want to connect to my database - I'll just call my client constant, and apply either a client.select() or any other query I want to run. The problem ? Well, running a little SELECT * FROM pg_stat_activity returned me 76 connections (all from my machine, ClientRead, on status idle)... It's as if whenever I call my client it opens a new connection, ultimately reaching PSQL limit. What's the best approach ? I believe if many users connect to my app once it's live, it'd be problematic. I've heard about pgBouncer but I don't know if I need session or transaction mode to be enabled. Any best practice ? Again, I just want my Next.js app to support any number of user I want (like 1000 daily users) without any problem with my PSQL reaching connection limits.

8 replies

•Created by sevenwestonroads on 6/5/2023 in #help

Bulk update in Postgres w/ Drizzle ?

I'm using PSQL and would really like if Drizzle supported bulk updates out-of-the-box. I've looked at the docs - but the only option I've seen is an UPSERT (ie. insert w/ onConflictDoUpdate) statement which I'm not fan of for this use case. I'd really like to be able to do a .update(table).set(arrayOfObjects) ! But it looks like it's not supported. I'm thinking about doing an UPDATE FROM statement for PSQL leveraging sql from Drizzle but can't make it work somehow. Any idea ? Thank you !

1 replies

•Created by sevenwestonroads on 5/24/2023 in #help

PSQL SQL query does not work

Unable to achieve it with Drizzle helpers - I had to write my query in plain SQL. I'm trying to build a query that looks like this;

const existingIdentities: Identity[] = await client.execute(
        sql`
          SELECT * FROM identities
          WHERE
            ip_addresses && ARRAY[${batchIPs}]::varchar[] OR
            anonymous_ids && ARRAY[${batchAnonymousIds}]::varchar[] OR
            email IN (${batchEmails.join(", ")})
        `,
      );

const existingIdentities: Identity[] = await client.execute(
        sql`
          SELECT * FROM identities
          WHERE
            ip_addresses && ARRAY[${batchIPs}]::varchar[] OR
            anonymous_ids && ARRAY[${batchAnonymousIds}]::varchar[] OR
            email IN (${batchEmails.join(", ")})
        `,
      );

But I keep getting the following error;

PostgresError: syntax error at or near "$1"

PostgresError: syntax error at or near "$1"

I do not understand the error and can't manage to find a solution... To give you context, ip_addresses is a column of identities that is a VARCHAR[] anonymous_ids is a column of identities that is a VARCHAR[] email is a column of identities that is a VARCHAR Then, all the interpolated values are arrays of strings. Any idea on how to make it work ??? Much appreciated 🙏

2 replies

•Created by sevenwestonroads on 5/23/2023 in #help

Create a type of VARCHAR[]

I want to create a type for my column of VARCHAR[] but when I used Drizzle-Kit to generate it, I got the drizzle-kit that generated "varchar[]" type instead of VARCHAR[] Here's how I defined the column; anonymousIds: varchar("anonymous_ids").array() It represents an array of ids. Thanks a lot !

4 replies

•Created by sevenwestonroads on 5/12/2023 in #help

Deploying Next.js w/ Drizzle on Vercel with Turborepo

I have a turborepo with Next.js using drizzle-orm as a shared package. A bit of context - I'm using a monorepo with drizzle and share the tables, types and schemas across all my apps by having a shared packages/database library - that my Next.js app is consuming. One issue I've had is that in order to get drizzle-orm working in my monorepo I've had to install it globally ie. in my root level package.json. My package.json at the root level looks like this at the moment:

{
  "name": ...,
  "private": true,
  "scripts": {
    "build": "turbo run build",
    "dev": "turbo run dev",
  },
  "devDependencies": {
    "@ianvs/prettier-plugin-sort-imports": "^3.7.2",
    "@types/pg": "^8.6.6",
    "drizzle-kit": "^0.17.5",
    "eslint": "^7.32.0",
    "prettier": "^2.8.7",
    "turbo": "latest"
  },
  "dependencies": {
    "drizzle-orm": "^0.25.4",
    "drizzle-zod": "^0.4.1",
    "pg": "^8.10.0",
    "pg-native": "^3.0.1",
    "zod": "^3.21.4"
  },
  "workspaces": [
    "apps/*",
    "packages/*"
  ]
}

{
  "name": ...,
  "private": true,
  "scripts": {
    "build": "turbo run build",
    "dev": "turbo run dev",
  },
  "devDependencies": {
    "@ianvs/prettier-plugin-sort-imports": "^3.7.2",
    "@types/pg": "^8.6.6",
    "drizzle-kit": "^0.17.5",
    "eslint": "^7.32.0",
    "prettier": "^2.8.7",
    "turbo": "latest"
  },
  "dependencies": {
    "drizzle-orm": "^0.25.4",
    "drizzle-zod": "^0.4.1",
    "pg": "^8.10.0",
    "pg-native": "^3.0.1",
    "zod": "^3.21.4"
  },
  "workspaces": [
    "apps/*",
    "packages/*"
  ]
}

Note that all of the packages above are not in either my packages/database or apps/next dependencies. My Next.js app consume my database library like this in the apps/next application's package.json "database": "1.0.0"

21 replies

•Created by sevenwestonroads on 5/7/2023 in #help

The inferred type of '<tableName>' cannot be named without a reference to '.pnpm/[email protected]

I'm trying to use the drizzle-zod but I keep getting this error... Any idea on where it comes from ? Thank you !

4 replies

•Created by sevenwestonroads on 4/30/2023 in #help

Typesafe floats ?

I'm trying to create a column called "weight". It needs to have values like "33.33" as floats. I'm using PostgreSQL - and I've created a column with the following settings; numeric('weight', { precision: 10, scale: 2 }) The thing is - when I try to insert a value like 33.33 it tells me that

number' is not assignable to type 'string'

number' is not assignable to type 'string'

If the numeric is a string - how can it be typesafe, as I can insert any string I want ? I mean PSQL will throw an error, but how can I make it typesafe ? Thank you guys

4 replies

•Created by sevenwestonroads on 4/23/2023 in #help

UPSERT many ?

I have a table called crmCompanies containing companies from my client's CRMs. Each day, I receive an array with all of the companies of my client. The thing is: I need to insert a new company in crmCompanies if it doesn't exist otherwise, update all its values. Here's the syntax from the docs (https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/README.md#upsert-insert-with-on-conflict-statement); await db.insert(users).values({ id: 1, name: 'Dan' }).onConflictDoUpdate({ target: users.id, set: { name: 'John' } }); But because I have an array - I'd like to do an UPSERT on the whole array, instead of looping through it with a for loop - if that makes sense. Do you know if / how I could achieve that ? Much appreciated.

14 replies

•Created by sevenwestonroads on 4/22/2023 in #help

Using Drizzle as a package in Turborepo doesn't work ?

I have a monorepo (Turborepo) with a shared package called @mono/database in which I put my drizzle schema and my types. I'm testing a query on an app of this monorepo (a simple Node.js folder with TS files), and I also run the exact same query (with the same exact drizzle client definition) from my package to test it out. The thing is: the query works perfectly when ran from my package but outputs an error when run from my app that uses the content of the package. When I run the query from my package directly, the logger it outputs the beautiful SQL statement complete with all the tables and fields. But when I run it from my app (the Node.js folder with TS files) I get the following weird statement;

Query: select  from $1 left join $2 on $3 = $4 order by $5 desc limit $6 -- params: [[object Object], [object Object], [object Object], [object Object], [object Object], 1]

Query: select  from $1 left join $2 on $3 = $4 order by $5 desc limit $6 -- params: [[object Object], [object Object], [object Object], [object Object], [object Object], 1]

Which returns me the error below

.../.pnpm/[email protected]/node_modules/pg/lib/utils.js:81
  return JSON.stringify(val)
              ^
TypeError: Converting circular structure to JSON
    --> starting at object with constructor 'PgTable'
    |     property 'id' -> object with constructor 'PgSerial'
    --- property 'table' closes the circle
    at JSON.stringify (<anonymous>)

.../.pnpm/[email protected]/node_modules/pg/lib/utils.js:81
  return JSON.stringify(val)
              ^
TypeError: Converting circular structure to JSON
    --> starting at object with constructor 'PgTable'
    |     property 'id' -> object with constructor 'PgSerial'
    --- property 'table' closes the circle
    at JSON.stringify (<anonymous>)

------------------------------------------------ I logged the client in both cases, and also the tables which outputted, for both, the exact object of 750 lines. But when I copy / pasted the corresponding pgTable directly in the file of my app in which I was importing my tables from the package and it works with no errors. Here is the package.json of my @mono/database package.

{
  "name": "@mono/database",
  "version": "0.1.0",
  "main": "./index.ts",
  ...
 }

{
  "name": "@mono/database",
  "version": "0.1.0",
  "main": "./index.ts",
  ...
 }

Any idea ? So that means the bundling in the turborepo process might be creating the error ?

13 replies

•Created by sevenwestonroads on 4/20/2023 in #help

How to reproduce a Prisma `include` statement for arrays of related entities without SQL?

Hello, I'm trying to translate this SQL query to DrizzleORM;

SELECT
  channels.id,
  channels.name,
  channels.slug,
  channels.type,
  array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;

SELECT
  channels.id,
  channels.name,
  channels.slug,
  channels.type,
  array_agg(channel_performances.*) AS channel_performances
FROM channels
LEFT JOIN channel_performance ON channels.id = channel_performance.channel_id
GROUP BY channels.id;

My issue is that I don't know how to use / when to use the array_agg - do I have to use raw SQL ? In Prisma, I could just do a findMany for channels w/ include the channel_performances. In my case the objective is to retrieve the channels with inside, the channel_performances as array for each channels. Do you know how to do that in Drizzle ORM ? Thank you !

21 replies

•Created by sevenwestonroads on 4/19/2023 in #help

Table definition has 'any' type

I'm defining all of my tables in an index.ts file; but on some tables like visitor_events I get this errors;

'visitor_events' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

On other tables however, I don't have the error and the PgTableWithColumns is correctly infered. I've restarted TS Server and VSCode but with no success.

13 replies