Raw SQL query vs Query builders (Knex.js/Objection.js)

raw_sql_vs_query_builders.md

Raw SQL query vs Query builders (Knex.js/Objection.js)

Raw SQL

static async listPostsPublicRaw ({ offset, limit, filter = {} } = {}) {
    assert.integer(limit, { required: true, min: 10 })
    assert.integer(offset, { required: true, min: 0 })
    assert.validate(filter.blogId, BlogModel.schema.id, { required: true })
    assert.validate(filter.stackId, StackModel.schema.id)

    const countWithStackIdQuery = `
    SELECT COUNT("posts"."id")
    FROM "posts" FULL JOIN "stacks" ON "posts"."stackId" = "stacks"."id"
    WHERE "posts"."blogId" = ? AND "posts"."private" = false AND "stacks"."private" = false AND "posts"."isDraft" = false AND "posts"."stackId" = ?;
    `

    const countNoStackIdQuery = `
    SELECT COUNT("posts"."id")
    FROM "posts" FULL JOIN "stacks" ON "posts"."stackId" = "stacks"."id"
    WHERE "posts"."blogId" = ? AND "posts"."private" = false AND "stacks"."private" = false AND "posts"."isDraft" = false
    OR "posts"."blogId" = ? AND "posts"."private" = false AND "posts"."stackId" IS NULL AND "posts"."isDraft" = false;
    `

    const countQuery = filter.stackId ? [countWithStackIdQuery, [filter.blogId, filter.stackId]] : [countNoStackIdQuery, [filter.blogId, filter.blogId]]

    const postsWithStackIdQuery = `
    SELECT "posts".*
    FROM "posts" FULL JOIN "stacks" ON "posts"."stackId" = "stacks"."id"
    WHERE "posts"."blogId" = ? AND "posts"."private" = false AND "stacks"."private" = false AND "posts"."isDraft" = false AND "posts"."stackId" = ?
    ORDER BY "posts"."createdAt" DESC
    LIMIT ? OFFSET ?;`

    const postsNoStackIdQuery = `
    SELECT "posts".*
    FROM "posts" FULL JOIN "stacks" ON "posts"."stackId" = "stacks"."id"
    WHERE "posts"."blogId" = ? AND "posts"."private" = false AND "stacks"."private" = false AND "posts"."isDraft" = false
    OR "posts"."blogId" = ? AND "posts"."private" = false AND "posts"."stackId" IS NULL AND "posts"."isDraft" = false
    ORDER BY "posts"."createdAt" DESC
    LIMIT ? OFFSET ?;`

    const postsQuery = filter.stackId ? [postsWithStackIdQuery, [filter.blogId, filter.stackId, limit, offset]] : [postsNoStackIdQuery, [filter.blogId, filter.blogId, limit, offset]]

    const count = await this.knex().raw(...countQuery)
    const total = Number(count.rows[0].count)
    if (total) {
      const { rows } = await this.knex().raw(...postsQuery)
      return { results: rows, total }
    }

    return this.emptyPageResponse()
  }

Query builder

static async listPostsPublic ({ offset, limit, filter = {} } = {}) {
    assert.integer(offset, { required: true, min: 0 })
    assert.integer(limit, { required: true, min: 10 })
    assert.validate(filter.blogId, BlogModel.schema.id, { required: true })
    assert.validate(filter.stackId, StackModel.schema.id)

    const whereOptions = {
      'posts.blogId': filter.blogId,
      'posts.isDraft': false,
      'posts.private': false,
      'stacks.private': false,
      ...(filter.stackId && { 'posts.stackId': filter.stackId })
    }
    const orWhereOptions = filter.stackId ? {} : {
      'posts.blogId': filter.blogId,
      'posts.isDraft': false,
      'posts.private': false,
      'posts.stackId': null
    }

    const postsCount = await this.knexQuery()
      .count('posts.id')
      .fullOuterJoin('stacks', 'posts.stackId', 'stacks.id')
      .where(whereOptions)
      .orWhere(orWhereOptions)

    const total = Number(postsCount[0].count)

    if (total) {
      const [blog, posts] = await Promise.all([
        BlogDAO.baseGetById(Number(filter.blogId)),
        this.knexQuery()
          .select('posts.*')
          .fullOuterJoin('stacks', 'posts.stackId', 'stacks.id')
          .where(whereOptions)
          .orWhere(orWhereOptions)
          .orderBy('createdAt', 'desc')
          .offset(offset)
          .limit(limit)
      ])

      return {
        results: posts.map(post => ({ ...post, blog })),
        total
      }
    }

    return this.emptyPageResponse()
  }