Using Postgres function in an insert with a field as a select
I'm trying to translate the following SQL:
```sql
INSERT INTO sb.text_document_chunk (text_document_id, text)
SELECT NEW.id, sentence...
create role in migration
Is it possible to create a role in a migration? And to do the grants and things as part of that?
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?...Querying two different tables with subset of common columns
I have two tables that have an intersection of common columns that I'm looking to query. For each of the tables, I have identical conditional logic (
.wheres) that I'd like to apply for both queries, and it's quite extensive so I'd prefer not to copy and paste. My query would only .select the common columns.
Is there any Kysely-ic way of doing this?...How to select from a function with parameters?
I understand that
I created a helper:...
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>)
In a transaction, how do you ignore generated field requirements?
```
export interface TextDocumentTable {
id: Generated<number>;
created_at: Generated<Date>;
}...
Solution:
im assuming id/created_at are nullable on
documentCross database joins in MySQL
Hi all,
Is it possible to do joins across databases within the same MySQL instance?
i.e
```sql
SELECT * FROM db1.table1 INNER JOIN db2.table2 ON db1.table1.id = db2.table2.id...
.where('x', 'is not', null) and correspond type's nullability
Not sure if this even possible in typescript (although all of the work Kysely already makes it seem magical). But let's say we have a column 'x' that has type
string | null. Is it possible to write a query that ends up resulting in a final type of string after using .where('x', 'is not', null) or any similar method?Solution:
Hey 👋
Due to compiler performance and type maintainability, Kysely doesnt narrow the type in this case. We've recently introduced the
.$narrowType that allows you to narrow the output type safely....What is the suggested way of adding/removing methods to expression builders?
Context:
I am building a custom dialect for YDB https://github.com/Gaspero/kysely-ydb
YDB is slightly different from standart dialects. E.g. create/drop index expressions are a part of alter table expression (ALTER TABLE ... ADD/DROP INDEX) https://ydb.tech/en/docs/yql/reference/syntax/alter_table#secondary-index
There are also other differences that are not as critical, but I am thinking of the ways how I can protect library users from writing broken SQL queries. E.g. Insert/Replace/Update/Upsert do not support returning values and thus do not support RETURNING expression; CREATE TABLE expression only supports primary key constraint.
...
Solution:
Context:
I am building a custom dialect for YDB https://github.com/Gaspero/kysely-ydb
YDB is slightly different from standart dialects. E.g. create/drop index expressions are a part of alter table expression (ALTER TABLE ... ADD/DROP INDEX) https://ydb.tech/en/docs/yql/reference/syntax/alter_table#secondary-index
There are also other differences that are not as critical, but I am thinking of the ways how I can protect library users from writing broken SQL queries. E.g. Insert/Replace/Update/Upsert do not support returning values and thus do not support RETURNING expression; CREATE TABLE expression only supports primary key constraint.
...
Running database agnostic queries (MySQL)
As part of my test suite, I drop/create databases programmatically. Is it possible to have a Kysely connection that isn't attached to a specific database, so that I could run queries like
DROP DATABASE a or CREATE DATABASE a?
Other similar use cases include:
RESET QUERY CACHE, SELECT 1 (for latency check), etc...Asserting type of .countAll() (MySQL)
I've noticed that the return type from the result of aggregation functions like
countAll() and sum() are string | number | bigint.
I assume this is in-case the number returned is too large to be stored in a JS "number." In my case, it appears to be being returned as strings. Is it possible to assert it as a number so that I don't need to wrap every query in a parseInt(result as string, 10)?...Solution:
nvm got it
```ts
await db
.selectFrom("player_stats")
.where("last_active", ">", dateThreshold)...
Coalesce return empty array
Hey is there a way to return an empty array rather than
null when an array column is empty?
I already tried
``ts
.select(coalesce("categories",sqlARRAY[]::"CollectionCategory"[]`).as("categories"))...kysely-codegen for multiple databases
I have two MySQL 'databases' within the same instance. From the documentation, it doesn't look like the codegen has innate support for multiple databases with different schemas?
I could codegen with the two different connection strings with a different
--out-file, rename the exported interfaces, then manually update index.d.ts, but I was wondering if there's a better solution?...Solution:
Hey 👋
type file & kysely instance per database is what I would go for....
Using MySQL functions in SELECT statement
Hi!
Just trying to migrate over from Knex. Skimmed over the documentation but still unsure of how to replicate this query in Kysely, or might be looking in the wrong place? In Knex, I'd often have to use
selectRaw for these
SELECT a, b, UPPER(c) FROM x;...Extract OrderBy TS Keys
Hey I have the following query
```ts
const getBaseQuery = ({ offset, pageSize, slug }: GetBaseQuery) =>
db...
insert into with mix of static and table values
How might I execute an insert into that combines JS-side values with a select, like:
...
INSERT INTO some_table (a, b, c, d) SELECT o.a, o.b, 16, 'hello world' FROM other_table O WHERE somekey = 32;
INSERT INTO some_table (a, b, c, d) SELECT o.a, o.b, 16, 'hello world' FROM other_table O WHERE somekey = 32;
Migration error "TypeError: Cannot read properties of undefined (reading 'getExecutor')"
I am trying to run a migration using Kysely, and its returning this error:
```
file:///Users/brunocruz/code/look-manager/node_modules/kysely/dist/esm/raw-builder/raw-builder.js:61
: executorProvider.getExecutor();
^...
where clause with length
How do I have a where clause with a length, e.g.
select * from data where length(prodcode) = 3...show generated sql
how can i see what the generated sql is for a kysely query? e.g.
```
result = await db.selectFrom("data")
.select(['partner_cd', 'reporter_cd', 'product_cd', 'period'])...
Property does not exist on type
Hello.
I'm trying to run the following query:
```javascript...
Solution:
Hey 👋
What kysely version are you using? cmpr (and other cool new expression builder stuff) is only available from 0.24.x...