Kysely•3mo ago

Creating snippets (best pratice)

How can I create query snippet once and add it to multiple queries?

const querySnippet = kysely; // create once and add to both count and rows query instead of repeating the same code twice?

countQuery = countQuery.where((eb) =>
    eb.or([
        eb(
            'searchablePostalCode',
            'ilike',
            eb(eb.val(searchPostalCode), '||', sql.lit('%')),
        ),
        eb(
            eb.val(searchPostalCode),
            'ilike',
            eb('searchablePostalCode', '||', sql.lit('%')),
        ),
    ]),
);
rowsQuery = rowsQuery.where((eb) =>
    eb.or([
        eb(
            'searchablePostalCode',
            'ilike',
            eb(eb.val(searchPostalCode), '||', sql.lit('%')),
        ),
        eb(
            eb.val(searchPostalCode),
            'ilike',
            eb('searchablePostalCode', '||', sql.lit('%')),
        ),
    ]),
);

const querySnippet = kysely; // create once and add to both count and rows query instead of repeating the same code twice?

countQuery = countQuery.where((eb) =>
    eb.or([
        eb(
            'searchablePostalCode',
            'ilike',
            eb(eb.val(searchPostalCode), '||', sql.lit('%')),
        ),
        eb(
            eb.val(searchPostalCode),
            'ilike',
            eb('searchablePostalCode', '||', sql.lit('%')),
        ),
    ]),
);
rowsQuery = rowsQuery.where((eb) =>
    eb.or([
        eb(
            'searchablePostalCode',
            'ilike',
            eb(eb.val(searchPostalCode), '||', sql.lit('%')),
        ),
        eb(
            eb.val(searchPostalCode),
            'ilike',
            eb('searchablePostalCode', '||', sql.lit('%')),
        ),
    ]),
);

Solution:

But if you want a reusable helper, you need to let go of some type-safety. It's very difficult and often impossible to create a generic and strict helper. You can do something like this ```ts db.selectFrom('whatever').select('foobar').where(myLovelyFilter(searchPostalCode)) ...

Jump to solution

6 Replies

koskimas•3mo ago

Why not first add the where statement and only after that split into rows and count queries? Unrelated, but instead of this

eb(
    'searchablePostalCode',
    'ilike',
    eb(eb.val(searchPostalCode), '||', sql.lit('%')),
)

eb(
    'searchablePostalCode',
    'ilike',
    eb(eb.val(searchPostalCode), '||', sql.lit('%')),
)

it's 100% safe to do this

eb(
    'searchablePostalCode',
    'ilike',
    `${searchPostalCode}%`
)

eb(
    'searchablePostalCode',
    'ilike',
    `${searchPostalCode}%`
)

mike•3mo ago

regarding the "prettier" stuff - why not 🙂 regarding where splitting can you show it on complex example?

koskimas•3mo ago

Why does it have to be complex? What you're doing is running the same query twice except the other has columns selected and the other just has a single count(*) selection right? So something like this

const query = db
  .selectFrom(whatever)
  .where(howeverComplexShitYouWant)

const [rows, { count }] = await Promise.all([
  query.select(['the', 'columns', 'you', 'want', 'to', 'select'])
       .execute(),
  query.select(eb => [eb.countAll().as('count')])
       .executeTakeFirstOrThrow(),
])

const query = db
  .selectFrom(whatever)
  .where(howeverComplexShitYouWant)

const [rows, { count }] = await Promise.all([
  query.select(['the', 'columns', 'you', 'want', 'to', 'select'])
       .execute(),
  query.select(eb => [eb.countAll().as('count')])
       .executeTakeFirstOrThrow(),
])

Solution

koskimas•3mo ago

But if you want a reusable helper, you need to let go of some type-safety. It's very difficult and often impossible to create a generic and strict helper. You can do something like this

db.selectFrom('whatever').select('foobar').where(myLovelyFilter(searchPostalCode))

function myLovelyFilter(searchPostalCode: string) {
    return () => {
        const eb = expressionBuilder<Database, 'theTableThatHasSearchablePostalCodeColumn'>()

        return eb.or([
            eb(
                'searchablePostalCode',
                'ilike',
                eb(eb.val(searchPostalCode), '||', sql.lit('%')),
            ),
            eb(
                eb.val(searchPostalCode),
                'ilike',
                eb('searchablePostalCode', '||', sql.lit('%')),
            ),
        ])
    }
}

db.selectFrom('whatever').select('foobar').where(myLovelyFilter(searchPostalCode))

function myLovelyFilter(searchPostalCode: string) {
    return () => {
        const eb = expressionBuilder<Database, 'theTableThatHasSearchablePostalCodeColumn'>()

        return eb.or([
            eb(
                'searchablePostalCode',
                'ilike',
                eb(eb.val(searchPostalCode), '||', sql.lit('%')),
            ),
            eb(
                eb.val(searchPostalCode),
                'ilike',
                eb('searchablePostalCode', '||', sql.lit('%')),
            ),
        ])
    }
}

koskimas•3mo ago

The type-safety issue there is that you can use myLovelyFilter with queries that don't actually have the searchablePostalCode in contexts, leading to runtime errors.

mike•3mo ago

wow, I will definitely look into this. thank you

Gaming

Programming

Creating snippets (best pratice)