Refactor Query Builder

[faustbrian]

I recently added https://github.com/fix/ark-core/blob/master/packages/core-database-sequelize/lib/query-builder.js to have a clean and fluent way of building and executing raw queries.

It was just an initial version to quickly replace Sequelize methods with raw queries and should be refactored to provide more power and dry it.

The builder should provide a fluent interface for building, escaping and executing raw queries through Sequelize.query().

A very basic draft of the Query Builder could look like the following.

class QueryBuilder {
    select (columns = '*') {
        //
    }

    from (table) {
        //
    }

    as (alias) {
        //
    }

    where (column, operator, value) {
        //
    }

    whereBetween (column, from, to) {
        //
    }

    whereNotBetween (column, from, to) {
        //
    }

    whereLike (column, value) {
        //
    }

    whereNotLike (column, value) {
        //
    }

    whereIn (column, value) {
        //
    }

    whereNotIn (column, value) {
        //
    }

    whereNull (column) {
        //
    }

    whereNotNull (column) {
        //
    }

    whereKeyValuePairs (conditions) {
        // Remove - Make it possible to pass in objects to .where()
    }

    whereStruct (conditions) {
        // Remove - Make it possible to pass in objects to .where()
    }

    whereRaw (query) {
        // Remove - This is currently only needed because of the prototype state of the builder
    }

    orWhere (column, value, operator = '=') {
        //
    }

    andWhere (column, value, operator = '=') {
        //
    }

    groupBy (column) {
        //
    }

    orderBy (column, direction) {
        //
    }

    skip (value) {
        //
    }

    take (value) {
        //
    }

    all () {
        //
    }

    first () {
        //
    }

    raw (query) {
        //
    }

}

The down side of this approach is that everything is in one class and not clearly separated. A better approach is to have a basic builder that has many Concerns that will provide the underlying functionality of select, where, group and whatever else is needed.

Concerns would look like the following and are easy to extend.

class WhereConcern {
    constructor (column, operator, value) {
        //
    }

    between (column, from, to) {
        //
    }

    notBetween (column, from, to) {
        //
    }

    like (column, value) {
        //
    }

    notLike (column, value) {
        //
    }

    in (column, value) {
        //
    }

    notIn (column, value) {
        //
    }

    null (column) {
        //
    }

    notNull (column) {
        //
    }
    
    or (column, value, operator = '=') {
        //
    }

    and (column, value, operator = '=') {
        //
    }
}

class QueryBuilder {
    ...

    where (column, operator, value) {
        return new WhereConcern(column, operator, value)
    }

    ...
}

This way we will be able to build concerns for each part of a query and provide a fluent interface like the following.

query
    .select('username')
    .from('wallets')
    .where('balance', '>=', 5)
    .and('address', '=', 'test1')
    .or('address', '=', 'test2')
    .first()

[luciorubeens]

Nice, this could also be an independent package faustbrian/sequelize-query-builder 👍

[faustbrian]

I can't steal all the good things 😂

This needs be part of core as other plugins should make use of it instead of using the Sequelize methods directly which causes massive overhead compared to raw queries.

It's very easy to kick a node into a fork by corrupting the database with a bunch of resource hungry queries to the blocks table by using Sequelize methods instead of raw queries.

Will work on that tomorrow or friday, should be a quick one.