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...
Complex Query Builder from dynamic input - Typescript issue
Hi, it's me - again 🙂
I'm currently trying to migrate our current query builder.
It seems that everything is working as expected, but I get some type errors and I'm not sure how to fix this - Maybe you have an idea how to fix this / what the rootcause is....
Solution:
Hey 👋
This is a bit harder to grok.
What's immediate is that
conditionBuilder might return undefined and .where expects something....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!...
Solution:
Hey 👋
Kysely supports column comments in introspectors. This should allow external codegen tools (e.g. kysely-codegen) to generate types with jsdocs.
Builders don't have built-in support for adding comments on tables or columns. You can use
modifyEnd where possible to add comments with sql template tag....Need help improving a custom helper
My dopamine with Kysely is quite high right now, so I am experimenting all sorts of things to create custom helpers for common use-cases.
Here's an example of a working demo https://old.kyse.link/?p=s&i=qkTHjgwIuyb847wjoRd3
Following is a trimmed snippet from the above mentioned demo, for which I need help....
Solution:
Yeah unfortunately typescript doesn't bend that way. We wouldn't need the whole
ExpressionBuilder callback thingy anywhere if it did. We could just use free functions and they'd somehow catch the context through generics in the return value.
another option is month(eb)('created_at')....jsonArrayFrom with `as` not being typed
I have the following query
```ts
await db
.selectFrom('user')
.selectAll()...
Solution:
so Im using deno, and it uses import maps to import deps, I have these 2 import paths set up:
I forgot to bump the helper path version to 0.27.3 as well 🤦♂️...
How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )
Hey guys,
I'm currently working on a new version for our graphql wrapper.
Currently we're using knex to generate the query - it works as expected but isn't typesafe with how we use it 🙈
...
Solution:
You could actually get away with:
```ts
const test = db
.selectFrom<"person">(...
Using raw SQL with `or` where
I have been using the raw SQL template tag to work with certain JSON columns and wanted to add a
orWhere clause using it. Here is a simple example of what I am trying to achieve.
```ts
const result = await db
.selectFrom('users')...Solution:
And you're right,
eb.or expects a list of Expression<SqlBool> where SqlBool is boolean | 0 | 1