Drizzle Team•13mo 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 ⚡•13mo ago

Do you see the team on Supabase dashboard?

Screw•13mo ago

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

rphlmr ⚡•13mo ago

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

Angelelz•13mo ago

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

Screw•13mo 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•13mo 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.

Screw•13mo 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 ⚡•13mo ago

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

Screw•13mo 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 ⚡•13mo 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?

Screw•13mo 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 ⚡•13mo 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?

Screw•13mo 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 ⚡•13mo 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•13mo 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

Gaming

Programming

Issues with supabase inserts with drizzle