jsingleton37 Posts - Answer Overflow

jsingleton37

Posts Comments

DTDrizzle Team

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

Running into some type errors with query.where

13 replies

DTDrizzle Team

•Created by jsingleton37 on 12/12/2024 in #help

I messed up my supabase and drizzle setup

To start off, databases and ORMs I am not super familiar with and didn't realize I was just directly running migrations against the production db 🙃 so after chatting with some folks I decided to install docker and run pnpm supabase start locally and that ended up failing due to

ERROR: column "class_year" cannot be cast automatically to type integer (SQLSTATE 42804)
At statement 1: --> statement-breakpoint                                                
ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer

ERROR: column "class_year" cannot be cast automatically to type integer (SQLSTATE 42804)
At statement 1: --> statement-breakpoint                                                
ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer

I used to have a class_year column in that table, however, I no longer do as it was renamed and even shows up under https://github.com/JamesSingleton/redshirt-sports/tree/feature/transfer-portal/supabase as being renamed... So not entirely sure what to do from here.

19 replies

DTDrizzle Team

•Created by jsingleton37 on 12/12/2024 in #help

Column of relation already exists

I have a couple of tables

export const schoolReferences = pgTable(
  'school_references',
  {
    id: serial('id').primaryKey(),
    sanityId: text('sanity_id').notNull().unique(),
    name: text('name'),
  },
  (table) => [uniqueIndex('sanity_id_idx').on(table.sanityId)],
)

export const players = pgTable('players', {
  id: serial('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  currentSchoolId: integer('current_school_id').references(() => schoolReferences.id),
})

export const transferPortalEntries = pgTable('transfer_portal_entries', {
  id: serial('id').primaryKey(),
  playerId: integer('player_id')
    .references(() => players.id)
    .notNull(),
  previousSchoolId: integer('previous_school_id')
    .references(() => schoolReferences.id)
    .notNull(),
  commitmentSchoolId: integer('commitment_school_id').references(() => schoolReferences.id),
})

export const schoolReferences = pgTable(
  'school_references',
  {
    id: serial('id').primaryKey(),
    sanityId: text('sanity_id').notNull().unique(),
    name: text('name'),
  },
  (table) => [uniqueIndex('sanity_id_idx').on(table.sanityId)],
)

export const players = pgTable('players', {
  id: serial('id').primaryKey(),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  currentSchoolId: integer('current_school_id').references(() => schoolReferences.id),
})

export const transferPortalEntries = pgTable('transfer_portal_entries', {
  id: serial('id').primaryKey(),
  playerId: integer('player_id')
    .references(() => players.id)
    .notNull(),
  previousSchoolId: integer('previous_school_id')
    .references(() => schoolReferences.id)
    .notNull(),
  commitmentSchoolId: integer('commitment_school_id').references(() => schoolReferences.id),
})

I went to add a new column name to the school_references table & remove a column from transfer_portal_entries and ran drizzle-kit generate followed by drizzle-kit migrate. However, when I run the migrate command I get applying migrations...PostgresError: column "name" of relation "school_references" already exists. I previously added name via the Supabase dashboard to test something and then removed it but it was back after running generate and migrate the other day (which I want). I'm not sure why it's complaining now.

3 replies

DTDrizzle Team

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

Is there a way to force a text field to be certain values?

I want a certain column to be one of three values, “Entered”, “Committed”, and “Withdrawn”. Is there a way to do that at the DB level with drizzle?

2 replies

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