Hacker News Reader

Comment by balls187 | Hacker News Reader

This is slick, especially the API explorer, but my biggest gripe about these orm's is that once you get past the very basic CRUD, you need to execute custom sql.

Does it support custom DB types?

For example, I want my primary keys to be bigint on Mysql, and not have to override the underlying dbdriver to do the mapping. I'd prefer to set the appropriate dbtype in the model, and have the driver pass it along directly.

Also does it support dbdriver level SQL query execution?

I have custom aggregation query across a self join, and that's not easily represented with static methods on a model.

Today I use node-orm2, with a custom SQL script that updates the identity columns to be bigints, and it supports executing raw sql queries (with some basic parameter sanitizing).

View on HN

Has the same problem using Bookshelf (which built on Knex). The query language breaks down almost immediarely. Need to use PostGIS primitives? Awkward. Need to perform aggregate functions with group by, or add dynamically computed columns to the select? Impossible. Subqueries in the select? Nope. Need to order by a function? Impossible, too.

These libraries fail because they are not designed around a logical expression language (unlike Ruby's ARel, a very nice SQL building library that forms the underpinnings of ActiveRecord). You need to be able to programmatically build expressions as an AST which still preserving SQL language rules and avoid exposing yourself to injection.

> The query language breaks down almost immediarely.

Agreed. We dropped an ORM completely and use SQL in functions (aka stored procedures). For models we use plain JS objects and for validation we use Joi (https://github.com/spumko/joi) from Walmart Labs.

Eliminating an ORM has been freeing. We did have to invest in upgrading our team's SQL skills, but we no longer run into limitations of an abstraction layer. Our queries are faster as there are fewer layers. Now that we're up to speed on SQL we find it to be expressive for our data access needs.

1. We support custom type mappings between JS and DB, see:

http://docs.strongloop.com/display/DOC/Model+definition+refe...

2. We allow you to execute native query using dataSource.connector.query(query, params, cb). We're considering to promote it to dataSource level and fully document it.

Hooks would be nice for this. It's what Kinvey does and I think it's the best option. Want to run a special query or a special function? Have a hook that listens on the related queries, alter anything you like. You should also have direct access to raw database connection there, which is again, what Kinvey does.

Reply on news.ycombinator.com