PWSey Posts - Answer Overflow

PWSey

•Created by PWSey on 10/30/2023 in #help

Using db. causes the whole app to hang forever.

drizzle.config.ts

import type { Config } from "drizzle-kit"

import "dotenv/config"

export default {
  schema: "./src/lib/db/schema.ts",
  driver: "mysql2",
  out: "./src/drizzle/migrations",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config

import type { Config } from "drizzle-kit"

import "dotenv/config"

export default {
  schema: "./src/lib/db/schema.ts",
  driver: "mysql2",
  out: "./src/drizzle/migrations",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config

index.ts

import { connect } from "@planetscale/database"
import { drizzle } from "drizzle-orm/planetscale-serverless"

 
import * as schema from "@/lib/db/schema"

import "dotenv/config"

const connection = connect({
  url: process.env.DATABASE_URL,
})

export const db = drizzle(connection, { schema })

export const getFeedbacks = async ({ query }: { query?: string }) => {...
}

import { connect } from "@planetscale/database"
import { drizzle } from "drizzle-orm/planetscale-serverless"

 
import * as schema from "@/lib/db/schema"

import "dotenv/config"

const connection = connect({
  url: process.env.DATABASE_URL,
})

export const db = drizzle(connection, { schema })

export const getFeedbacks = async ({ query }: { query?: string }) => {...
}

layout.tsx

import "./globals.css"

import { db } from "@/lib/db"
import { users } from "@/lib/db/schema"

export const metadata = {...
}

export default async function RootLayout({
  children,
  authModal,
}: {
  children: React.ReactNode
  authModal: React.ReactNode
}) {

  const data = await db.query.users.findMany()
  console.log(data)
  return (
    <html lang="en">...
    </html>
  )
}

import "./globals.css"

import { db } from "@/lib/db"
import { users } from "@/lib/db/schema"

export const metadata = {...
}

export default async function RootLayout({
  children,
  authModal,
}: {
  children: React.ReactNode
  authModal: React.ReactNode
}) {

  const data = await db.query.users.findMany()
  console.log(data)
  return (
    <html lang="en">...
    </html>
  )
}

I can successfully db:push, and I can see the tables being created on planetscale as well as when running db:studio

2 replies

DTDrizzle Team

•Created by PWSey on 10/27/2023 in #help

Neon DB pooled connection prepared statement error

When using Neon DB with pooled connection it gives the following error:

- error node_modules\@neondatabase\serverless\index.js (1539:47) @ execute
- error NeonDbError: db error: ERROR: prepared statement "s10734" does not exist

Caused by:
    ERROR: prepared statement "s10734" does not exist
    at async getFeedbacks (./src/lib/feedbacks.ts:47:20)
null

- error node_modules\@neondatabase\serverless\index.js (1539:47) @ execute
- error NeonDbError: db error: ERROR: prepared statement "s10734" does not exist

Caused by:
    ERROR: prepared statement "s10734" does not exist
    at async getFeedbacks (./src/lib/feedbacks.ts:47:20)
null

The above error happends when using prepared statement, as well as when not using prepared statements. Without pooled connection it works.

2 replies

DTDrizzle Team

•Created by PWSey on 10/23/2023 in #help

How to get a similar result of db.query but with extra aggregation and using select

I have the following drizzle query which works correctly but the results is not what I expect since we are grouping votes as well by their ids.

export const mostUpvoted = await db
  .select({
    id: feedback.id,
    feedbackNo: feedback.feedbackNo,
    userId: feedback.userId,
    title: feedback.title,
    body: feedback.body,
    type: feedback.type,
    status: feedback.status,
    isPinned: feedback.isPinned,
    createdAt: feedback.createdAt,
    updatedAt: feedback.updatedAt,
    totalVotes: sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`,
    votes: votes,
    author: users,
  })
  .from(feedback)
  .leftJoin(users, eq(feedback.userId, users.id))
  .leftJoin(votes, eq(votes.feedbackId, feedback.id))
  .groupBy(feedback.id, votes.id, users.id)
  .orderBy(asc(feedback.id))
  .prepare("most_upvoted");

export const mostUpvoted = await db
  .select({
    id: feedback.id,
    feedbackNo: feedback.feedbackNo,
    userId: feedback.userId,
    title: feedback.title,
    body: feedback.body,
    type: feedback.type,
    status: feedback.status,
    isPinned: feedback.isPinned,
    createdAt: feedback.createdAt,
    updatedAt: feedback.updatedAt,
    totalVotes: sql<number>`sum(CASE WHEN ${votes.voteType} = 'UP' THEN 1 WHEN ${votes.voteType} = 'DOWN' THEN -1 ELSE 0 END)`,
    votes: votes,
    author: users,
  })
  .from(feedback)
  .leftJoin(users, eq(feedback.userId, users.id))
  .leftJoin(votes, eq(votes.feedbackId, feedback.id))
  .groupBy(feedback.id, votes.id, users.id)
  .orderBy(asc(feedback.id))
  .prepare("most_upvoted");

I am trying to get

const test = await db.query.feedback.findMany({
    with: {
      author: true,
      votes: true,
    },
});

const test = await db.query.feedback.findMany({
    with: {
      author: true,
      votes: true,
    },
});

this results in all the votes which is what I am trying to achieve, but I am also trying to aggregate the total votes from the database as I believe it will be easier when filtering.

12 replies

Gaming

Programming