Hacker News Reader

Comment by Seb-C | Hacker News Reader

For a long time, I have been frustrated with the state-of-the-art about the existing ORMs. I like things to be simple, but somehow all ORMs seems to be bloated and overcomplicate a lot of things.

When designing a new project, I have been trying to find a more satisfying design while avoiding the existing ORMs. I especially wanted to use proper SQL rather than reducing it's syntax to fit it in another language.

This is the result of my experiments. This is a completely new balance between productivity, simplicity and readability, while providing useful features.

I use the template-string tagging syntax to allow writing and concatenating raw SQL queries, while keeping it safe from injections, which allowed me to build kiss-orm. There is no query-building involved, you can freely use the full-power of SQL.

I would appreciate feedback on this (and contributions if you love it :D ).

View on HN

Still not very convinced your ORM is solving a problem it didn't create but I certainly like the approach more than more traditional ORMs.

I feel like Im not really the type that wants an ORM to take care of SQL or relational functionality, all I really want is an object mapper at the edges, going in, I want to pass an object and have it map into the right fields, coming out, I want it mapped to the right place.

Doing stuff like preloading or relation loading should be done through convention or by database schema querying.

Its a tricky problem to solve, while some relational mapping is super helpful to prototype it almost always results in a mess down the line where just writing SQL upfront prevents pain later.

I sort of agree here. I tend to stay away from ORMs in general, but I'd probably use one if it actually justified itself. Most ORMs I've used basically just abstract the data layer so you don't have to write own SQL, but that in itself comes at a cost (and almost all of them are bound to eventually write some really crappy SQL for you and cause performance bottlenecks). If there's not an appreciable benefit besides just not having to write as much SQL, then it's not really worth using.

In one way or other you cannot avoid the SQL builder, this ORM is building it in the user's code base.

I believe in ballance,

you need the query builder for simple queries (that makes most of your queries)

but the complex queries should be also supported (even if they are not portable)

We have written exactly this kind of ORM https://github.com/holdfenytolvaj/pogi

It saves you a lot of code writing, but is not taking away the power of postgresql (however it is anchored to postgresql)

Personally, all I want is something that can dynamically build SQL and can hydrate into specific objects.

I have no interest in automatically building relationships, I'm completely fine doing that manually, or not at all. Which is why dapper is probably my favorite DB solution out there.

    return conn.query<MyKlass>("select * from my_klass_table where status = :status", new { status=myStatus }).ToList();

The only thing that can be bothersome sometimes is inserts and updates. It would be really nice to have something that could auto-generate the SQL for you.

    var sql = SQLGenerator.insert(MyObject);

    var sql = SQLGenerator.insert(MyObject, MyKlass.InsertableProperties);

It would take care of one of the downsides of raw SQL, which is schema updates needing to be dealt with in multiple places, and would remain super simple.

I mean hell, if you wanted to get fancy you could create a generator that would figure out the columns necessary by reaching out to the DB the first time and caching it afterwards, and using a naming convention to map to the actual properties. But personally, I'm 100% fine not having that.

Jdbi works the way you describe: https://jdbi.org/

Yep, I've been enjoying JDBI for a new microservice. /me makes sign of cross and throws holy water on legacy Hibernate code it's replacing.

My own TypeScript non-ORM, Zapatos, shares much of the same design philosophy (and indeed a sql`...` tagged template function): https://jawj.github.io/zapatos/

Previous discussion: https://news.ycombinator.com/item?id=23273543

thought this looked awesome when I saw it. Was waiting for a little more traction, my project to calm down and perhaps some GQL helpers to appear before jumping on board (not requesting them but just naturally picked up by the community)

Glad to hear it! Presumably GQL is GraphQL, which so far I have let completely pass me by. How would those helpers look, roughly?

Zapatos is amazing. I'd definitely use it if I were writing Node backends, I think its design is wonderful.

Also, I'm impressed by how beautiful and interactive your docs are. How did you do that? The show imports button, the embedded monaco modal.. Did you just code that up yourself just for these docs, or is this "just" some nice documentation template that I'm not aware of? Either way, very nice!

Thanks. I just coded it up myself[1]. :)

As an educator, I’d say the docs are probably the most important element of a library.

[1] https://github.com/jawj/zapatos-docs

Wow this is amazing. Really bridges the last remaining gap to close the type-safety gap from SQL -> TS/Node.JS -> GraphQL -> TS / client code.

Exactly the kind of "use SQL in typescript code with type-safety" non-ORM that I've always wanted.

"you can freely use the full-power of SQL"

Sounds like Dapper from the .Net world - which I like precisely for that reason:

https://github.com/StackExchange/Dapper

OP's project is a little more opinionated than Dapper in that it defines repositories and whatnot. Dapper's more like just a set of query extensions that support basic object mapping. I love it personally, but I wouldn't even call it a micro-ORM.

It might be a good idea to focus on the use of template strings for safe and handy SQL generation instead of introducing too many opinionated ORM concepts.

If one had to, i'd separate these things in different libraries and let the developer opt in to what he needs.

That is something I seriously considered, but I just did not want to bother with multiple packages and repositories, especially for a repository class that is about 150 lines of code.

The repository class that I provide is a very basic and handy abstraction for the 4 basic CRUD operations, but ultimately the goal is to write your own queries and repositories.

Thankfully [0], it [1] already [2] exists. [3]

[0] https://github.com/gajus/slonik-sql-tag-raw

[1] https://github.com/felixfbecker/node-sql-template-strings

[2] https://github.com/blakeembrey/sql-template-tag

[3] https://www.npmjs.com/search?q=sql%20template

Edit: When I first read your comment, I thought you were saying that you wanted a separate library just to have the SQL template functionality.

I agree with what you're saying though.

This is great. I built something a bit lower-level than this targeting MySQL not long ago. This library has taught me a couple language features I didn't know, however. The SQL tag is pretty clever. I was pleasantly surprised to see transaction support (I feel like people who don't actually use their libraries in real products tend to leave this kind of thing out).

I noticed the support for soft-delete (which seems to be a simpler thing in PgSql than in other relational db systems), which is also nice. I think another fairly easy, generic win would be a way to specify that a model has audit tracking fields (createdAt/By, modifiedAt/By, deletedAt/By, etc.). I know some database servers also have a way to track this separately (no idea whether PgSql specifically does), but there are use cases for showing that kind of stuff at an application level as well (and also can make ETL type jobs a bit less painful).

All in all, great work. It looks very polished!

Thanks! It was a bit tricky to solve the transactions problem, because I did not want to abstract the BEGIN / COMMIT / ROLLBACK instructions, but at the same time I needed to provide something to ensure the integrity of a transaction made of multiple commands.

As for the audit fields, I decided not to include it because it very simple to implement, and would be clearer to do it specifically. Most of those fields could be implemented by just adding a default value in the right function.

Another interesting way to solve the transaction problem is following more of a Unit of Work pattern. Then the transaction is more or less held until the unit is committed. Or rolled back. This is also wonderful for writing integration tests because your repositories all contribute to the same UoW and your test can just roll back when it’s finished to return to a clean state.

In Ruby, I have always been extremely fond of the Sequel ORM - http://sequel.jeremyevans.net/

The amount of expressiveness and capability it gives is simple outstanding. It lets me drop into pure SQL as well as write statements which are simpler and easire to grok when I'm using the ORM level abstractions.

Sequel for SQL users - http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html

Cheat sheet - http://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdo...

I'm extremely happy with Django's ORM. It never felt bloated or overcomplicated to me, especially when compared with the innate verbosity and complexity of SQL.

I've been playing with tagged-template for a while now and have a similar solution at work. I wanted to build something like that for a while now, well done!

I wrote about tagged template literal on my website : https://cavaleiro.fr/posts/template-literals/

What do you think about ORMs like Androids Room[1], where you do specify your own queries with sql (by annotating abstract methods) and can bring your own model and the ORM creates the database schema in the database and gives you access to easy insertion methods.

I don't know how Room does it but darts floor library(which is similar)[2] then generates the code that is necessary, in addition to a very slim runtime layer for managing update events. I find that this is also an approach which in some ways only does half the work for more flexibility, while still depending on a fixed database schema.

[1] https://developer.android.com/training/data-storage/room

[2] Shameless plug: https://github.com/vitusortner/floor

Thanks. I do not have experience with Room, but I usually prefer to control and know what is happening in my schema.

For example, if your schema is automatically handled, how do you both rename and change the type of a column without losing data? Would it perform a delete and a create?

Only the creation is handled automatically. Room helps with migrations by enforcing a database version but you will have to write the migration code(sql) yourself.

thanks. I love it! It is not clear where the `article.id` comes from in many-to-many example. Anyway I don't think I would use those relationships patterns, instead I would just load and operate in plain (shallow) model objects.

Thanks! It was a copy/paste mistake. I just fixed the README file.

tl;dr: thank god it finally has been done.

Long version: i've been seriously frustrated with the state of ORM (in Javascript in particular) for years.

Javascript ORM are nice and handy if all you're doing is simple CRUD stuff. If you're starting with more complex relational queries (we're using an RDBMS so why wouldn't we?) you quickly reach the limits of what the ORM can map. If you start doing more complex aggregations or stuff like window functions and the likes, you most certainly have to fallback to raw queries, usually rendering the whole mapping function of the ORM completely useless.

Also projects like Knex.js (or for example HQL in the Java world) look nice at first but are mostly useless IMHO because they just replace SQL with another syntax you have to learn. Why stay with the language everybody familiar with RDBMS can speak if you can invent some useless abstraction, right? And please don't tell me you want to support multiple RDBMS in the same codebase. How often is this really an important use-case?

I really loved the way MyBatis did this in Java: instead of mapping tables to objects, mapping result sets to objects and leaving the full power of SQL to the developer.

Always wanted (and actually started something almost the same as you did some weeks ago) to basically do MyBatis in Javascript and never had the time to.

Thanks for getting it started.

Very surprised by the no value you attach to Knex, I'm curious to get your view on the values I see in using it for a few years now.

I feel at ease with SQL and like to get as close to it as possible in my Node service. But Knex still appears to be highly valuable to me to, for instance, not care about managing DB connections, at least until they become critical for my use-case.

Not care about sanitising inputs and protecting myself from SQL injections.

Have more readable and maintainable code in my repositories than SQL in plain strings as a default. Yes I have some raw queries but 98% of my queries are easy to follow knex chains.

Not care about creating and maintaining code for migrations. Running them in transactions, keeping track of and running only the ones needed, ... so happy I didn't have to re-invent that and be the responsible of it never ever failing in production.

> not care about managing DB connections, at least until they become critical for my use-case.

That's something the db driver usually does. E.g. when using Postgres, the pg library already comes with the connection pooling. Haven't looked into the implementation in Knex but i'd suspect they just use the Pool class of pg (https://node-postgres.com/features/pooling).

> Not care about sanitising inputs and protecting myself from SQL injections.

That's also not that much of a concern when just binding parameters.

> Have more readable and maintainable code in my repositories than SQL in plain strings as a default. Yes I have some raw queries but 98% of my queries are easy to follow knex chains.

Comes with the cognitive cost of maintaining another abstraction for SQL.

> Not care about creating and maintaining code for migrations.

That's actually the one feature which made me use Knex for years (just for the migration part of course :) ). I didn't use the schema builder functions mostly, just a bunch of `knex.raw` calls in the migration files. But for the benefits you mentioned (transactions, bookkeeping) it is really useful.

Thanks for your comment!

As you say, one of the main arguments for query-builders is allowing the support of different RDBMS. But in my experience it not only never happens, but the abstraction is never perfect and makes any unsupported edge case impossible to solve without hacking around.

There is so much that can be done with SQL that would result in a mess of inefficient spaghetti code...

Also one of the bigger reasons why i dislike SQL abstractions: you usually pick your RDBMS for a reason, because of some vendor-specific features, extensions or something. Abstracting SQL away makes end up with the smallest common denominator which defeats the purpose of choosing one specific RDMBS.

So whenever you have reasons to spend some thoughs on the choice of RDBMS, you'll probably not want to abstract SQL away.

IMHO one of the key use cases for query builders like Knex is that you can programmatically build up a query in a very clean fashion.

Otherwise you need to resort to workarounds like concatenating strings, or building the biggest possible query and using boolean toggles to disable parts of your query.

I don't undertand your frustration. Virtually every ORM I know has a query builder which as a .raw() function that lets you do anything you want.

We actually went with knex/objection specifically because there was a planned migration to postgres that it would simplify, and we'd be able to migrate different databases in stages.

Plus it was integrated into feathers, and knex simplifies hook-based query building logic.

Reply on news.ycombinator.com