jsingleton37 Posts - Answer Overflow

jsingleton37

Posts Comments

DTDrizzle Team

•Created by jsingleton37 on 9/2/2024 in #help

What is the best way to get distinct values from a table?

3 replies

DTDrizzle Team

•Created by jsingleton37 on 8/16/2024 in #help

Properly setting timestamp column

1 replies

DTDrizzle Team

•Created by jsingleton37 on 7/27/2024 in #help

Confused about onConflictDoUpdate when having a constraint

I have a table that looks like

export const weeklyFinalRankings = pgTable(
  'weekly_final_rankings',
  {
    id: serial('id').primaryKey(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year').notNull(),
    rankings: jsonb('rankings').notNull(),
  },
  (table) => ({
    isUniqueVote: unique().on(table.year, table.week),
  }),
)

export const weeklyFinalRankings = pgTable(
  'weekly_final_rankings',
  {
    id: serial('id').primaryKey(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year').notNull(),
    rankings: jsonb('rankings').notNull(),
  },
  (table) => ({
    isUniqueVote: unique().on(table.year, table.week),
  }),
)

and after going through some docs, I was doing an insert like so

await db
  .insert(weeklyFinalRankings)
  .values({
    division,
    year: 2024,
    week: 0,
    rankings: rankedTeams,
  })
  .onConflictDoUpdate({
    target: weeklyFinalRankings.week,
    set: { rankings: rankedTeams },
  })

await db
  .insert(weeklyFinalRankings)
  .values({
    division,
    year: 2024,
    week: 0,
    rankings: rankedTeams,
  })
  .onConflictDoUpdate({
    target: weeklyFinalRankings.week,
    set: { rankings: rankedTeams },
  })

However, I get PostgresError: there is no unique or exclusion constraint matching the ON CONFLICT specification What am I doing incorrect here?

4 replies

DTDrizzle Team

•Created by jsingleton37 on 7/23/2024 in #help

Is there a way to say a column value should come from another table?

I have the following two schemas

export const seasonsTable = pgTable(
  'seasons',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
)

export const weeksTable = pgTable(
  'weeks',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    week: integer('week').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.year, table.week),
  }),
)

export const seasonsTable = pgTable(
  'seasons',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueSeason: unique().on(table.year),
  }),
)

export const weeksTable = pgTable(
  'weeks',
  {
    id: serial('id').primaryKey(),
    year: integer('year').notNull(),
    week: integer('week').notNull(),
    start: timestamp('start').notNull(),
    end: timestamp('end').notNull(),
  },
  (table) => ({
    isUniqueWeek: unique().on(table.year, table.week),
  }),
)

And in the weeks table, I want to say that the year should come from the seasons table. Is that something that's possible? Is that something that is advisable? I currently grab this information from an ESPN endpoint, but figured I should probably migrate this into my own DB so I don't rely on an endpoint that could potentially get blocked.

3 replies

DTDrizzle Team

•Created by jsingleton37 on 7/20/2024 in #help

Is there a way to just return a specific column?

I have a DB that holds voting results and I want to grab all the weeks that have votes. I.e. if there are votes for weeks 0-5, I just want to return 0, 1, 2, 3, 4, 5 and not the entire row for each of those weeks.

7 replies

DTDrizzle Team

•Created by jsingleton37 on 7/5/2024 in #help

TypeError saying `id` doesn't exist in table?

1 replies

DTDrizzle Team

•Created by jsingleton37 on 6/19/2024 in #help

Is there a way to check/constrain a column?

1 replies

DTDrizzle Team

•Created by jsingleton37 on 6/15/2024 in #help

Querying DB returning same row for two different userIds

Hi everyone 👋 , I am running into an issue where I have two different users logged in (one on Chrome and one on Safari) to test multiple users logging in at once. One user has submitted a ballot and the other has not. However, with the one that has not voted, hasVoterVoted is returning true AND getUsersVote is returning the vote of the other user. I logged out user.userId and they are different in each call... So why would drizzle/supabase be returning the exact same information for two different userId? My guess is due to the eq(model.userId, user.userId) && eq(model.year, year) && eq(model.week, week) because if I remove the check for year and week in the hasVoterVoted, it returns false for the user who has not voted.

2 replies

DTDrizzle Team

•Created by jsingleton37 on 6/15/2024 in #help

How to add a new column to an existing table

2 replies

DTDrizzle Team

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

onConflictDoNothing still incrementing primaryKey

9 replies

DTDrizzle Team

•Created by jsingleton37 on 6/4/2024 in #help

What's the best way to prevent duplicate entries?

I am building a voting app for college sports. A user can go and submit their Top 25 teams each week in a season. My API route looks like

import * as Sentry from '@sentry/nextjs'

import { auth } from '@clerk/nextjs/server'
import { db } from '@/server/db'
import { votes } from '@/server/db/schema'

export async function POST(req: Request) {
  try {
    const body = await req.json()
    const user = auth()

    if (!user.userId) {
      return new Response('Unauthorized', { status: 401 })
    }

    const votesConverted = Object.entries(body)
      .map(([key, value]) => {
        const match = key.match(/rank_(\d+)/)
        if (!match) return null
        const rank = parseInt(match[1], 10)
        return { teamId: String(value), rank: rank } // Convert 'value' to a string
      })
      .map((vote) => ({
        userId: user.userId,
        week: 1,
        teamId: vote!.teamId,
        rank: vote!.rank,
      }))

    await db.insert(votes).values(votesConverted)

    return new Response('OK', { status: 200 })
  } catch (error) {
    Sentry.captureException(error)
    return Response.error()
  }
}

import * as Sentry from '@sentry/nextjs'

import { auth } from '@clerk/nextjs/server'
import { db } from '@/server/db'
import { votes } from '@/server/db/schema'

export async function POST(req: Request) {
  try {
    const body = await req.json()
    const user = auth()

    if (!user.userId) {
      return new Response('Unauthorized', { status: 401 })
    }

    const votesConverted = Object.entries(body)
      .map(([key, value]) => {
        const match = key.match(/rank_(\d+)/)
        if (!match) return null
        const rank = parseInt(match[1], 10)
        return { teamId: String(value), rank: rank } // Convert 'value' to a string
      })
      .map((vote) => ({
        userId: user.userId,
        week: 1,
        teamId: vote!.teamId,
        rank: vote!.rank,
      }))

    await db.insert(votes).values(votesConverted)

    return new Response('OK', { status: 200 })
  } catch (error) {
    Sentry.captureException(error)
    return Response.error()
  }
}

Right now I can just keep submitting the same form over and over again. Is there a quick and dirty way to just see if this user has submitted a vote for that week and year? Currently year is just autopopulated.

11 replies

DTDrizzle Team

•Created by jsingleton37 on 6/2/2024 in #help

Drizzle Studio Not Showing Tables that are in Supabase

I am starting to work on a project and using Drizzle and Supabase. I just did a drizzle-kit generate followed by a drizzle-kit migrate. When I look in supabase.com it shows that table. However, when I run drizzle-kit studio, I just get a spinner. This is what my drizzle.config.ts looks like

import { defineConfig } from 'drizzle-kit'
import { env } from '@/app/env'

export default defineConfig({
  dialect: 'postgresql',
  schema: './server/db/schema.ts',
  out: './supabase/migrations',
  dbCredentials: {
    url: env.POSTGRES_URL,
  },
})

import { defineConfig } from 'drizzle-kit'
import { env } from '@/app/env'

export default defineConfig({
  dialect: 'postgresql',
  schema: './server/db/schema.ts',
  out: './supabase/migrations',
  dbCredentials: {
    url: env.POSTGRES_URL,
  },
})

The POSTGRES_URL is from the Vercel & Supabase integration and uses the port 6543. However, if I use the POSTGRES_URL_NON_POOLING env var that ends in 5432, the studio will load. When looking into Supabase's Database Settings, it looks like the one ending in 6543 is for transaction mode and the one ending in 5432 is for session mode. Does this mean I need to use the non pooling URL for drizzle studio?

3 replies

DTDrizzle Team

•Created by jsingleton37 on 6/2/2024 in #help

Trying to figure drizzle & Supabase out

1 replies

Gaming

Programming