Returning numeric fields as float
Hello, we're migrating from Supabase to Kysely and we have some queries failing in our client because it seems Kysely is returning columns marked as numeric in string format. How can one setup Kysely to parse those numeric columns to float ?
how to convert bigint to text in select statement?
In raw sql we do this cursor_id::text. How to perform this in kysely?
Subquery on same table
I'm experimenting with how to improve the performance of some queries against a PlanetScale MySQL DB.
I have a fulltext index on a field in a table, then some other indexes on various other fields.
MySQL can't use two indexes for a query so I'd like to try including the section of the query that uses the fulltext column in a subquery but I'm struggling with the syntax
The primary key of the table is activityId....
How to return last inserted ID ?
```
export async function createCommands(deviceId: number, commands: string[]) {
return db
.insertInto("commands")
.values(commands.map((c) => ({ deviceId, content: c })))...
hi, how do i make a select with a where that searches within a json? I'm using postgres
inside the json there is an id that I want to use to do the search, but I didn't understand how to do it, could you help me?
CASE example in docs?
Hello, where can one find an example of how to properly use CASE for query building?
We want to use it inside a Where clause at the moment....
How can I cast a jsonb value to a float8 in query?
I have a query that has a jsonb value in the db that will always be a number, and I want to sum it. how can I change this value to a number
fn.sum<number>('value')
what I want is:...
`InsertId` type for UUIDs
Hello, we use UUIDs as our primary IDs so when running insertInto statements we expected the insertId to be a string, but we are getting a
bigint | undefined type.
How can we get the correct type?...How to access error events -- Is there a best/good practice for error handling
I'm writing my methods and associated tests for db access and when trying to validate error events. I get a SQL event log but nothing is caught in my try-catch block. When I log the response it is either empty or
undefined.
*Example
```typescript...`createTable` function with dynamic column array
Hey folks, I'm trying to create a create table function; I feel like this should be straight forward and just work. But, I'm obviously missing something. Please let me know if more details are required.
Thank you for your time. 🙏
```typescript...
Issue Getting TypeScript to work with nested query
Hi there 👋 . I'm experiencing an issue with getting typescript to play nice with Kysely. I should add that I'm still a newbie to Kysely and typescript in general.
Here's the code:
```typescript
await db.selectFrom((eb) => {
return eb.selectFrom("faqs")...
How can one make `jsonObjectFrom` respect nullability of underlying subquery?
Hello, I am getting a type error when using the
jsonObjectFrom helper on a subquery where the base table type of a field is not null, but all fields returned by jsonObjectFrom are nullable.
We are passing the eb with the jsonObjectFrom helper inline in our query and were wondering if there a way to specify that a field will not be null?...Filtering data based on relationship value
Hello, I'm using subqueries to fetch and return related data from a resource table. I am using the
jsonObjectFrom helper which is working perfectly. My question now is how can I filter the final data based on a field present in the relation data. For example if my main table is User and the related data is Account, how can I fetch Users where the account is active all in the same query? Im already sing the expression builder (eb) for the subquery, do I need to add a separate join expression...How can I write a query that does multiple insertions at once?
I have an array of Ids that I want to assign to a single id in a "bridging" table for a many to many relationship:
ie:
singleId: id1
singleId: id2
singleId: id3...
How to "generate" or "store" a query result type?
Hello, I was wondering how can I extract or store the type resulting from a query so that I ca use said type in other parts of my code without it depending on a specific function or implementation location in code.
Migrations in transaction and self management
Hi, I have a questions regarding migrations:
- Is it possible to run all migrations in one transaction?
- If I want to use kysely migrations for only generating raw sql for another db migration framework (such as umzug), should I use compile()?
- How to handle deprecated models? If I remove a column from a model how will this be supported by the typing?
...
Running a function during migration
I am looking to use Kysely with TimescaleDB, which is an extension of Postgres to support timeseries data. There are a few places where I know I won't have type support, and executing raw sql is fine for my purposes. However, the process to create a timeseries table invokes a specific function via
SELECT e.g. :
SELECT create_hypertable('conditions', 'time', 'location', 4);
Since this is part of the table creation, I would like to make sure it happens during migration step. Is there a way to run raw sql as part of a migration?...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....