Updating JSON object value in column
Hello, Im trying to wrap my head around how to update a JSON column were I want to update the value not by replacing the whole object but by setting the specific field in the JSON using:
```sql
set metadata = jsonb_set(metadata, '{root,nested}', '"value"');
...
Kysely Geometry Parsing Plugin - Convert driver values to/from geojson
0
I've currently written a rough prototype for parsing geometry. Most orms/query builders allow you to provide a mapValueToDb & mapValueFromDb based on a column type. However, kyseley doesn't really seem to have that.
I found transformQueryResponse inside a plugin example, so I've written one of those, of which I had to loop over every value (nested too) that could be a buffer and then parse it to geometry. So I've used this to handle parsing from DB to client, however transforming it back to the DB without knowing the column type was going to be near impossible....
Ad-Hoc select from table not in Kysely types
Hey, we are dynamically generating our KyselyDB types using the
kysely-codgen cli, and want to know how one can ad-hoc use the selectFrom statement from a table not included in the generated types by typing it ourselves as we build the query?Convert JSON array result into JS array
Hello, I am running an SQL query that returns data in this format since it is a PG JSON array:
```
{
rows: [
{...
Destroying connection necessary?
Hello! If we are using pooling via pgbouncer and using kysely from our edge functions, is it necessary to explicitly destroy the kysely instance/connection at the end to the function?
Im asking since at the moment if an error is thrown, the instance is never explicitly destroyed. In local dev we dont have pgbouncer so we connect directly to the DB. After a while during dev and testing, the DB give a too many connections error....
how to insert an array of strings
hey guys,
I wanted to know how to store an array of strings with kysely, I created my table using migrations:
```...
Typescript Error when creating MySQL db pool
Hi Everyone,
I'm getting the following error when creating a database:
```
kysely Type '() => Promise<Pool>' is not assignable to type 'MysqlPool | (() => Promise<MysqlPool>)'...
Migrate beyond 9 (to `10-...`)
Hey, I am trying to run a new migration called
...
10-passport but I am always getting this error:
Error: corrupted migrations: expected previously executed migration 2-userguilds to be at index 2 but 10 was found in its place. New migrations must always have a name that comes alphabetically after the last executed migration.
Error: corrupted migrations: expected previously executed migration 2-userguilds to be at index 2 but 10 was found in its place. New migrations must always have a name that comes alphabetically after the last executed migration.
Transaction response time
Hey everyone, in my code I have a
createUser(), createProfile(), createBusiness(), and createAddress() for this example.
Currently, I have creaeUser() execute on it's own and then the other 3 free functions execute as one transaction. Which gives me the benefit of having a fast response time for when a user creates an account. (the loading screen is quick) but I don't know if that benefit outweighs the risk of having user records in the database without a profile, business, and/or address records - *if for some reason the transaction fails but the createUser() executed fine.
...How to handle failed transactions
Hey everyone, this is not directly related to Kysely but I was wondering what is a common solution for handling failed transactions? - is it supposed to be logged somewhere so you can review it?
How to generate query synchronously?
Besides other things I want to use kysely to generate subqueries for sequelize scopes. Is it possible to generate the query synchronously? Sequelize scopes do not accept async calls.
How to write "SELECT 1 FROM tablename"
I want to create subquery for exists but don't know type select(1)...? should I use val or sql.lit or other way...? Thanks in advance
https://kyse.link/?p=s&i=rF7gNapHXGQFdbmhVHJx...
Join using a json value with postgres
I have this table:
```
export interface MzCatalogMzAuditEvents {
id: Generated<string>;
event_type: Generated<string>;...
How to use coalesce in a join
I'm selecting disparate object types, and then looking up details in per-type lookup tables, then I want to join on another table based on an ID in the type tables, so I need to coalesce the ID columns. Currently I'm just doing a cast to make it work:
```
.leftJoin(
"mz_catalog.mz_clusters as c",...
Plugin for auto-update "updated_at" field
Hello Team, Greetings!
I am trying to write a plugin that will auto-update the "updated_at" column when there will be an update operation.
So far, I have implemented the following code:...
Solution:
Something like this:
```ts
class UpdatedAtPlugin implements KyselyPlugin {
transformQuery(args: PluginTransformQueryArgs): RootOperationNode {...
Making Transactions with KyselyAdapter for auth.js
Hey everyone, I am using this KyselyAdapter package for auth.js https://github.com/nextauthjs/next-auth/pull/5464
I made slight changes since I am in a SvetleKit project and it works!
This package returns and object of functions, each function creates, updates, deletes a record in the database....
Wrong return type in raw sql
Greetings I have simple aggregations on mysql:
``typescript
select([
sqlCOUNT(DISTINCT r.review_id).as("reviewCount"),
sqlCOUNT(DISTINCT i.interaction_id)`.as("interactionCount"),...Solution:
Kysely doesn't do any data transformations. The underlying driver has full control over that.
Some drivers might not unwrap numeric values of result columns that might exceed javascript's safe number range.
If the driver returns column metadata with the results, you could extend the dialect and automatically unwrap specific result columns by data type.
If not, you can implement a custom plugin that allows per-query transformation of result columns given in a list....
Using `.orderBy` in JSON
Hey,
I have a schema that roughly looks like this ```ts
export interface Member {
id: string;...
Solution:
try something like:
```ts
const rows = await kysely
.selectFrom("member")...
Modifying the return type of a query
I have a question, hopefully it's not a silly one.
So I have generated types for my
DB (via kanel-kysely), one of which is for a materialized view, and as such all columns of that view are generated as nullable. (I think that's just some Postgres weirdness, not related to kysely or kanel).
I also have a function that runs a query and selects all from that view, and I am wondering if there is any way to modify that in kysely land so that the returned type has all of these columns as non-nullable....Solution:
Hey 👋
Not a silly one at all. 🙂
You could use
.$narrowType to narrow the result type in a type-safe way....Access column type in custom plugin
Hey!
We would like to access the column type in one of my custom Kysely plugins.
The AST does not seem to know the column type in the
transformQuery method.
Is there a way to access the column types within our plugin? ...Solution:
That info is not available. The only way to get it is to query the db and Kysely never runs queries you don't explicitly execute.