Is there a way to handle migrations with .sql files?
I enjoy being able to control the specific queries in the migration files. To do this, I use postgrator (https://www.npmjs.com/package/postgrator) along with a custom script that also manages which environment should run each file. Does Kysely migrations also support this feature (migrations with only .sql files)?
```sql
-- context: dev,stage,prod
CREATE TABLE IF NOT EXISTS postgrator_migrations (
version BIGINT NOT NULL,...
Solution:
But nothing prevents you from using a separate tool for migrations. There's no benefit from using Kysely's migrations. And to be honest, the migration module has received very little love. It's quite bare-bones.
Postgres: Transaction not honouring previous queries
Hi, I have a table with unique constraint on some columns.
In a transaction, I am deleting all the entries from the table and then I am inserting new entries. However, I am getting
error: duplicate key value violates unique constraint "xxxxx_key"
I only want the deletion to happen if the insertion is successful, and that's why I tried using transaction. However, the transaction seems to work in a different way than I thought.
...Solution:
If you insert before you delete, that's what's going to happen. You insert something and only after that delete the duplicates. The constraint is checked immediately when you insert.
How do I specify a MySQL index hint?
Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not work out how I can pass the index hint (
USE INDEX ...):
```sql
SELECT
sc.synopsis_fk,
'characters',...question about transaction isolation level
I am not sure if the way I do queries with transaction with kysely is the right way. I would be glad if someone could take a look:
``
try {
await sqlBEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`...case when column = another column
I have a semi-complex query, and I'm trying to use a case() that includes a when() where the two values are columns (aka the two columns have the same value).
It doesn't seem to be supported, however in theory this should work. Any idea how I can make it work with Kysely?
The
.when("shahz_response", "=", "rank") is where I'm attempting this, but this isn't working...Solution:
Welp, looks like I figured it out!
.when("shahz_response", "=", e.ref("rank"))...Webstorm warning with multiple joins
```ts
const organizationsWithAdminUser = await db
.selectFrom("organizations")
.innerJoin("groups", "groups.organization_id", "organizations.id")
.innerJoin("users_groups", "users_groups.group_id", "groups.id")...
How do I use `WHERE NOT EXISTS`?
I am trying to write the followng where condition:
```sql
INSERT INTO Filing (location_id, filing_date, other_columns)
SELECT L.location_id, '2023-01-01'::date, default_values_for_other_columns
FROM Location L...
Solution:
You can call exists inside not to use not exists, something like this
```ts
db.insertInto("Filing")
.columns(["location_id", "filing_date"])...
Message Not Public
Sign In & Join Server To View
transforming database values to JS values (dates in sqlite)
hi. is it possible to transform database values with any driver into any JS value? such as integers to timestamps on specified columns? i want to do this for sqlite and other databases but idk how
How to enable pragmas with SqliteDialect
Hello, I am using SqliteDialect and want to enable foreign_keys pragma.
From SQLite Docs:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection....
Solution:
Message Not Public
Sign In & Join Server To View
Dynamic return type based on provided select array
So I have this function:
```
async getBillableUsers(select: SelectExpression<DB, 'user_data'>[]) {
return this.db
.selectFrom('user_data')...
Solution:
Took 2 hours but I think I figured it out.
Here's my solution for anyone else who might stumble upon this problem.
Feel free to leave better/smarter/faster/shorter alternatives.
```...
How to work with json columns?
There are so many different sources in the documentation as those made me confused completely. I used documented way but still get errors:
https://kyse.link/H3MKN...
Does the TypeScript performance scale with larger projects?
I just started migrating to kysely, love it so far! I was wondering how well the TypeScript performance scales for medium to large projects. For example, I'm working on a project with 500 db tables and 600k lines of code with much of it composed of db queries. As I'm rewriting the first few queries, I'm noticing slight delays with type inference and IDE code completion. Nothing major, but I'm worried it might get worse as I migrate more of the project and more type checking needs to happen.
Is this an actual concern or am I overthinking it? Can anyone who is using kysely on a larger project attest to whether they're running into type performance issues?...
Solution:
I'd strongly consider splitting into domains/services and exposing kysely clients with only what's known/used by each domain/service.
SQLite__namespace is not a constructor
Hi Im building a Electron-Vite App and running into the following error:
```
TypeError: SQLite__namespace is not a constructor
at Object.<anonymous> (C:\Users\USER\OneDrive\Dokumente\Programmieren\dim_wishlist_2\out\main\index.js:26:13)
at Module._compile (node:internal/modules/cjs/loader:1271:14)...
Solution:
This is caused by your module interop config somehow. Does it work if you use
...
import SQLite from 'better-sqlite3'
import SQLite from 'better-sqlite3'
Raw SQL in where clause
I have the following code and I'm getting a type error. This is strange as the raw sql statement works for insert statements but doesn't seem to play nice with the where clause.
In addition, the code below seems to work before
0.27.0.
```...Solution:
Give a type for the raw expression
...
.where('disabled_at', '>', sql<Date>`now()`)
.where('disabled_at', '>', sql<Date>`now()`)
smallint support (Postgres)
Type dataType does not contain smallint?
```TS...
addColumn<CN extends string>(columnName: CN, dataType: DataTypeExpression, build?: ColumnBuilderCallback): CreateTableBuilder<TB, C | CN>;
addColumn<CN extends string>(columnName: CN, dataType: DataTypeExpression, build?: ColumnBuilderCallback): CreateTableBuilder<TB, C | CN>;
Solution:
Yep, seems to be missing. You can use
...
addColumn("foo", sql`smallint`)
addColumn("foo", sql`smallint`)
Examples inner join using OR?
Hello, we are having trouble trying to have a inner join using OR, as in
ON a.id = b.id OR a.sub_id = b.sub_idfull join "using" list of columns
Hey,
How can I create a full join which uses "using"
I want to use "using" in the join but I only see "on", maybe I can acheive this using sql
using , what is the correct way ?
example:
```...Solution:
```ts
.innerJoin('b', join => join
.onRef('a.foo', '=', 'b.foo')
.onRef('a.bar', '=', 'b.bar')
)...
Is kysely sanitizing sql injection when using raw sql ?
Suppose I have a piece of code that does
sql
${myString}
where myString contains sql injection, will kysely sanitize it or is it something that I need to take care of ?...Solution:
Of course it is.
Support for D1 batching
Hi. I'm wondering about support for batching statements with Cloudflare's D1 batching, similar to libsql's batching - i.e. multiple statements are sent and processed at once similar to a transaction, except it all occurs within the database driver itself. Can I somehow go about extending kysely or my dialect to add support for this? I.e. some dialect-specific functions i guess?
Omit/filter out columns from query
Hey, is there an easy or convenient way with Kysely to filter out data from a query? Lets say I have columns 1,2,3,4,..., 10 and I want to omit column 9, do I have to really list all my columns like this:
?...
select(['1','2','3','4','5','6','7','8','10']
select(['1','2','3','4','5','6','7','8','10']
Solution:
Updated version with autocompletion and a list of excludes https://kyse.link/?p=s&i=Z9WWYXTjVPvB901Q1N9M