Kysely•14mo ago

Is there a way to execute an ExpressionBuilder?

Using the expression hasDogNamed example from the docs

const eb = expressionBuilder<DB, 'person'>()

const query = eb.selectFrom('pet')
  .select('pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('pet.species', '=', 'dog')
  .where('pet.name', '=', name)

const result = await query.execute(); <--- This compiles, but doesn't work...

const eb = expressionBuilder<DB, 'person'>()

const query = eb.selectFrom('pet')
  .select('pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('pet.species', '=', 'dog')
  .where('pet.name', '=', name)

const result = await query.execute(); <--- This compiles, but doesn't work...

Is there a way I can provide an ExpressionBuilder an instance of Kysely<DB> so that it can execute the query?

5 Replies

autobotkilla•14mo ago

Figured it out. The function createExpressionBuilder accepts an optional QueryExecutor

export function createExpressionBuilder<DB, TB extends keyof DB>(
  executor: QueryExecutor = NOOP_QUERY_EXECUTOR
): ExpressionBuilder<DB, TB

export function createExpressionBuilder<DB, TB extends keyof DB>(
  executor: QueryExecutor = NOOP_QUERY_EXECUTOR
): ExpressionBuilder<DB, TB

You can get a QueryExecutor from your Kysely<DB> instance.

const eb = createExpressionBuilder<DB, 'User'>(db.getExecutor());

const eb = createExpressionBuilder<DB, 'User'>(db.getExecutor());

Igal•14mo ago

Hey 👋 It's not really meant to be executed, I'd strongly advice you NOT to do this.

autobotkilla•14mo ago

Hmmm. I'm trying to create reusable select queries so that the same shape can be returned in different circumstances. Do you have an alternative suggestion? Here's an example.

export function selectJob(db?: DittoDb) {
    const eb = createExpressionBuilder<DB, 'Job'>(db?.getExecutor());

    // prettier-ignore
    return eb
        .selectFrom('Job')
        .select([
            'Job.id', 
            'Job.status',
            'Job.title',
            withUserInfo('Job.ownerId').as('owner'),
            withUserInfo('Job.managerId').as('manager'),
        ])
}

export function withJob(jobIdColumn: any) {
    return jsonObjectFrom(selectJob().whereRef('Job.id', '=', jobIdColumn));
}

export function getJobById(id: string) {
    return selectJob(db).where('Job.id', '=', id).execute();
}

export function selectJob(db?: DittoDb) {
    const eb = createExpressionBuilder<DB, 'Job'>(db?.getExecutor());

    // prettier-ignore
    return eb
        .selectFrom('Job')
        .select([
            'Job.id', 
            'Job.status',
            'Job.title',
            withUserInfo('Job.ownerId').as('owner'),
            withUserInfo('Job.managerId').as('manager'),
        ])
}

export function withJob(jobIdColumn: any) {
    return jsonObjectFrom(selectJob().whereRef('Job.id', '=', jobIdColumn));
}

export function getJobById(id: string) {
    return selectJob(db).where('Job.id', '=', id).execute();
}

This way I can get the same Job object when I select a job directly or include a Job in another query, like getting a JobApplication. I'm looking for a way to define reusable shapes.

Igal•14mo ago

function selectJob(db: Kysely<DB>) {
  return db.selectFrom("Job").select(["Job.id", "Job.status", "Job.title"])
}

const rows = await selectJob(db).execute()

const rows2 = await db
  .selectFrom(["User", selectJob(db).as("Job")])
  .selectAll()
  .execute()

function selectJob(db: Kysely<DB>) {
  return db.selectFrom("Job").select(["Job.id", "Job.status", "Job.title"])
}

const rows = await selectJob(db).execute()

const rows2 = await db
  .selectFrom(["User", selectJob(db).as("Job")])
  .selectAll()
  .execute()

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

autobotkilla•14mo ago

oh. That's pretty simple. Thanks!!

Gaming

Programming

Is there a way to execute an ExpressionBuilder?