Theo Gravity Posts - Answer Overflow

Theo Gravity

Explore posts from servers

•Created by Theo Gravity on 6/12/2023 in #❓-help

How to use the errorFormatter when using an adapter?

The example here uses context.create(), which isn't something you do when using an adapter. I don't see an errorFormatter exposed in createExpressMiddleware() https://trpc.io/docs/server/error-formatting

4 replies

TtRPC

•Created by Theo Gravity on 6/10/2023 in #❓-help

How do I log thrown errors from the query/ mutation on the server side globally?

I was surprised to find that the server servicing tRPC requests do not log any thrown errors from a query / mutation to the console by default (but the client does capture errors). I want to be able to log all errors on the server side. How would I do this? This page doesn't really give me any clues as I don't know what trpcNext.createNextApiHandler refers to: https://trpc.io/docs/server/error-handling#handling-errors I am using the express adapter with tRPC. I do not want to have to attach an error handling function to each query / mutation I define.

9 replies

KKysely

•Created by Theo Gravity on 5/26/2023 in #help

How to translate the WITH keyword in postgres when SELECT does not have FROM?

I'm trying to translate the following postgres function into pure Kysley:

CREATE OR REPLACE FUNCTION rand.similarity_search(model TEXT, needle TEXT)
RETURNS TABLE (
  document_chunk_id INTEGER,
  similarity REAL,
  text TEXT
) AS $$
BEGIN
  RETURN QUERY
  WITH query AS (
      SELECT pgml.embed(model, needle) AS embedding
  )
  SELECT document_chunk_embedding.document_chunk_id, pgml.cosine_similarity(document_chunk_embedding.embedding, query.embedding), document_chunk.text AS similarity
  FROM rand.document_chunk_embedding
  JOIN rand.document_chunk ON document_chunk_embedding.document_chunk_id = document_chunk.id, query
  ORDER BY similarity DESC
  LIMIT 5;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION rand.similarity_search(model TEXT, needle TEXT)
RETURNS TABLE (
  document_chunk_id INTEGER,
  similarity REAL,
  text TEXT
) AS $$
BEGIN
  RETURN QUERY
  WITH query AS (
      SELECT pgml.embed(model, needle) AS embedding
  )
  SELECT document_chunk_embedding.document_chunk_id, pgml.cosine_similarity(document_chunk_embedding.embedding, query.embedding), document_chunk.text AS similarity
  FROM rand.document_chunk_embedding
  JOIN rand.document_chunk ON document_chunk_embedding.document_chunk_id = document_chunk.id, query
  ORDER BY similarity DESC
  LIMIT 5;
END;
$$ LANGUAGE plpgsql;

I'm able to translate the main select body:

  return db.selectFrom('document_chunk_embedding')
    .innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
    .select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
    .orderBy('similarity', 'desc')
    .limit(5)
    .returningAll()
    .execute();

  return db.selectFrom('document_chunk_embedding')
    .innerJoin('document_chunk', 'document_chunk_embedding.document_chunk_id', 'document_chunk.id')
    .select(['document_chunk_embedding.document_chunk_id', 'document_chunk.text as similarity', db.fn('pgml.cosine_similarity', ['document_chunk_embedding.embedding', 'query.embedding'])])
    .orderBy('similarity', 'desc')
    .limit(5)
    .returningAll()
    .execute();

But I'm not sure how to translate the WITH part:

  WITH query AS (
      SELECT pgml.embed(model, needle) AS embedding
  )

  WITH query AS (
      SELECT pgml.embed(model, needle) AS embedding
  )

9 replies

KKysely

•Created by Theo Gravity on 5/25/2023 in #help

Using Postgres function in an insert with a field as a select

I'm trying to translate the following SQL:

INSERT INTO sb.text_document_chunk (text_document_id, text)
SELECT NEW.id, sentence
FROM unnest(string_to_array(NEW.text_content, '. ')) AS sentence
WHERE sentence ~ '[[:alnum:]]';

INSERT INTO sb.text_document_chunk (text_document_id, text)
SELECT NEW.id, sentence
FROM unnest(string_to_array(NEW.text_content, '. ')) AS sentence
WHERE sentence ~ '[[:alnum:]]';

to:

db.insertInto('text_document_chunk').values((eb) => ({
    text_document_id: document.id,
    text: db
      .selectFrom(eb.fn('unnest', [eb.fn('string_to_array', [document.text_content, '. '])]).as('sentence'))
      .where('sentence', '~', '[[:alnum:]]'),
  })).execute()

db.insertInto('text_document_chunk').values((eb) => ({
    text_document_id: document.id,
    text: db
      .selectFrom(eb.fn('unnest', [eb.fn('string_to_array', [document.text_content, '. '])]).as('sentence'))
      .where('sentence', '~', '[[:alnum:]]'),
  })).execute()

It doesn't seem to like [document.text_content, '. '] and where('sentence'). It seems the array value has to be a ReferenceExpression type, but the documentation isn't very clear on what that is.

20 replies

KKysely

•Created by Theo Gravity on 5/23/2023 in #help

How to insert JSONB types in postgres?

I've looked at https://kysely-org.github.io/kysely/classes/InsertQueryBuilder.html#values and it's not very clear how I'd apply JSON to that column type when inserting. Do I need to create some sort of special helper like in: https://github.com/kysely-org/kysely/blob/master/site/docs/recipes/extending-kysely.md Also for the interface, do we define the field as a Record type? What if the field is optional?

12 replies

KKysely

•Created by Theo Gravity on 5/22/2023 in #help

How to select from a function with parameters?

I understand that kysely will not natively support stored procedures / functions, and was wondering if there's a pattern for this:

select * from function_name(<parameters>)

select * from function_name(<parameters>)

I created a helper:

export function similaritySearch(
  text: string,
  model: string,
) {
  return sql`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

export function similaritySearch(
  text: string,
  model: string,
) {
  return sql`similarity_search(${sql.lit(text)}, ${sql.lit(model)})`;
}

But, I'm not exactly sure how I can use that since db.selectFrom() expects a table name.

4 replies

KKysely

•Created by Theo Gravity on 5/22/2023 in #help

In a transaction, how do you ignore generated field requirements?

export interface TextDocumentTable {
  id: Generated<number>;
  created_at: Generated<Date>;
}

export interface TextDocumentChunkTable {
  id: Generated<number>;
  text_document_id: number;
  created_at: Generated<Date>;
  text: string;
}

export type TextDocumentRow = Selectable<TextDocumentTable>;
export type TextDocumentChunkRow = Selectable<TextDocumentChunkTable>;

export interface TextDocumentTable {
  id: Generated<number>;
  created_at: Generated<Date>;
}

export interface TextDocumentChunkTable {
  id: Generated<number>;
  text_document_id: number;
  created_at: Generated<Date>;
  text: string;
}

export type TextDocumentRow = Selectable<TextDocumentTable>;
export type TextDocumentChunkRow = Selectable<TextDocumentChunkTable>;

await this.db.transaction().execute(async (trx) => {

  // 2nd param is TextDocumentRow
  const insertedTextDocument = await insertTextDocument(trx, document);

  // 2nd param is TextDocumentChunkRow
  await insertTextDocumentChunk(trx, {
    text_document_id: insertedTextDocument.id,
    text: document.text_content,
  });
});

await this.db.transaction().execute(async (trx) => {

  // 2nd param is TextDocumentRow
  const insertedTextDocument = await insertTextDocument(trx, document);

  // 2nd param is TextDocumentChunkRow
  await insertTextDocumentChunk(trx, {
    text_document_id: insertedTextDocument.id,
    text: document.text_content,
  });
});

Typescript will complain that insertTextDocumentChunk needs id and created_at even though they are Generated values.

9 replies

Gaming

Programming