tgriesser/knex

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

Potpot

@pot-code

knex.raw('? FETCH FIRST ? ROWS ONLY', [knex.('table').where('id', 1), limit])

tyvm, it works now!

Jakub

@Jakub41

Hello, may I ask your help how to convert an SQL to pure Knex as I'm new to it and have no idea how please :)

SELECT
    *
FROM
    "conversation"
ORDER BY
    (
        SELECT
            max("created_at")
        FROM
            "message"
        WHERE
            "conversation_id" = 'conversation.id')
    DESC

Kristjan

@KristjanTammekivi

Something like

knex('conversation')
    .orderBy(
        knex('message')
            .max('created_at')
            .whereRaw('conversation_id = conversation.id'),
        'desc'
    )

Jakub

@Jakub41

Thanks Yes but I giving me undefined column on the where part :( when using Knex.
Also a question would better to do this with a join instead as I'm having 2 tables there message and conversation?

Jakub

@Jakub41

I decided to use a join and I did in this way but I got a syntax error near "SELECT" after the join.
Do you know why and how to fix it?
below also the SQL generated from this query

this.builder
      .join(
        this.tx(messageTable)
          .select(messageColumns.conversationId)
          .max(messageColumns.createdAt, 'updated_at')
          .where(messageColumns.conversationId, columns.id)
          .groupBy(messageColumns.conversationId),
        messageColumns.conversationId,
        'updated_at'
      )

SELECT
    *
FROM
    "conversation"
    INNER JOIN
    SELECT
        "conversation_id",
        max("created_at")
    FROM
        "message"
    WHERE
        "conversation_id" = 'id'
    GROUP BY
        "conversation_id" ON "conversation_id" = "updated_at"

Arthur Neuman

@exoRift

So I have a problem

I have this query

                .andWhere(
                  req.db.raw('(:start::date >= starttime AND :start::date <= endtime) OR (:end::date >= starttime AND :end::date <= endtime)', {
                    start: start.toISOString(),
                    end: end.toISOString()
                  })
                )

The problem is, the colons that are meant to convert it to date format treat the named bindings as identifiers instead of values

Since it's interpreted as :start: instead of :start

Mikael Lepistö

@elhigu

Workaround is to use ?? bindings

Slim Hmidi

@slim-hmidi

hello,
I created a query using with and raw:

 const eventKnex = Event.knex()
      const fetchedEvents = await eventKnex
        .with('cte_event_imp', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
            from event,
            (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
            where event_type = 'imp'
            order by ST_Distance(geography, ref) asc
            limit 1
        `)).with('cte_event_click', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
        from event,
        (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
        where event_type = 'click'
        order by ST_Distance(geography, ref) asc
        limit 1
    `))
        .select(['cte_event_imp.id as imp_id', 'cte_event_click.id as click_id'])
        .from(['cte_event_imp', 'cte_event_click'])

I got this error:

"with cte_event_imp as (select *, ST_Distance(geography, ref)/1000 as distance from event,            (select ST_MakePoint(3, 4)::geography as ref) as ref            where event_type = 'imp'            order by ST_Distance(geography, ref) asc            limit 1        ), cte_event_click as (select *, ST_Distance(geography, ref)/1000 as distance        from event,        (select ST_MakePoint(3, 4)::geography as ref) as ref        where event_type = 'click'        order by ST_Distance(geography, ref) asc        limit 1    ) select cte_event_imp.id as imp_id, cte_event_click.id as click_id from cte_event_imp as 0, cte_event_click as 1 invalid reference of an entry in the FROM clause for the table "cte_event_imp"

The issue is with the from(['cte_event_imp', 'cte_event_click']), when I put only one table name it works but I need to select from both of the cte. How can I fix that?

Slim Hmidi

@slim-hmidi

I fixed it using the table indexes:

 const eventKnex = Event.knex()
      const fetchedEvents = await eventKnex
        .with('cte_event_imp', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
            from event,
            (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
            where event_type = 'imp'
            order by ST_Distance(geography, ref) asc
            limit 1
        `)).with('cte_event_click', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
        from event,
        (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
        where event_type = 'click'
        order by ST_Distance(geography, ref) asc
        limit 1
    `))
        .select(['0.id as imp_id', '1.id as click_id'])
        .from(['cte_event_imp', 'cte_event_click'])

Is it the right way to select columns of multiples tables or there is another clean way?

Mikael Lepistö

@elhigu

Pass an object for .from({ cei : ’cte_event_imp’, cec: ’cte_event_click’ })

Coop

@Darkle

Hi, I have a question about knex, sqlite3 and its async nature. If I have a transaction that takes multiple seconds to complete, does that mean nothing else can read/write to the db, or are other parts of my code still able to read/write to the database becase the sqlite3 library is async?

Mikael Lepistö

@elhigu

if sqlite library supports multiple connections to the database, reading while transaction is open should work (years ago sqlite didn't support multiple connections, but I think it supports them now).

you also need to configure knex to have poolsize > 1 in that case

Arthur Neuman

@exoRift

Would it be possible to automatically replace all instances of selecting a specific column (interval (Postgres)) to extract epoch?

Could it be done with wrapIdentifier?

Arthur Neuman

@exoRift

Basically, is there any better way to do this

db('meetings')
    .select(['*', db.raw('EXTRACT(EPOCH from length) as length')])
    .where('id', '1')
    .orderBy('startdate', 'asc')

Mikael Lepistö

@elhigu

That looks the way it should be done 👍👌

Wigger Boelens

@Boelensman1

Hi! Is there anyone who could quickly walk me through how knex acquires a connection and if I want to make changes to that where I should put the tests for that. Want to make a pull request to allow mode selection for the sqlite connection, but having some trouble familiarizing myself with the codebase

Mikael Lepistö

@elhigu

There is no easy way to implement that kind of funcionality... also how would you like to work exactly. you are probably better off by creating separate knex instances ro/rw connections (if that is what you mean vy mode selection).

There is also an issue / feature request or somthing like that in knex issues with more discussion about that kind of feature.

Wigger Boelens

@Boelensman1

There is no easy way to implement that kind of funcionality... also how would you like to work exactly. you are probably better off by creating separate knex instances ro/rw connections (if that is what you mean vy mode selection).

What I'm looking for is the option to pass the OPEN_URI option to sqlite3

Which allows you to, among other things, use named in-memory databases, which in turn allows you to use a pool and transactions on in memory databases

node-sqlite implements it, so (in theory) it should be possible to replace

new this.driver.Database(
        this.connectionSettings.filename,

with new this.driver.Database(this.connectionSettings.filename, this.connectionSettings.mode (or something)

Wigger Boelens

@Boelensman1

At least that's what I'm hoping

https://github.com/mapbox/node-sqlite3/pull/1078/files is the pull request adding support for it in node-sqlite3

There is also an issue / feature request or somthing like that in knex issues with more discussion about that kind of feature.

I can't seem to find that issue, what should I search for?

Wigger Boelens

@Boelensman1

I created a pull request (knex/knex#4446), so any comments on things I did wrong or you would like changed can be placed there!

Snir Shechter

@SnirShechter

Hey everyone,

I'm wondering about the best performant way to access the database very frequently.

I have a URL shortener microservice. With each access to a short URL, I'm saving a count of that access in the database. Of course, this can lead to thousands of changes in the database each minute.

Should I be adding some kind of cache + flush mechanism before Knex or does Knex handle it properly?

Mikael Lepistö

@elhigu

Knex can handle thousands of queries per second if the underlying db can. Though I’m not sure if an SQL database is the best for that use case.

Snir Shechter

@SnirShechter

So it is better for me to make some kind of cache

I'll flush it every 10~ seconds as a single operation to the database

It's just count++ :)

Mikael Lepistö

@elhigu

Well i would just write every operation separately and optimize afterwards if there really is a problem. With that caching you will risk loosing data if app crashes.

If loosing some data is not a problem and good write performance is the only goal I would think of using redis with persistency set to flush to disk every second or so.

Snir Shechter

@SnirShechter

Yes, that's a risk, but the microservice is an open-source (https://github.com/mini-services/miniurl) so I don't really want to burden the database. I don't mind losing some of that count in extreme cases.

I agree, but as an open-source, I can't really set those constraints.

Mikael Lepistö

@elhigu

You can setup a separate db for its data to not to burden other databases

Snir Shechter

@SnirShechter

Already done as part of the Helm chart

But a user can just connect it to an existing database

I think caching the updates just for the count operation would probably be fine.

Mikael Lepistö

@elhigu

If you dont know what kind of loads users put to that service and to what kind of db it connects i suppose it should be configurable how many requests are queued at most and for how long maximum time

If you really want to add that cache at all :j

Snir Shechter

@SnirShechter

You're right. I'll make it configurable :)

Chris Newhouse

@newhouse

Any idea the best way to add the MATERIALIZED keyword to a with / CTE in Knex? I need WITH "foo" AS (...) to become WITH "foo" AS MATERIALIZED (...).

2 replies

donaldball

@donaldball_twitter

I am on a quest to excise deadlock errors from a set of services I have inherited and am wondering if I'm missing something? I don't see any facility in knex to retry deadlock errors. I do see two mysql dialect libraries which mix that functionality in. Is that the recommended approach? Is there any interest in a common way to express this?

Phong Thieu

@pthieu

Hi, I'm trying to query a table1 with a column that is an array of UUIDS:

table1 {
  ...
  uuids: UUID[],
  ...
}

I want to take an input array of uuids and query on this column using the ANY operator, as in if any one of the UUIDs within the input array exists in any of the column's UUIDs, then the record is returned.

I have this piece of code

knex(TABLE_NAME).where('uuids', '@>', uuidsWanted);

But what happens here is that the column uuid must be exactly what is listed in uuidsWanted. Is there a way to do this with knex or would

I have to do this with a raw query?

I've tried as well but I believe the knex.raw syntax is wrong

return knex(TABLE_NAME).where('uuids', '@>', knex.raw(`ANY(${mmUuids})`));

Phong Thieu

@pthieu

Update: I was able to do it like this

return knex(TABLE_NAME).where(
    'uuids',
    '&&',
    knex.raw(`ARRAY[${uuidsWanted.map((id) => `'${id}'`)}]::UUID[]`),
  );

Where communities thrive

People

Repo info

Activity