ohmi Posts - Answer Overflow

ohmi

•Created by ohmi on 3/23/2024 in #help

Creating an 'enum' type column

Hi all, Trying to re-create this MySQL in Kysely

...
CREATE TABLE ...(
...
members ENUM('female','male','coed') NOT NULL

...
CREATE TABLE ...(
...
members ENUM('female','male','coed') NOT NULL

 await trx.schema
            .createTable("expected_available_songs")
             ....
            .addColumn("members", "", (x) => x.notNull())

 await trx.schema
            .createTable("expected_available_songs")
             ....
            .addColumn("members", "", (x) => x.notNull())

but not quite sure what type to use (or escape hatches) to generate an ENUM here

4 replies

KKysely

•Created by ohmi on 6/22/2023 in #help

Figuring out where in codebase an exception originated from

Hi all, This might be a stupid question, but what is the best way to figure out which .execute() call threw a SQL exception? I know ideally I should be doing error handling on each call, but I have a large codebase and I'm not sure where this error is coming from. Is there any easy way of figuring this out? Maybe a way to have a wrapper that prints out the query if malformed, or to print out the stack trace where the original call was executed?

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1. Trace: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at PoolConnection.handlePacket (/app/node_modules/mysql2/lib/connection.js:490:32)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:95:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:102:25)

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1. Trace: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at PoolConnection.handlePacket (/app/node_modules/mysql2/lib/connection.js:490:32)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:95:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:102:25)

30 replies

KKysely

•Created by ohmi on 5/25/2023 in #help

Using column aliases in `.where()`

This is a supported feature in MySQL, but having issues here: https://kyse.link/?p=s&i=xxyMrfnfJ6CjbRlQZpMA Do I need to do something special for it to recognize the alias?

7 replies

KKysely

•Created by ohmi on 5/23/2023 in #help

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?

15 replies

KKysely

•Created by ohmi on 5/22/2023 in #help

Cross database joins in MySQL

Hi all, Is it possible to do joins across databases within the same MySQL instance? i.e

SELECT * FROM db1.table1 INNER JOIN db2.table2 ON db1.table1.id = db2.table2.id

SELECT * FROM db1.table1 INNER JOIN db2.table2 ON db1.table1.id = db2.table2.id

I've generated the typings for both databases, but not sure if there's a way to leverage them?

11 replies

KKysely

•Created by ohmi on 5/22/2023 in #help

.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?

12 replies

KKysely

•Created by ohmi on 5/19/2023 in #help

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

8 replies

KKysely

•Created by ohmi on 5/19/2023 in #help

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)?

7 replies

KKysely

•Created by ohmi on 5/19/2023 in #help

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?

14 replies

KKysely

•Created by ohmi on 5/18/2023 in #help

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;

6 replies

Gaming

Programming