DT
Drizzle Team•10mo ago
Boxer

Supabase/postgres get query is stuck on prod app

Hi, I'm coming across an issue with my discord bot, I currently have a query that checks if an user exists in the database. But for some reason, after a while (I believe) this query gets stuck and my bot becomes unresponsive because of it. 
import { db } from '@db/index';
import { users } from '@db/schema/user';
import { eq } from 'drizzle-orm';
import { logger } from './logger';

export const validateUserExists = async (userId: bigint) => {
  try {
    logger.info(`Validating ${userId} exists in database...`);
    let [user] = await db.select().from(users).where(eq(users.user_id, userId));
    if (!user) {
      logger.info(`User ${userId} not found, creating...`);
      [user] = await db
        .insert(users)
        .values({ user_id: userId, balance: 0 })
        .returning();
    }
    logger.info(`User ${user.user_id} validated`);
  } catch (error) {
    logger.error(error);
  }
};
import { db } from '@db/index';
import { users } from '@db/schema/user';
import { eq } from 'drizzle-orm';
import { logger } from './logger';

export const validateUserExists = async (userId: bigint) => {
  try {
    logger.info(`Validating ${userId} exists in database...`);
    let [user] = await db.select().from(users).where(eq(users.user_id, userId));
    if (!user) {
      logger.info(`User ${userId} not found, creating...`);
      [user] = await db
        .insert(users)
        .values({ user_id: userId, balance: 0 })
        .returning();
    }
    logger.info(`User ${user.user_id} validated`);
  } catch (error) {
    logger.error(error);
  }
};
I currently have this app hosted on railway and I can see from the logs is that it stops after it queries the DB. 
[20:57:10.461] [info] Validating 211490267285422080 exists in database...

[20:57:10.462] [log] Query: select "id", "user_id", "balance" from "users" where "users"."user_id" = $1 -- params: [211490267285422080]
[20:57:10.461] [info] Validating 211490267285422080 exists in database...

[20:57:10.462] [log] Query: select "id", "user_id", "balance" from "users" where "users"."user_id" = $1 -- params: [211490267285422080]
I've checked the supabase logs and it shows that the connection is received and authorized. Honestly, i'm not sure if this is a Supabase issue or a drizzle issue. Maybe someone knows what could be happening? -- configs: 
//drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema',
  out: './supabase/migrations',
  driver: 'pg',
  breakpoints: true,
  dbCredentials: {
    connectionString: process.env.DATABASE_URL || '',
  },
} satisfies Config;
//drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema',
  out: './supabase/migrations',
  driver: 'pg',
  breakpoints: true,
  dbCredentials: {
    connectionString: process.env.DATABASE_URL || '',
  },
} satisfies Config;
// client
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema/user';

// create the connection
const connection = postgres(Bun.env.DATABASE_URL as string, {
  max: 30,
  max_lifetime: 300, // 5 minutes
});


export const db = drizzle(connection, {
  logger: true,
  schema,
});
// client
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema/user';

// create the connection
const connection = postgres(Bun.env.DATABASE_URL as string, {
  max: 30,
  max_lifetime: 300, // 5 minutes
});


export const db = drizzle(connection, {
  logger: true,
  schema,
});
No description
12 Replies
Boxer
Boxer•10mo ago
looks like it could have been that I have the postgres property max set to 1 😬 After a while it still stays trying to query 😔 I added redis because i was making lots of calls to the DB to offload, but it still gets stuck
Boxer
Boxer•10mo ago
I have a discord bot that makes sql queries whenever an event happens; for example, when people talk in a channel, the bot checks if the channel is of certain criteria set on the DB - this means there's a call everytime someone talks in a channel. I believe this caused issues, because when channels become very active this requires many DB calls. To offload DB queries, I added redis and it helped a bit but it seems that my app after a certain amount of time gets stuck making calls to the DB. It looks like I get errors back but they just come back as an empty {}. I am not sure how to debug this. I am not sure if this is a drizzleORM issue or a supabase issue 
export async function isSuccessChannel(message: Message) {
  try {
    const { channel_id, guild_id } = selectChannelSchema.parse({
      channel_id: BigInt(message.channelId),
      guild_id: BigInt(message.guildId as string),
    });

    const cacheKey = `guild:${guild_id}:channel:${channel_id}:isSuccess`;

    // First, check if the result is in the cache
    logger.info(`Checking cache for channel (${channel_id})`);
    const cachedResult = await redis.get(cacheKey);
    if (cachedResult !== null) {
      logger.info(`Cached channel (${channel_id}) is success: ${cachedResult}`);
      return cachedResult === 'true';
    }

    logger.info(`Channel (${channel_id}) is not cached, checking database`);
    const [channel] = await db
      .select()
      .from(channels)
      .where(
        and(
          eq(channels.channel_type, 'success'),
          eq(channels.channel_id, channel_id as bigint),
          eq(channels.guild_id, guild_id as bigint),
        ),
      );
    // Cache the result with an expiration (e.g., 1 hour = 3600 seconds)
    const result = channel ? 'true' : 'false';
    await redis.setex(cacheKey, 3600, result);
    logger.info(
      `Channel (${channel_id}) has been cached with result: ${result}`,
    );

    return channel ? true : false;
  } catch (error) {
    logger.error(error);
    return false;
  }
}
export async function isSuccessChannel(message: Message) {
  try {
    const { channel_id, guild_id } = selectChannelSchema.parse({
      channel_id: BigInt(message.channelId),
      guild_id: BigInt(message.guildId as string),
    });

    const cacheKey = `guild:${guild_id}:channel:${channel_id}:isSuccess`;

    // First, check if the result is in the cache
    logger.info(`Checking cache for channel (${channel_id})`);
    const cachedResult = await redis.get(cacheKey);
    if (cachedResult !== null) {
      logger.info(`Cached channel (${channel_id}) is success: ${cachedResult}`);
      return cachedResult === 'true';
    }

    logger.info(`Channel (${channel_id}) is not cached, checking database`);
    const [channel] = await db
      .select()
      .from(channels)
      .where(
        and(
          eq(channels.channel_type, 'success'),
          eq(channels.channel_id, channel_id as bigint),
          eq(channels.guild_id, guild_id as bigint),
        ),
      );
    // Cache the result with an expiration (e.g., 1 hour = 3600 seconds)
    const result = channel ? 'true' : 'false';
    await redis.setex(cacheKey, 3600, result);
    logger.info(
      `Channel (${channel_id}) has been cached with result: ${result}`,
    );

    return channel ? true : false;
  } catch (error) {
    logger.error(error);
    return false;
  }
}
No description
Andrii Sherman
Andrii Sherman•10mo ago
so you are using postgres.js but I can't see anything from with drizzle setup so far it may be supabase issue I guess you can try to run this in docker Postgres or neon as long as all of that is postgres compatibale - you can just change connection url if you will see that issue is gone - it's supabase issue if not - I can check more into drizzle
Boxer
Boxer•10mo ago
@Andrew Sherman it's very hard to debug because it does not happen in dev. I do have a supabase dev environment on my local at this time and it works fine. In production, the queries work but after some time it just stops working, so it might be a supabase issue!
Andrii Sherman
Andrii Sherman•10mo ago
yeah, seems like it
Boxer
Boxer•10mo ago
Yup, thank you for taking a look!! I'll report back once i find a fix Hey @Andrew Sherman sorry to bother you once again. So it looks like it could be a client issue, I was able to gather some logs: 
[  {
    "error_severity": "FATAL",
    "event_message": "unsupported frontend protocol 0.0: server supports 3.0 to 3.0",
    "timestamp": "2023-12-04T16:55:31.290000"
  },
  {
    "error_severity": "FATAL",
    "event_message": "unsupported frontend protocol 65363.19778: server supports 3.0 to 3.0",
    "timestamp": "2023-12-04T15:32:21.287000"
  },
  {
    "error_severity": "FATAL",
    "event_message": "password authentication failed for user \"postgres\"",
    "timestamp": "2023-12-04T14:18:14.544000"
  }]
[  {
    "error_severity": "FATAL",
    "event_message": "unsupported frontend protocol 0.0: server supports 3.0 to 3.0",
    "timestamp": "2023-12-04T16:55:31.290000"
  },
  {
    "error_severity": "FATAL",
    "event_message": "unsupported frontend protocol 65363.19778: server supports 3.0 to 3.0",
    "timestamp": "2023-12-04T15:32:21.287000"
  },
  {
    "error_severity": "FATAL",
    "event_message": "password authentication failed for user \"postgres\"",
    "timestamp": "2023-12-04T14:18:14.544000"
  }]
Have y'all ever seen this unsupported frontend protocol 0.0: server supports 3.0 to 3.0 issue with postgres? I also changed my database's password since it looks like auth fails sometimes
Andrii Sherman
Andrii Sherman•10mo ago
never saw this one seems like it's postgre.js driver issue have you seen any on their GH issues page?
Andrii Sherman
Andrii Sherman•10mo ago
nothing
No description
Boxer
Boxer•10mo ago
Yeah weird, found some explanation https://pganalyze.com/docs/log-insights/connections/C31. Could be my host as well
Budi
Budi•9mo ago
@Boxer did you try the new Supavisa connection Pooler? Might resolve things
Boxer
Boxer•9mo ago
Which one?
Budi
Budi•9mo ago
For your Supabase connection string
Want results from more Discord servers?
Add your server