Drizzle Team•16mo ago

Issues with supabase inserts with drizzle

        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });

        //make sure team is in db
        const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });

        //make sure team is in db
        const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

the transaction returns the team object with id from supabase db but when querying it to make sure it exists the team is not found. not sure whats going on exactly would love some clarification on is i'm just missing something

22 Replies

rphlmr ⚡•16mo ago

Do you see the team on Supabase dashboard?

ScrewOP•16mo ago

it is not there but if i do spam this function it will work sometimes so it’s super weird

rphlmr ⚡•16mo ago

really weird yes. Maybe you can share the whole function?

Angelelz•16mo ago

Maybe turn on Drizzle logging to see what the sql output looks like.

ScrewOP•16mo ago

Query: insert into "teams" ("id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id") values (default, $1, $2, $3, $4, default, $5) returning "id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id" -- params: ["WhiteMushyMarsupial460", "DOUBLES", "WAGER", 2, 10]
Query: insert into "team_stats" ("id", "team_id", "total_wins", "total_losses", "xp", "total_money_earned", "total_money_lost", "team_rank") values (default, $1, $2, $3, $4, $5, $6, default) -- params: [94, 0, 0, 0, 0, 0]
Query: insert into "user_teams" ("user_id", "team_id") values ($1, $2) -- params: [10, 94]
Query: select "id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id" from "teams" where "teams"."id" = $1 -- params: [94]

Query: insert into "teams" ("id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id") values (default, $1, $2, $3, $4, default, $5) returning "id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id" -- params: ["WhiteMushyMarsupial460", "DOUBLES", "WAGER", 2, 10]
Query: insert into "team_stats" ("id", "team_id", "total_wins", "total_losses", "xp", "total_money_earned", "total_money_lost", "team_rank") values (default, $1, $2, $3, $4, $5, $6, default) -- params: [94, 0, 0, 0, 0, 0]
Query: insert into "user_teams" ("user_id", "team_id") values ($1, $2) -- params: [10, 94]
Query: select "id", "team_name", "team_type", "match_type", "game_id", "created_at", "captain_id" from "teams" where "teams"."id" = $1 -- params: [94]

this my logger queries and this is the full function

import { json, error } from '@sveltejs/kit';
import { db } from '$database';
import { teams, user_teams, team_stats, games } from '$database/schema';
import { eq } from 'drizzle-orm';

export async function POST({ request, locals }) {
    if (!locals.user) throw error(401, 'Unauthorized');

    const data = await request.json();
    const { team_name, game_id, team_type, match_type } = data;

    if (!team_name || !game_id || !team_type || !match_type)
        throw error(400, 'Missing required fields.');

    if (team_name.length > 255 || team_name.length < 3)
        throw error(400, 'Team name is too long or too short.');

    const existingTeam = await db
        .select()
        .from(teams)
        .where(eq(teams.team_name, team_name.toLowerCase()))
        .limit(1);

    const allowedTeamTypes = ['SOLO', 'DOUBLES', 'TEAM'];
    const allowedMatchTypes = ['LADDER', 'WAGER', 'TOURNAMENT'];

    if (existingTeam.length > 0) throw error(400, 'Team name already exists.');
    if (!allowedTeamTypes.includes(team_type)) throw error(400, 'Invalid team type.');
    if (!allowedMatchTypes.includes(match_type)) throw error(400, 'Invalid match type.');

    const teamObj = {
        team_name,
        game_id,
        team_type,
        match_type,
        captain_id: Number(locals.user.id)
    };
    try {
        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });

        //make sure team is in db
        const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

        console.log(test);

        if (!test) throw error(500, 'Error Team not created');

        const [game] = await db
            .select({ game_slug: games.slug, game_name: games.name })
            .from(games)
            .where(eq(games.id, Number(team?.game_id)));

        const ExtendedTeam = {
            ...team,
            team_id: team?.id,
            game_slug: game?.game_slug,
            game_name: game?.game_name
        };
        return json({ team: ExtendedTeam });
    } catch (err) {
        console.log(err.message);
        if ((err as any)?.body) throw error(400, (err as any).body.message);
        if ((err as Error).message.includes('teams_team_name_unique')) {
            throw error(400, 'Team name already exists.');
        }
        throw err; // Re-throw the error if it's not the one we're looking for
    }
}

import { json, error } from '@sveltejs/kit';
import { db } from '$database';
import { teams, user_teams, team_stats, games } from '$database/schema';
import { eq } from 'drizzle-orm';

export async function POST({ request, locals }) {
    if (!locals.user) throw error(401, 'Unauthorized');

    const data = await request.json();
    const { team_name, game_id, team_type, match_type } = data;

    if (!team_name || !game_id || !team_type || !match_type)
        throw error(400, 'Missing required fields.');

    if (team_name.length > 255 || team_name.length < 3)
        throw error(400, 'Team name is too long or too short.');

    const existingTeam = await db
        .select()
        .from(teams)
        .where(eq(teams.team_name, team_name.toLowerCase()))
        .limit(1);

    const allowedTeamTypes = ['SOLO', 'DOUBLES', 'TEAM'];
    const allowedMatchTypes = ['LADDER', 'WAGER', 'TOURNAMENT'];

    if (existingTeam.length > 0) throw error(400, 'Team name already exists.');
    if (!allowedTeamTypes.includes(team_type)) throw error(400, 'Invalid team type.');
    if (!allowedMatchTypes.includes(match_type)) throw error(400, 'Invalid match type.');

    const teamObj = {
        team_name,
        game_id,
        team_type,
        match_type,
        captain_id: Number(locals.user.id)
    };
    try {
        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });

        //make sure team is in db
        const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

        console.log(test);

        if (!test) throw error(500, 'Error Team not created');

        const [game] = await db
            .select({ game_slug: games.slug, game_name: games.name })
            .from(games)
            .where(eq(games.id, Number(team?.game_id)));

        const ExtendedTeam = {
            ...team,
            team_id: team?.id,
            game_slug: game?.game_slug,
            game_name: game?.game_name
        };
        return json({ team: ExtendedTeam });
    } catch (err) {
        console.log(err.message);
        if ((err as any)?.body) throw error(400, (err as any).body.message);
        if ((err as Error).message.includes('teams_team_name_unique')) {
            throw error(400, 'Team name already exists.');
        }
        throw err; // Re-throw the error if it's not the one we're looking for
    }
}

I'm using sveltekit, and supabase

Angelelz•16mo ago

I don't see a transaction beginning or ending in your query there.. Does Supabase use Mysql? Does it support transactions? I don't see anything wrong with your function.

ScrewOP•16mo ago

supabase uses postgres and i do believe they support transactions

        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });
        console.log(team);

        const team = await db.transaction(async (tx) => {
            const [team] = await tx.insert(teams).values(teamObj).returning();

            await tx.insert(team_stats).values({
                team_id: team.id,
                total_losses: 0,
                total_wins: 0,
                xp: 0,
                total_money_earned: 0,
                total_money_lost: 0
            });

            await tx.insert(user_teams).values({
                team_id: team.id,
                user_id: Number(locals.user.id)
            });

            return team;
        });
        console.log(team);

this console log logs

{
  id: 104,
  team_name: 'PinkConcreteSquirrel5180',
  team_type: 'TEAM',
  match_type: 'WAGER',
  game_id: 2,
  created_at: 2023-08-18T16:22:11.000Z,
  captain_id: 10
}

{
  id: 104,
  team_name: 'PinkConcreteSquirrel5180',
  team_type: 'TEAM',
  match_type: 'WAGER',
  game_id: 2,
  created_at: 2023-08-18T16:22:11.000Z,
  captain_id: 10
}

showing the id was made from db but when checking that same id that was made

    const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

        console.log(test, 'here');

    const [test] = await db.query.teams.findMany({
            where: (teams, { eq }) => eq(teams.id, team.id)
        });

        console.log(test, 'here');

i get undefined

undefined here

undefined here

ah it seems like some prepared statements are failing

rphlmr ⚡•16mo ago

Not sure to be able to read that this evening but I'll take a look. I use Supabase and transaction every day 😄

ScrewOP•16mo ago

any help will be much appreciated sir i kinda narrowed it down to when i check supabase logs under postgres i get

prepared statement "xokagzosej96" does not exist

prepared statement "xokagzosej96" does not exist

which im not sure why since im not doing any prepared statements

rphlmr ⚡•16mo ago

something trouble me with query logs The only thing you is Error Team not created right? Even if it fails at if (!test) throw error(500, 'Error Team not created');, you see the team on db?

ScrewOP•16mo ago

the thing is i don’t see it in db like the above insert did nothing but has to be doing SOMETHING to be returning back data right? that if was there so i can at least figure out when it’s working or not

rphlmr ⚡•16mo ago

Surprising because if the transaction end, it should be committed on db I would understand if it was throwing in the transaction but here it seems to not be the case I’m not aware of such a bug. what driver are you using?

ScrewOP•16mo ago

yes that’s what i’m not understanding i’m switching from the pooling url to the long standing one but since i’ll be using vercel for production i’m not sure that will work

rphlmr ⚡•16mo ago

on the supabase dashboard, you are sure that you don’t have any set filter (it happened to me ... four hours lol) Is it an open-source project? I will try to reverse-engineer your tables and tell you if it works for me

francis•16mo ago

potential issue: do you have your pgbouncer settings in supabase set to statement level pooling it's an option you can select and if you do transactions no longer work as expected

rphlmr ⚡•16mo ago

Where do you find this? I only have transaction or session If it matters, I use postgres-js as driver

francis•16mo ago

oh, maybe they removed it. They used to allow you to turn statement level pooling on which would break all transaction support

francis•16mo ago

https://supabase.com/blog/supabase-pgbouncer#pool-modes

Supabase

PgBouncer is now available in Supabase

Better support for Serverless and Postgres.

rphlmr ⚡•16mo ago

My production db is in transaction mode. Seems to be the default now.

ScrewOP•16mo ago

it is not open source but i can invite you since its only i working on it, and yeah mine is transaction too no filters on that i know of

rphlmr ⚡•16mo ago

Don't worry. Maybe you can share you schema? Or else I will typing it lol can be in private if you want 😉

rphlmr ⚡•16mo ago

For people following this thread, pooler on port 6543 is really unstable on high rate. Direct connection on 5432 works fine. If you encounter the same issue on Supabase serverless env, try to ask Supabase for Supavisor https://supabase.com/docs/guides/database/connecting-to-postgres#supavisor

Connecting to your database | Supabase Docs

Explore the options for connecting to your Postgres database.

Gaming

Programming

Issues with supabase inserts with drizzle