mike Posts - Answer Overflow

mike

•Created by mike on 9/30/2024 in #help

Migrator transaction (postgresql dialect)

Hello everyone, will the code below ensure all migrations are in a single transaction? if kysely migrations don't support AsyncLocalStorage, does it make sense to use unmanaged transaction and put BEGIN before and COMMIT at the end?

    await db.transaction().execute(async () => {
        let results: any[], error: any;
        if (migrationDirection === 'down') {
            ({ error, results } = await migrator.migrateDown());
        } else if (migrationDirection === 'up') {
            ({ error, results } = await migrator.migrateUp());
        } else if (migrationDirection === 'to') {
            if (!migrationName) {
                throw new Error(
                    'Migration name must be provided when using "to" direction.',
                );
            }
            ({ error, results } = await migrator.migrateTo(migrationName));
        } else {
            ({ error, results } = await migrator.migrateToLatest());
        }
    });

    await db.transaction().execute(async () => {
        let results: any[], error: any;
        if (migrationDirection === 'down') {
            ({ error, results } = await migrator.migrateDown());
        } else if (migrationDirection === 'up') {
            ({ error, results } = await migrator.migrateUp());
        } else if (migrationDirection === 'to') {
            if (!migrationName) {
                throw new Error(
                    'Migration name must be provided when using "to" direction.',
                );
            }
            ({ error, results } = await migrator.migrateTo(migrationName));
        } else {
            ({ error, results } = await migrator.migrateToLatest());
        }
    });

13 replies

KKysely

•Created by mike on 6/14/2024 in #help

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('%')),
        ),
    ]),
);

11 replies

KKysely

•Created by mike on 6/13/2024 in #help

How to order by desc using expression builder with case

I am ordering by using case but cannot find a way how to add desc to the eb.

const person = await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(eb.fn("length", ["first_name"]))
      .else(eb.fn("length", ["last_name"]))
      .end(),
  )
  .executeTakeFirst()

const person = await db
  .selectFrom("person")
  .select(["id", "first_name"])
  .where("id", "=", "1")
  .orderBy((eb) =>
    eb
      .case()
      .when(
        eb.fn("length", ["first_name"]),
        "<=",
        eb.fn("length", ["last_name"]),
      )
      .then(eb.fn("length", ["first_name"]))
      .else(eb.fn("length", ["last_name"]))
      .end(),
  )
  .executeTakeFirst()

https://old.kyse.link/?p=s&i=E4Hpv78UML4e6YqZoY6u

5 replies

KKysely

•Created by mike on 6/11/2024 in #help

How to like left value to right raw statement?

Unsuccessful attempts: https://old.kyse.link/?p=s&i=RXSid8MMyDZRXfcl8Jdq Expected result:

SELECT *
FROM geography.postal_areas
WHERE $1 LIKE "postalCode" || '%';

SELECT *
FROM geography.postal_areas
WHERE $1 LIKE "postalCode" || '%';

20 replies

KKysely

•Created by mike on 6/4/2024 in #help

Any limits on insertValues(array)?

I am seeding data in migrations and received error when trying to insert over 35.000 rows at once.

const rows = [ // there are 35.000 objects here]
await db
  .insertInto('tableName')
  .values(rows)
  .execute();

const rows = [ // there are 35.000 objects here]
await db
  .insertInto('tableName')
  .values(rows)
  .execute();

but if I do the loop it all works without any problems.

for (const row of rows) {
  await db
    .insertInto('tableName')
    .values(row)
    .execute();
}

for (const row of rows) {
  await db
    .insertInto('tableName')
    .values(row)
    .execute();
}

Failed to migrate error: bind message has 39901 parameter formats but 0 parameters
    at /project-folder/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:91:69)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16) {
  length: 122,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1709',
  routine: 'exec_bind_message'
}
node:internal/errors:863
  const err = new Error(message);

Failed to migrate error: bind message has 39901 parameter formats but 0 parameters
    at /project-folder/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:91:69)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16) {
  length: 122,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1709',
  routine: 'exec_bind_message'
}
node:internal/errors:863
  const err = new Error(message);

6 replies

KKysely

•Created by mike on 6/3/2024 in #help

Migration to seed huge amount of data

Hi, are there any guides or suggestions how to effectively seed huge amount of data with migrations? We have the data in separate json (or csv) files which are copied to "assets" folder in the dist folder. How do you do this?

5 replies

KKysely

•Created by mike on 5/31/2024 in #help

How to add array column in migrations?

I am trying to add a column to a table via migrations but cannot find how to do something like this: .addColumn('countyNames', 'varchar', col => col.IsArray() )

15 replies

KKysely

•Created by mike on 4/10/2024 in #help

Custom Plugin to transform Alias.* to "Alias.Column1", "Alias.Column2", etc.

Hi everyone, in the previous question, I asked how to transform the following syntax into a new one. I was adviced to create my own plugin serializer (example: https://github.com/subframe7536/kysely-sqlite-tools/tree/master/packages/plugin-serialize). However, I still struggle with how to do it. Can anybody please give me a hint? I'll do it later by myself but need a guide how to start. Thank you very much! input syntax:

  .SelectFrom('Table as Alias')
  .SelectAll('Alias')

  .SelectFrom('Table as Alias')
  .SelectAll('Alias')

required result:

 .SelectFrom('Table as Alias')
 .Select('Alias.Column1 as "Alias.Column1"')
 .Select('Alias.Column2 as "Alias.Column2"')
 ... all the rest columns in 'Alias' ...

 .SelectFrom('Table as Alias')
 .Select('Alias.Column1 as "Alias.Column1"')
 .Select('Alias.Column2 as "Alias.Column2"')
 ... all the rest columns in 'Alias' ...

12 replies

KKysely

•Created by mike on 3/26/2024 in #help

Snippet compilation

Hi, is it somehow possible to get only part of the compiled code to use it as snippet in ORM? Or vice-versa is it possible to build only part of the code with type helpers and compile them without the parent query? For example I would like to get only the code for the header_index and header_order aliases:

SELECT 
  code, 
  name, 
  priority, 
  header,
  CASE 
    WHEN header IS NULL THEN 0
    ELSE MIN(priority) OVER (PARTITION BY header) 
  END AS header_index,
  ROW_NUMBER() OVER (PARTITION BY header ORDER BY priority) AS header_order
FROM 
  Product
ORDER BY 
  header_index, 
  priority;

SELECT 
  code, 
  name, 
  priority, 
  header,
  CASE 
    WHEN header IS NULL THEN 0
    ELSE MIN(priority) OVER (PARTITION BY header) 
  END AS header_index,
  ROW_NUMBER() OVER (PARTITION BY header ORDER BY priority) AS header_order
FROM 
  Product
ORDER BY 
  header_index, 
  priority;

6 replies

KKysely

•Created by mike on 2/23/2024 in #help

how to write not (array1 && array2)

Hi again, how to write following code? I would love to use not(expression) Expected result:

not('{US,UK,AR}' && my_table.allowed_countries)

not('{US,UK,AR}' && my_table.allowed_countries)

raw attempt:

sql`not('{ ${array1.join(',')} }') && ref('my_table.allowed_countries')

sql`not('{ ${array1.join(',')} }') && ref('my_table.allowed_countries')

but it produces:

not('{'US,UK,AR'}') && my_table.allowed_countries)

not('{'US,UK,AR'}') && my_table.allowed_countries)

2 replies

KKysely

•Created by mike on 2/23/2024 in #help

Partial compile

Hello everyone, is it possible (somehow) to generate only part of the compiled query? I have an edge-case when want to use only part ("where" statement in my case) of the compiled query from kysely. Is there any short-way to do it? I definitely don't want to parse the result. Thanks Mike

11 replies

KKysely

•Created by mike on 2/1/2024 in #help

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

6 replies

KKysely

•Created by mike on 11/13/2023 in #help

How to plugin column aliases with table prefix?

I am not familiar with kysely plugin creation. I am also not sure if my issue can be done with such a plugin. My aim is to have overload method like this:

.selectAll('t1', 'prefix_t1')

.selectAll('t1', 'prefix_t1')

so the resulted query will be:

select t1.column1 as "prefix_t1.column1", t2.column2 as "prefix_t1.column2", etc.

select t1.column1 as "prefix_t1.column1", t2.column2 as "prefix_t1.column2", etc.

would it be possible with plugins or any other (even existing) solution in kysely? Thank you

7 replies

KKysely

•Created by mike on 11/10/2023 in #help

Zero number omitted from parameters

I looks like the compile() works incorrectly with zero number when used as parameter - the parameter is omitted from the array of sql components. Not included in sql params:

// used in case/when
.when(eb.fn.max('Orders.quantity'), '>', 0)

// used in case/then/else
.else(0)

// used in case/when
.when(eb.fn.max('Orders.quantity'), '>', 0)

// used in case/then/else
.else(0)

Included (workaround) in the params:

.when(eb.fn.max('Orders.quantity'), '>', sql<number>`0`)
.else(sql<number>`0`)

.when(eb.fn.max('Orders.quantity'), '>', sql<number>`0`)
.else(sql<number>`0`)

5 replies

KKysely

•Created by mike on 11/9/2023 in #help

bool_or

Hi, How to use bool_or ? I am using it in case-when-then construction. https://www.postgresql.org/docs/8.4/functions-aggregate.html

3 replies

KKysely

•Created by mike on 10/12/2023 in #help

withSchema is marked as deprecated in ExpressionBuilder?

7 replies

KKysely

•Created by mike on 9/19/2023 in #help

Is there a plan to add "NULLS NOT DISTINCT" (postgresql v15) option to unique constraints/indexes?

In postgresql version 15.0 "NULLS NOT DISTINCT" statement was introduced. How to include this statement? BTW in version 16 it was disallowed for primary keys.

4 replies

KKysely

•Created by mike on 9/18/2023 in #help

How to work with window function ROW_NUMBER correctly?

How to type correctly something like this? I struggle with the ROW_NUMBER...

const query = `
    WITH ranked_licenses AS (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY type ORDER BY l."expiresAt" ASC
            ) as rn
        FROM license l
        WHERE l.active = ${String(true)}
    )
    SELECT * FROM ranked_licenses WHERE rn = 1;
`;

const query = `
    WITH ranked_licenses AS (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY type ORDER BY l."expiresAt" ASC
            ) as rn
        FROM license l
        WHERE l.active = ${String(true)}
    )
    SELECT * FROM ranked_licenses WHERE rn = 1;
`;

5 replies

KKysely

•Created by mike on 8/24/2023 in #help

Migrations in transaction and self management

Hi, I have a questions regarding migrations: - Is it possible to run all migrations in one transaction? - If I want to use kysely migrations for only generating raw sql for another db migration framework (such as umzug), should I use compile()? - How to handle deprecated models? If I remove a column from a model how will this be supported by the typing? Thanks in advance Mike

6 replies

KKysely

•Created by mike on 8/4/2023 in #help

How to generate query synchronously?

Besides other things I want to use kysely to generate subqueries for sequelize scopes. Is it possible to generate the query synchronously? Sequelize scopes do not accept async calls.

6 replies

Gaming

Programming