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 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)
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();
var sql = SQLGenerator.insert(MyObject);
var sql = SQLGenerator.insert(MyObject, MyKlass.InsertableProperties);
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.
Previous discussion: https://news.ycombinator.com/item?id=23273543
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!
As an educator, I’d say the docs are probably the most important element of a library.
Exactly the kind of "use SQL in typescript code with type-safety" non-ORM that I've always wanted.
Sounds like Dapper from the .Net world - which I like precisely for that reason:
If one had to, i'd separate these things in different libraries and let the developer opt in to what he needs.
[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.
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.
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!
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.
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 wrote about tagged template literal on my website : https://cavaleiro.fr/posts/template-literals/
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
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?
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.
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.
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.
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.
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...
So whenever you have reasons to spend some thoughs on the choice of RDBMS, you'll probably not want to abstract SQL away.
Plus it was integrated into feathers, and knex simplifies hook-based query building logic.
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 ).