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')...Type-only imports in migration files
In the Kysely documentation, an example is given of a migration file which imports
Kysely from the kysely package. This import is only used to type the up and down functions, but not as a value. Depending on the TypeScript verbatimModuleSyntax compiler option, this may or may not result in a runtime import in the compiled code, which in the worst case could mean different behaviors of the migration script.
My question is: Is it safe to import Kysely with a type-only import in migration files? If not, what side effects of the import are needed in such files?
If the idea (as I suspect) is indeed only to import the Kysely type for typing the function arguments, I suggest also that the example should be changed to use a type-only import or accompanied by a note about the possibility of such an import....Solution:
Hey 👋
It is safe to type-only....
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>({...
generating raw sql?
I think I saw somewhere an option that you can build a query using kysely and convert it to raw sql, but I can't find that anywhere. Does such an option exist or did I get something mixed up?
Solution:
Hey 👋
Use the
.compile() method instead of executing.
For more info, check out the link @53ny posted....Conditionally updating fields
How would i go about doing something like
UPDATE table SET field = IF(condition, trueValue, falseValue) WHERE someCondition;, i can't exactly seem to find that in the docs
Driver used: MySQL...Solution:
Hey 👋
Something like:
```ts...
How to cast to date. eg : `DATE(created_at)`
How to write this query in kysely
```sql
SELECT DATE(created_at) AS date, SUM(points) AS score
FROM table
WHERE id = '...' ...
Solution:
Hey 👋
Does this work for you?
```ts...
transaction takes too long
I have a problem with transactions. What might be the reason that db.transaction() adds more than 10ms latency?
```
Query took 2.3298759999997856
Validation took 0.006788000000597094
Update took 0.6013680000005479...
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....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...