brynthrax Posts - Answer Overflow

brynthrax

•Created by brynthrax on 10/7/2024 in #help

Conditionally return OR execute drizzle query

How can I achieve something like this (see sample code below)? my use case is that I have a base repository with CRUD methods that abstract over drizzle, but sometimes, I only need to return the query in cases where I need to extend it (e.g., override where condition, etc.).

async function someMethod(record: any, queryOnly: boolean) {
  const query = db.insert(entity).values(record);
  if (queryOnly) {
    return query.$dynamic();
  }

  const result = await query
  return result
}

async function someMethod(record: any, queryOnly: boolean) {
  const query = db.insert(entity).values(record);
  if (queryOnly) {
    return query.$dynamic();
  }

  const result = await query
  return result
}

1 replies

DTDrizzle Team

•Created by brynthrax on 3/24/2024 in #help

Typed “with” relations config on relational query builder

How can I properly get the proper relation type (with) of a specific table/entity? For example, I would like to achieve the following with proper typings: const patientRelations = { relation1: true, relation2: { columns: { col1: true, col2: true } } }

1 replies

DTDrizzle Team

•Created by brynthrax on 3/18/2024 in #help

Batch query in better-sqlite3

Is there a way to do batch queries in better-sqlite3 such as in LibSQL, Neon, or D1? Thanks 🙂

1 replies

DTDrizzle Team

•Created by brynthrax on 3/17/2024 in #help

Application-defined functions/UDF in LibSQL

Is there a way to define JS functions within libsql's sqlite database just like in better-sqlite? (see ref below) db.function('add2', (a, b) => a + b); db.prepare('SELECT add2(?, ?)').pluck().get(12, 4); // => 16 db.prepare('SELECT add2(?, ?)').pluck().get('foo', 'bar'); // => "foobar" db.prepare('SELECT add2(?, ?, ?)').pluck().get(12, 4, 18); // => Error: wrong number of arguments https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#functionname-options-function---this

3 replies

DTDrizzle Team

•Created by brynthrax on 10/8/2023 in #help

Is it possible to use INSERT statements inside a CTE (WITH query) +++ another question?

In PostgreSQL, you're allowed to use other DML statements (e.g., insert, update, delete) inside a CTE. Something like:

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

It's also optional if you want to include RETURNING or not. 1st question is, can this kind of query be accomplished in Drizzle? Or is it only limited to SELECT statements? 2nd question is, how can I execute a transaction in Drizzle in such a way that the whole transaction block is sent as one whole statement to the database - in just one round trip? Something similar to LibSQL Batch API. For this use case, I don't have any JavaScript code/logic between each statement inside the transaction. (I'm using PostgreSQL) Current behavior I've observed is that each statement incurs a round trip. So if there are around 5 statements inside the transaction, then I think that corresponds to 7 total trips to the database (5 + including BEGIN and COMMIT). I want to reduce latency. Thanks! https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

1 replies

DTDrizzle Team

•Created by brynthrax on 8/11/2023 in #help

How to flatten select results from query with left joins (one-to-many)?

Is there a way to achieve this? Current behavior in drizzle works like this (assuming we're printing the variable "result" which is supposed to be an array of person with all their hobbies, one to many:

// NOTE: I've read a portion of the docs and it says something like //using a reduce function. I'm looking for a solution that is more // of an abstraction that works out of the box - just like how
// things are when you use a relational query (i.e., findMany)
[
  {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H1", ... }
  },
 {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H2", ... }
  },
{
    person: {
      id: "P3",
      name: "Bob Smith"
      ...
    }
    hobby: { id: "H5", ... }
  }
]

// NOTE: I've read a portion of the docs and it says something like //using a reduce function. I'm looking for a solution that is more // of an abstraction that works out of the box - just like how
// things are when you use a relational query (i.e., findMany)
[
  {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H1", ... }
  },
 {
    person: {
      id: "P1",
      name: "Jane Doe"
      ...
    }
    hobby: { id: "H2", ... }
  },
{
    person: {
      id: "P3",
      name: "Bob Smith"
      ...
    }
    hobby: { id: "H5", ... }
  }
]

What I want to achieve is an array wherein these relations are flattened, such as:

[
  {
    "id": "P1",
    "name": "Jane Doe"
    "hobby": [
      { "id": "H1", ...  },
      { "id": "H2", ...  }
    ],
    ...
  },
  {
    "id": "P2",
    "name": "Bob Smith"
    "hobby": [
      { "id": "H5", ...  }
    ],
    ...
  }
]

[
  {
    "id": "P1",
    "name": "Jane Doe"
    "hobby": [
      { "id": "H1", ...  },
      { "id": "H2", ...  }
    ],
    ...
  },
  {
    "id": "P2",
    "name": "Bob Smith"
    "hobby": [
      { "id": "H5", ...  }
    ],
    ...
  }
]

18 replies

Gaming

Programming