tgriesser/knex

A query builder for PostgreSQL, MySQL and SQLite3, designed to be flexible, portable, and fun to use.

Mikael Lepistö

@elhigu

There is actually abstraction which allows to use migration system without node filesystem access.

Also one can write custom dialect and pass it as a parameter in knex config.

Johnny Robeson

@jrobeson

which is what i'm doing to support capacitor's sqlite (aka android or ios sqlite)

but since migrations are still pulled in, it requires fs when it shouldn't. which is why it currently breaks, because the noops aren't being applied

do you know what thedifference is in react-native's support vs the browser? because they both have different contents

i know the react-native key isn't standard package.json

maybe i need to read up on that

Mikael Lepistö

@elhigu

Did you check this one for migrations? https://knexjs.org/#custom-migration-sources

Johnny Robeson

@jrobeson

that wil be useful at some point indeed, but not right now i think. i'd have to read more of the code to say for sure. i think the dependency on fs is still a deal breaker

and polyfilling that in particular for web is a nogo atm

although it would be nice to have virtual fs to use here

probably not worth investigating until sql.js is viable out of the box

Mikael Lepistö

@elhigu

That allows removing dependency for fs

Johnny Robeson

@jrobeson

hmm, maybe the example isn't enough for me to tell then. i wonder if i can find a code example

i'd also need to find one for seeds as well

Mikael Lepistö

@elhigu

The link I pasted. That abstraction was added exactly for being able to write implementation without fs. Imo seeds are useless. One can just write any knex script which runs the same queries that you would add to seedfile.

Johnny Robeson

@jrobeson

have you seen an example of it being used?

Mikael Lepistö

@elhigu

I need to check on laptop who implemented that feature. He wrote the example implementation / docs too.

Mikael Lepistö

@elhigu

Looks like it was implemented here and I reviewed it :) knex/knex#2775

Johnny Robeson

@jrobeson

nice, but do you hve an example of it being used in practice?

Mikael Lepistö

@elhigu

Not more than is actually mentioned in documentation. Also I don't know JakeGinnivan to ask him if he has something available in public repos.

this is one trick though that I do quite a lot when dealing with libraries with incomplete documentation to see how other people has used them https://github.com/search?l=JavaScript&q=migrationSource&type=Code

I need to go to bed now, good luck!

John Winston

@winston0410

Hi, how can I type a raw query like this? I tried to use generic, but typing it with string is wrong

const result = await knexConnection.raw('select NOW()');

console.log(result?.rows[0]) //Print the date

Johnny Robeson

@jrobeson

i can't say. although i just build my first knex driver

Johnny Robeson

@jrobeson

so i implemented a knex driver for https://github.com/capacitor-community/sqlite for ionic

weirdly.. createTable works, but not hasTable

createTableIfNotExists is fine though

Johnny Robeson

@jrobeson

https://gist.github.com/jrobeson/788bfe6b97db45aa1535bf6897681b94 that's kind of it there.

Daniel Rodríguez Rivero

@danielo515:matrix.org

[m]

Hello. I'm migrating a big repository to knex 2.0 from a 0.X version. It seems that knex.raw does not execute the queries. Am I right?

Mikael Lepistö

@elhigu

afaik that has never executed queries and never will

but await knex.raw(...) or knex.raw(...).then() should

danielo515

@danielo515:matrix.org

[m]

Yes, if you await it then it will. That looks like some kind lazy promise? not how usual promises works. Anyway, my error was somewere else (connection settings). I thought that await was not working, and I thought .then() was mandatory. but that is not the case right? await should work as well? even the native await?

Mikael Lepistö

@elhigu

“Lazy promise” pattern is part of Promise spec and there is a it is called thenable. It is interoperability method how to create classes that operate nicely with promises. Any object that has .then() method and is returned from promise chain / resolved will automatically execute that .then() method. Async / await is basically just syntactic sugar on top of promises so everything that follows promise spec also@works with native async / await.

tl;dr knex query builder instance is thenable.

Daniel Rodríguez Rivero

@danielo515:matrix.org

[m]

Thanks for the detailed explanation. For me one of the drawbacks of promises, at lest in 99% implementations I tried, is that they are eager. In any case, as I said, the problem was something else, and indeed await works as expected. Thanks for your answers

Chris Lusted

@ChrisLusted

hello all, having a strange issue with calling knex.raw I keep getting a TypeError: db_1.knex.raw is not a function error, any ideas how I can debug that? i'm on knex@0.21.18 and node 16.10.0. thanks

Mikael Lepistö

@elhigu

Check what instance db_1.knex is

Chris Lusted

@ChrisLusted

aha this is in a jest test that I have just found is mocking knex by console logging knex. That was the issue, thanks for the suggestion!

iyuiasd7

@iyuiasd7

hello, how can I escape the question mask in whereRaw funciton, I have tried using \?, but not working

db.whereRaw(REGEXP_LIKE(column1, '^[+-]\\?\\d*\\.\\?\\d+([eE][+-]\\?\\d+)\\?$')), knex version: 2.0.0 database: oracle12.1c

joedeveloper

@joedeveloper

Hi - I am using strapi ( which uses knex ) to connect to a Digital Ocean hosted postgres DB, everything is fine until I now need to horizontally scale our CMS beyond certain a point, DO psql has a 100 connection limit, when I try to use the DO pgbouncer setting then strapi / knex tells me:

error error: select distinct "strapi_permission".* from "strapi_permission" left join "pgbouncer_dev-strapi"."public"."strapi_role" as "strapi_role_1" on "strapi_role_1"."id" = "strapi_permission"."role" where "strapi_role_1"."id" = $1 - cross-database references are not implemented: "pgbouncer_dev-strapi.public.strapi_role"
    at Parser.parseErrorMessage (/home/unomi/coder/projects/flexstay/github/ps-cms/node_modules/pg-protocol/dist/parser.js:287:98)

Any hints / insights into how I can best go about scaling horizontally? We have some long running processes in the CMS, so our default knex pool settings are 2-25, which would allow us only 4 cms instances, currently we have reduced to max 10, but we have less buffer than we like there, and worse, there is still a limit that is looming close in terms instance limits due to lack of connections

Aaron Greene

@akg1

From looking at previous issues and StackOverflow questions, it seems like the following should work:

const query = /* build a select query  as a knex QueryBuilder*/
await connection('table').insert(query);

When I do this in a TypeScript environment, I get the following:

Argument of type 'QueryBuilder<any, any>' is not assignable to parameter of type 'DbRecordArr<TRecord> | readonly DbRecordArr<TRecord>[]'.
  Type 'QueryBuilder<any, any>' is not assignable to type 'Readonly<Readonly<Partial<MaybeRawRecord<TRecord>>>>'.
    Index signature for type 'string' is missing in type 'QueryBuilder<any, any>'.ts(2345)

The code also appears to execute succesfully -- am I doing something wrong, or is there maybe a type definition issue in the knex package?

Boppy Sean

@Highsociety420_gitlab

Add this snap: high_mood 420 he can help you out

David Marcin

@djmarcin

I'm having some trouble with typescript typing on my query. I have an interface interface User { id: number; } and a query like the one from the documentation like const res = await knex.select(knex.ref("id").withSchema("users")).from<User>("users"); The documentation (http://knexjs.org/guide/query-builder.html#select) says that this should be inferred as Pick<User, "id">[] but for me it's getting inferred as any[]. Changing the query to const res = await knex<User>("users").select(knex.ref("id").withSchema("users")); results in the type inference being const res: { id: number; }[] which is somewhat better but still not Pick<...> as expected.

The real problem comes when I try to do a join with this. In order to get the typescript correct it seems like I have to do something like .innerJoin<Pick<User, "id">>(...) in order to get it to work right (and of course in reality I'm selecting a bunch of fields so this quickly becomes very verbose, hard to read, and somewhat silly because it seems like the types aren't actually enforced if they aren't inferred). Am I doing something wrong or is inference just not able to cover complex queries?

Mohit Singh

@mohit-singh-pepper

Hello everyone, can anyone tell me if there is a way to write following in knex preferrably in schema.createTable callback.
ALTER TABLE ... SET (toast_tuple_target = N).

christopher ha

@christopher-ha

How do you create a foreign key that references a primary uuid in another table? I keep getting a syntax error and there's no examples online.

Vincent GOUGEON

@vgougeon

Hi !

I'm trying to get the SQL code executed for a schema.alterTable with a .toString(), but when I add a table.renameColumn('name', 'pseudo') in the alter table, I can't get the complete SQL to rename the column, it only shows the first step of the rename :
show full fields from `user` where field = 'name'
I know that the other step to rename the column requires the result of this query, and I would like to find a way to get the whole SQL after the execution of the alterTable

It's also not displayed with debug: true on the Knex instance
Thank you !

Jakub

@Jakub41

Hello :) I have a question about the version changelog.
I noticed that we have now Knex 2.1.0 but the changelog is on 1.0.7
There is a specific reason why there is nothing for the last versions?
Right now I'm using 1.0.5 but will be updated soon and I was wondering what are the major changes between 1.0.5 and 2.1.0
Thank you :)

Where communities thrive

People

Repo info

Activity