lukas.slezevicius Posts - Answer Overflow

lukas.slezevicius

•Created by lukas.slezevicius on 7/5/2024 in #help

Advice for debugging timeout/connection issues with Kysely

Hi everyone, We've been using Kysely with pg in prod in our startup for over a year now and everything has been mostly stable, however, we started encountering these random errors with no seemingly obvious reason. I am 99.9% sure that this is not a Kysely specific issue, but I'd be grateful if someone could advice us on how to debug these issues, as we don't have much experience in this. Googling revealed some stack overflow threads were timeout configs were discussed, but that didn't help us too much. The error is: connect ETIMEDOUT 20.208.39.81:5432. The weird thing is that all the DB metrics are stable - memory, cpu, connections, etc. Raising timeout values, or even making them indefinite does not seem to help. We don't have too much data (2gb) and we rarely have queries that take more than a second to complete. We set up our client like this:

const pool = new pg.Pool({
  application_name: "Octocom Backend",
  connectionString: env.DB_URL,
  max: env.DB_POOL_SIZE,
  statement_timeout: 30_000,
  idle_in_transaction_session_timeout: 60_000,
});

pool.on("connect", (client) => {
  void client.query("SET lock_timeout = 30000");
});

const db = new Kysely<DB>({
  log: env.DB_DEBUG ? console.log : undefined,
  dialect: new PostgresDialect({
    pool,
  }),
  plugins: [new CamelCasePlugin()],
});

const pool = new pg.Pool({
  application_name: "Octocom Backend",
  connectionString: env.DB_URL,
  max: env.DB_POOL_SIZE,
  statement_timeout: 30_000,
  idle_in_transaction_session_timeout: 60_000,
});

pool.on("connect", (client) => {
  void client.query("SET lock_timeout = 30000");
});

const db = new Kysely<DB>({
  log: env.DB_DEBUG ? console.log : undefined,
  dialect: new PostgresDialect({
    pool,
  }),
  plugins: [new CamelCasePlugin()],
});

Once again, we'd appreciate any pointers!

1 replies

KKysely

•Created by lukas.slezevicius on 5/6/2024 in #help

Typing reusable functions for filtering rows

Hi all, we're using Kysely at our company and loving it! We recently encountered some issues when trying to type reusable functions. One example of our current attempt:

function applyFilters<T>({
  query,
  ...args
}: {
  query: SelectQueryBuilder<DB, "conversations", T>;
} & ConversationFilters): SelectQueryBuilder<DB, "conversations", T> {
  if (args.businessId) {
    query = query.where("businessId", "=", args.businessId);
  }
  ...
  return query;
}

function applyFilters<T>({
  query,
  ...args
}: {
  query: SelectQueryBuilder<DB, "conversations", T>;
} & ConversationFilters): SelectQueryBuilder<DB, "conversations", T> {
  if (args.businessId) {
    query = query.where("businessId", "=", args.businessId);
  }
  ...
  return query;
}

This works when our initial query is basic like this:

let query = getDb().selectFrom("conversations");

let query = getDb().selectFrom("conversations");

However, it stops working when we add some joins (which shouldn't break the original filtering function anyway):

let query = getDb()
      .selectFrom("conversations")
      .leftJoin(
        "contactConversations",
        "contactConversations.conversationId",
        "conversations.id",
      )

let query = getDb()
      .selectFrom("conversations")
      .leftJoin(
        "contactConversations",
        "contactConversations.conversationId",
        "conversations.id",
      )

If we update the SelectQueryBuilder middle type to be "conversations" | "contactConversations", it works with neither the simpler, nor the more "complex" query. I'd be thankful for any pointers and general tips on how to type reusable functions!

6 replies

KKysely

•Created by lukas.slezevicius on 4/18/2024 in #help

Syntax error when empty array is passed to a WHERE filter (request for more readable errors)

In a query like someQuery.where("x", "in", xs), if xs is empty, then the error will be something like error: syntax error at or near ")". Given that in complex queries there can be multiple places that could cause such an issue, debugging it is sometimes painful. I understand that it's SQL that is throwing the error and empty arrays are not possible, but would it be feasible to do a runtime check by the lib and throw a more readable error in case that happens? Or would this be against the philsophy of Kysely or too much overhead? Also, I think a fallback of 1=0 might make even more sense, since that should be the logical output of the query with an empty array.

2 replies

KKysely

•Created by lukas.slezevicius on 4/16/2024 in #help

Derived tables for Postgres

Hi, I am trying to do a bulk update like this:

UPDATE your_table
SET someColumn = new_values.new_value
FROM (VALUES
    (1, 'new_value_for_row_1'),
    (2, 'new_value_for_row_2')
    -- Add more rows as needed
) AS new_values (id, new_value)
WHERE your_table.id = new_values.id;

UPDATE your_table
SET someColumn = new_values.new_value
FROM (VALUES
    (1, 'new_value_for_row_1'),
    (2, 'new_value_for_row_2')
    -- Add more rows as needed
) AS new_values (id, new_value)
WHERE your_table.id = new_values.id;

I found that UpdateQueryBuilder supports update joins with from method, but I couldn't find how to create derived tables like in the pure SQL shown above. Thanks!

2 replies

KKysely

•Created by lukas.slezevicius on 4/12/2024 in #help

How to add table/column comments when creating tables?

Hey, we're approaching close to 100 tables in our project. New people will be joining our founding team and we thought documenting the tables using the Postgres table/column commenting feature would make it easier to understand the DB schema. After searching the docs, discord, and google, I couldn't find any info on how to do this in Kysely. Should we resort to raw SQL? Thanks!

6 replies

KKysely

•Created by lukas.slezevicius on 11/15/2023 in #help

Reusable CTEs that depend on previous CTEs

I have 2 CTEs that I'd like to separate out into separate functions for readability. These are the CTEs:

.with("eligibleChats", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId"),
)
.with("lastMessages", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin(
      "eligibleChats",
      "eligibleChats.chatId",
      "chatMessages.chatId",
    )
    .where("chatMessages.position", "=", ({ selectFrom }) =>
      selectFrom("chatMessages as innerChatMessages")
        .whereRef("innerChatMessages.chatId", "=", "chatMessages.chatId")
        .select(({ fn }) =>
          fn.max("innerChatMessages.position").as("maxPosition"),
        ),
    )
    .select(["chatMessages.position", "chatMessages.chatId"]),
)

.with("eligibleChats", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId"),
)
.with("lastMessages", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin(
      "eligibleChats",
      "eligibleChats.chatId",
      "chatMessages.chatId",
    )
    .where("chatMessages.position", "=", ({ selectFrom }) =>
      selectFrom("chatMessages as innerChatMessages")
        .whereRef("innerChatMessages.chatId", "=", "chatMessages.chatId")
        .select(({ fn }) =>
          fn.max("innerChatMessages.position").as("maxPosition"),
        ),
    )
    .select(["chatMessages.position", "chatMessages.chatId"]),
)

I know I can extract the first one like this:

const eligibleChatsCte = (db: QueryCreator<DB>) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId");

const eligibleChatsCte = (db: QueryCreator<DB>) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId");

But if I do the same for the second one, it won't be able to reference the chatMessages table . Is there a way to do that? The most relevant docs I found were https://kysely.dev/docs/recipes/expressions, but it doesn't cover this usecase. Thanks in advance!

7 replies

KKysely

•Created by lukas.slezevicius on 11/14/2023 in #help

Combining selectAll and arbitrary expressions

Hey! Is it possible to combine selectAll (for selecting all columns of a couple of tables) and then add a couple of expressions like jsonArrayFrom on top of that?

7 replies

Gaming

Programming