Accessing underlying methods on the client
Hi. I'm using libsql and I want to batch statements to be sent to the database. The underlying clients have these methods implemented, so I was wondering if it were possible to access these underlying methods from the
Kysely class itself, or if it is necessary for me to write my own extension of the class to achieve thisIs there an easy tool to convert my existing knex migrations to kysely migrations?
I have a bunch of knex migrations in following format:
```
.createTable("state", (table: Knex.TableBuilder) => {
table.bigIncrements("id").unsigned().primary();
table.string("abbreviation", 2).notNullable();...
What am I not understanding about ColumnType?
(Apologize if this is very nooby I am new to kysely as of today)
Used kysely-codegen and it spit out something like the following:
```export type Int8 = ColumnType<...
Solution:
So I figured out through combing through kysely-codegen closed issues that there is a differnece between the out-of-the-box kysely Generated type and the Generated type I was removing (that I thought was extra) that gets generated with codegen. When I restored for the codegen one the compilation issue went away.
For reference current codegen type:
```export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
? ColumnType<S, I | undefined, U>...
How to do `LIKE ANY` query in Kysely?
Hello, I am trying to build this query in Kysely but have no idea how to properly insert the
ANY into the query.
```sql
SELECT *
FROM your_table
WHERE your_column LIKE ANY (ARRAY[...Creating snippets (best pratice)
How can I create query snippet once and add it to multiple queries?
```ts
const querySnippet = kysely; // create once and add to both count and rows query instead of repeating the same code twice?
...
Solution:
But if you want a reusable helper, you need to let go of some type-safety. It's very difficult and often impossible to create a generic and strict helper. You can do something like this
```ts
db.selectFrom('whatever').select('foobar').where(myLovelyFilter(searchPostalCode))
...
How to order by desc using expression builder with case
I am ordering by using case but cannot find a way how to add
desc to the eb.
```ts
const person = await db
.selectFrom("person")...Solution:
Hey 👋
Try this:
```ts...
Is it possible to do "for await (const iterator of db.selectFrom("table").selectAll()) {....}
Can I execute as an async generator like in a cursor ?
Solution:
Hey 👋
Have you tried https://kysely-org.github.io/kysely-apidoc/interfaces/SelectQueryBuilder.html#stream ?...
Question about ColumnTypes and dates.
I have a simple yet confusing problem. In postgres I have a table with a column of type
DATE (yyyy-mm-dd).
```sql
CREATE TABLE IF NOT EXISTS person(
id INT NOT NULL,...Solution:
And yes, the "select type" is currently used as the return type AND the query type. We could add a fourth "query" type to
ColumnType which would be used in where statements (and other similar statements).
But then subqueries in where statements would break if the select type and the query type don't match....How to type tables with dynamic names?
The MS SQL dialect only supports global temporary objects, that forces us to add random table suffix to prevent collisions between sessions.
Is it possible to type the table contents as a second query?
Conceptually my code runs like this:...
Solution:
Hey 👋
A workaround would be to use the same table name in
.withTables and in the queries, and implement a plugin that on-the-fly adds the random suffix to all references to that table....clone select query
Hello
I need to 2 queries in parallel like this (select data + count)
`let baseQuery = db.selectFrom('event')
...
Solution:
No need to clone. The query builder is immutable. Every method call returns a clone (effectively. the implementation is more clever). But you can imagine there's a clone before every method call.
Any limits on insertValues(array)?
I am seeding data in migrations and received error when trying to insert over 35.000 rows at once.
```typescript
const rows = [ // there are 35.000 objects here]
await db...
Solution:
Just insert in chunks
Migration to seed huge amount of data
Hi, are there any guides or suggestions how to effectively seed huge amount of data with migrations? We have the data in separate json (or csv) files which are copied to "assets" folder in the dist folder. How do you do this?
Creating a jsonAgg helper funtcion that returns a context aware `AggregateFunctionBuilder`
Greetings. I am wondering if it's possible to create a helper that does exactly what
eb.fn.jsonAgg does but for JSONB.
- I have tried using the helpers/postgres/jsonArrayFrom, but that is functionally not the same because it creates a subquery.
- I have tried creating a simple helper, but it's not type/context aware, and it doesn't support method chaining (.distinct() for example):
...How to add array column in migrations?
I am trying to add a column to a table via migrations but cannot find how to do something like this:
.addColumn('countyNames', 'varchar', col => col.IsArray() )...
Solution:...
.addColumn('countyNames', sql`varchar[]`)
.addColumn('countyNames', sql`varchar[]`)
are conditional CTEs possible?
i don't find any hints here https://kysely.dev/docs/category/cte.
i have tried the conditional where approach (https://kysely.dev/docs/examples/WHERE/conditional-where-calls), but i get type error
Type 'QueryCreatorWithCommonTableExpression ... The types returned by 'with(...)' are incompatible between these types....Solution:
No, conditional CTEs are not possible. But you could construct your CTE so that it doesn't do anything based on some input.
Dynamic conditional raw query question
Hello there, Im trying to create a dynamic query like this:
``ts
await sqlSELECT
column AS "data"
FROM my_table ...Update on conflict clause
Hi. I need to use the on conflict clause in an update query within sqlite. I've searched the docs in mysql and it isn't present there. I created an example below for replication of the desired behavior. The only thing that I can think of is using the template sql`` operator from kysely, but is there a better way?
```sql
CREATE TABLE ReportCategory (
reportId TEXT,
categoryId TEXT,...
SelectAll Overwrites column with same field name in a join
Hello, very odd issue here. I have a table Course with field 'id' and User with field 'id'. When I join these two tables and SelectAll(['Course', 'User']), there's only a single id field, with the value from User. Is this a bug, or am I doing something wrong? (Detailed snippets below).
```ts
const result = await db...
Solution:
Kysely is not an ORM. It doesn't magically rename things. You need to provide non-colliding names.
concat in postgres
Hey! I want to concat few columns to get address, however query is built in wrong way cuz postgres uses
|| to concat. What am I doing wrong?
```
let warehouses = await db
.selectFrom('warehouse as w')...