Multi params query handling

[akermabon]

Hello,

First of all, thank you for your great work, I've been playing around with the library for a while and I'm considering using it in production.

Before that I would like to know if it's possible to build requests depending on params.
An exemple is better than a long talk so here it is:

async function getUsers (filters) {

    const conditions = [];
    const params = [];

    if (filters.name) {
        conditions.push('name = ?');
        params.push(filters.name);
    }

    if (filters.age) {
        conditions.push('age = ?');
        params.push(filters.age);
    }

    let sql = `
        select * from users
        where active = 1
    `;
    
    if (conditions.length) {
        sql = `${sql} and ${conditions.join(' and ')}`;
    }

    // getSQL is an util function which converts '?' to numbered params
    return db.query(util.getSQL(sql), params);
}

How would you do that (building a query conditionally) using your library ?
Thank you :)

[iamdimka]

use unsafe(sql: string, params: any[])

[akermabon]

Doc says

Unsafe queries sql.unsafe(query, [args], [options]) -> promise

I'd like my requests to stay safe so I guess I can't really use that 😞

[porsager]

@akermabon Some cases can be handled, but it's an area I'm trying to improve currently so you don't have to fall back to using sql.unsafe.

sql.unsafe is named this way so you're alert, but you can write safe queries using it if you know how :)

An example for dynamic select and condition would be this:

const conditions = {
  age: 'someid',
  name: 'rick'
}

const select = ['age', 'name']
const params = []

await sql.unsafe(`
  select ${ 
    select.map(x => escape(x)).join(',') 
  } 
  from users
  where ${ 
    Object.entries(conditions).map(([k, v]) =>
      escape(k) + ' = ' + '$' + params.push(v)
    ).join(' and ') 
  }
`, 
  params
)

The problem with this approach is the very imperative nature and also knowing how to go about it and ensuring eg. column names are escaped properly.

[akermabon]

@porsager Thank you for taking the time to show me this but if I have to escape myself, it becomes basically worse than using pg so I'll wait until your proper solution gets out before switching 😄

[porsager]

Ah, I wouldn't know about worse, since the same goes for pg. It doesn't have any way of doing safe query building at all afaik, so if you're doing dynamic string concatenation without escaping user input you're making unsafe queries there too.

[porsager]

But yeah, it hopefully shouldn't be long until I find time to tackle this so if you watch/follow this issue #12 you'll be notified as soon as that happens ;)

[EDjur]

Hey @porsager. Thanks for creating this project! 🙏

I've been trying to understand the docs and from what I can gather, a query similar to the one in this issue still isn't easily done without utilising the unsafe method?

    if (conditions.length) {
        sql = `${sql} and ${conditions.join(' and ')}`;
    }

I have a similar use case where I want to build a value list dynamically from an arbitrarily long list of conditions.

[e3dio]

#529 (reply in thread)

[EDjur]

That works great, thanks @e3dio ! 🙏

[rajasekar-d]

@akermabon Below BaseModel class may help you for dynamic nested queries

const sql = require('../libs/postgres.lib');

class BaseModel {
    
    constructor(table){
        this.table = table;
    }

    _orderBy(orderBy){
        return Object.entries(orderBy).map(order=>{
            return sql`${ sql(order[0]) } ${ order[1].toUpperCase() == 'ASC' ? sql`ASC` : sql`DESC` }`;
        }).reduce((acc, x) => {
            return sql`${acc}, ${x}`;
        });
    }

    _toAnd(array){
        return array.reduce((acc, x) => {
            return sql`${acc} AND ${x}`;
        });
    }

    _toOr(array){
        return array.reduce((acc, x) => {
            return sql`(${acc} OR ${x})`;
        });
    }

    _toSql(obj){
        return Object.keys(obj).map(key => {
            if(key == '$or'){
               return this._toOr(obj[key].map((obj)=>sql`(${ this._toAnd(this._toSql(obj)) })`));
            }else if(key == '$and'){
                return this._toAnd(obj[key].map((obj)=>this._toAnd(this._toSql(obj))));
             }else if(typeof(obj[key]) == 'object'){
                const [op] = Object.keys(obj[key]);
                const [value] = Object.values(obj[key]);
                if(op == '$ne'){
                    return sql`${sql(key)} != ${value}`;
                }else if(op == '$gt'){
                    return sql`${sql(key)} > ${value}`;
                }else if(op == '$gte'){
                    return sql`${sql(key)} >= ${value}`;
                }else if(op == '$lt'){
                    return sql`${sql(key)} < ${value}`;
                }else if(op == '$lte'){
                    return sql`${sql(key)} <= ${value}`;
                }else if(op == '$in'){
                    return sql`${sql(key)} IN ${sql(value)}`;
                }else if(op == '$nin'){
                    return sql`${sql(key)} NOT IN ${sql(value)}`;
                }else if(op == '$l'){
                    return sql`${sql(key)} LIKE ${value}`;
                }else if(op == '$nl'){
                    return sql`${sql(key)} NOT LIKE ${value}`;
                }else{
                    return sql`${sql(key)} = ${ value }`;
                }
            }else{
                return sql`${sql(key)} = ${obj[key]}`;
            }
        });
    }

    _toWhere(where){
        return this._toAnd(this._toSql(where));
    }

    create(record = {}, options = {}){
        return sql`
            INSERT INTO ${ sql(this.table) } ${ sql(record) }
            ${ options.returning ? ( sql`RETURNING ${ (options.columns ? sql(options.columns) : sql`*`) }`) : sql`` }
        `.then(([row])=>row);
    }

    find(where = {}, options = {}){
        return sql`
            SELECT ${ options.count ? sql`COUNT(1)` : (options.columns ? sql(options.columns) : sql`*`) } 
            FROM ${ sql(this.table) } 
            ${ Object.keys(where).length ? sql`WHERE ${ this._toWhere(where) }` : sql`` } 
            ${ options.orderBy ? sql`ORDER BY ${ this._orderBy(options.orderBy) }` : sql`` } 
            ${ options.limit ? sql`LIMIT ${ options.limit }` : sql`` } 
            ${ options.offset ? sql`OFFSET ${ options.offset }` : sql`` } 
        `;
    }

    findOne(query, options = {}){
        options.limit = 1;
        return this.find(query,options)
        .then(([row])=>row);
    }

    findById(id, options = {}){
        options.limit = 1;
        return this.find({ id },options)
        .then(([row])=>row);
    }
    
    update(where = {}, update = {}){
        update.updatedAt ? (update.updatedAt = sql`CURRENT_TIMESTAMP`) : null;
        return sql`
            UPDATE ${ sql(this.table) } 
            SET ${ sql(update) }
            ${ Object.keys(where).length ? sql`WHERE ${ this._toWhere(where) }` : sql`` } 
        `;
    }

    updateById(id, update){
        return this.update({ id }, update);
    }

    exists(where = {}, options = {}){
        return sql`
            SELECT EXISTS(
                ${ this.find(where, options) }
            );
        `.then(([row])=>row.exists);
    }

    count(where = {}, options = {}){
        options.count = true;
        return this.find(where, options)
        .then(([row])=>row.count);
    }

    delete(where = {}){
        return sql`
            DELETE ${ sql(this.table) }  
            ${ Object.keys(where).length ? sql`WHERE ${ this._toWhere(where) }` : sql`` } 
        `;
    }

    deleteById(id){
        return this.delete({ id });
    }
}

Just extend the class with your table name in constructor or create the BaseModel instance with table name

class User extends BaseModel{
    constructor(){
        super('Users');
    }
}
const user = new User();

or

const user = new BaseModel('Users');

Usage

user.find({
    id: {
        $nin: [1,2,3,4,5]
    }
},{
    columns: ['name'],
    groupBy: ['name'],
    orderBy: [
        ['name','DESC']
    ],
    limit: 10,
    offset: 3
});

user.find({
    $or: [
      { id: 1 },
      { id: 2 }
    ]
});

[MendezAndrewM]

None of the above suggestions solved for my use case but I was able to solve by passing in 'AND' as a conditional argument

const conditions = {
  id: '123',
  name: 'jon'
};

const dbResponse = sql`
  SELECT * FROM table_name
  WHERE
  ${conditions.id ? sql`id = ${conditions.id}` : sql``}
  ${conditions.id && conditions.name ? sql`AND` : sql``}
  ${conditions.name ? sql`name = ${conditions.name}` : sql``}
  ${conditions.age && conditions.id || conditions.name ? sql`AND` : sql``}
  ${conditions.age ? sql`age = ${conditions.age}` : sql``}
`;