I was recently considering the difference between using raw SQL and using a query builder like knex.js for writing dynamic queries so that I get any level of granularity for filtering database tables.
Is it bad practice to allow for any level of granularity for user input in a REST API as long as I consider the implications of allowing this, and build in dynamic permissions for user roles? Is there a better way to do this?
An example would be some endpoint let's say /book where it has several database fields and the frontend may want to search by any subset of the fields and if the underlying data changes the API will dynamically use the fields passed in as search parameters if they exist.
And for editing/creating is it bad practice to use the same level of granularity provided that the user permissions are considered for editing resources?
My reasoning for this design is so that the application can have several different frontends and each frontend may use resources in different ways. This seems like it would simplify all of these use cases to one REST endpoint.
I've never received an answer as to how to create useful REST APIs that can be utilized efficently by thick clients with high granularity and all examples that I've seen seem to hard-code field names into backend services. Would it make more sense to have a factory for creating granular CRUD endpoints for simple objects and only more complicated endpoints need to have hard-coded logic for processing.
Ex as an express.js middleware:
bookController.get = async (req, res, next) => {
//assume this was validated in another middleware but came from the req.query object
//could contain any subset of db fields
const query = res.locals.query;
try{
const books = await getBooks(query);
res.json(books);
}
catch(e) {
next(e);
}
}
//assuming that the db functions would dynamically create a sql query then execute it
async function getBooks(query) {
return await db.query('books').select('*').where(query);
}
