
Show HN: ORM for TypeScript with no query-builder, supporting full SQL queries - Seb-C
https://github.com/Seb-C/kiss-orm
======
Seb-C
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 ).

~~~
exevp
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.

~~~
areactnativedev
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.

~~~
exevp
> 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](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.

------
midrus
All these things fall short the moment you need real "production" features,
such as reliable migrations (writing them by hand? no thanks. Outsourcing to
another library like knex? no thanks), transactions, community support,
relationship/nested/join queries without a ton of boilerplate and being battle
tested.

So far, the best thing I've found in the node ecosystem is Prisma [1], and
it's better than the alternatives by a very long shot in my opinion.

[1] [https://www.prisma.io/docs/understand-prisma/why-
prisma](https://www.prisma.io/docs/understand-prisma/why-prisma)

~~~
exevp
I find that most of the production features you mentioned are actually more
difficult using a fat ORM.

How many hours have i wasted figuring out how i can write and map some complex
joins or aggregation query with <insert ORM name here>? Would have been a 3
minute task if all i had to write was just SQL ...

Plus i have a hard time seeing the benefit of Prisma. You are learning an
entirely new DSL just to define your schema - which actually isn't that far
off standard JS or TS syntax-wise so it feels like a complete waste of time to
come up with the DSL in the first place. I can only imagine the hard time you
have once you first have to break out of the frameworks cage because you hit a
case which isn't easily solved by the framework itself ...

~~~
2color
Thanks for sharing your thoughts about Prisma.

> Plus i have a hard time seeing the benefit of Prisma

Prisma is supposed to improve your productivity and confidence when working
with a database. It does so with a strong focus on type safety.

Most ORMs and query builders in the Node.js/TypeScript ecosystem do not
provide the level of type safety that Prisma does.

For example in a blog with users and posts (1:n) querying a user and related
posts looks as follows:

    
    
      const user = await prisma.user.findOne({
          where: {
            id: 1
          },
          include: {
            posts: true
          }
        })
    
      console.log(user.posts)
    
    

The _user_ object will have the correct TypeScript type, including the posts
object. If you remove the include object from the _findOne_ call and avoid
fetching related posts, the user's type will change. This approach to type
safety helps in catching many problems at build time.

It's possible because of the Prisma schema, which is the single source of
truth for both the database schema and the generated TypeScript client.

You don't have the burden of mapping between the database schema and the types
in their application. Besides that, it's a declarative and concise description
for the database schema and is database agnostic.

The type safety features come at "zero-cost" because you don't have to write
all the TypeScript types and mappings to the database. Prisma generates the
TypeScript client from your Prisma schema.

> You are learning an entirely new DSL just to define your schema - which
> actually isn't that far off standard JS or TS syntax-wise

The Prisma schema is actually fundamentally different to JS or TS because it's
declarative rather than imperative.

~~~
exevp
I understand where Prisma is coming from with the custom DSL: they want to
guarantee type safety and therefore need to know exactly the structure of the
types the result set is supposed to be mapped to.

In most other languages you'd shout "reflection" but unfortunately, there is
no such thing in TS. Hence the custom DSL so you know, while parsing, what the
structure of the type is.

I'm just asking myself: why invent the custom DSL for that? You could just use
babel to parse TS types. Sure babel is quite the dependency but in a node
environment, that wouldn't be a bigger concern to me then inventing a custom
DSL instead.

You could even use TS decorators to add more metadata like sequences and
(foreign) keys to the TS types.

~~~
2color
> I'm just asking myself: why invent the custom DSL for that?

Fair question. Besides all the type safety features I mentioned above, having
the DSL (Prisma schema) allows generating database clients in more than one
language, e.g. Go without the database declaration being tied to a specific
programming language.

It's also the reason it's declarative in contrast with most ORMs that rely on
an imperative language to define these mappings.

The second reason is that the Prisma schema is used for declarative database
migrations with Prisma Migrate.

The third reason is that Prisma supports introspection of an existing
database. So if you were to introduce Prisma to an existing project, you'd
introspect the database which would populate the Prisma schema based on the
database schema. This would then allow you to use Prisma for migrations.

Could all that be achieved without a custom DSL? Perhaps. But it'd probably
tie Prisma to a specific language ecosystem and would diminish the developer
experience of the features it offers.

I can understand the reluctance around a new DSL, but in reality, I haven't
seen many complaints about the need to use it.

------
Dragory
It seems you cannot load relationships for a collection of entities easily
without N+1 queries, unless I'm missing something. Based on the many-to-many
section of the docs ([https://github.com/Seb-C/kiss-orm#many-to-
many](https://github.com/Seb-C/kiss-orm#many-to-many)), I would have to load
relationships for each entity separately, and then if they have further nested
relationships, run a query for each again. The subsequent section also
mentions eager loading is not supported.

For me, being able to load relationships (and especially nested relationships)
with little boilerplate and few queries is probably the most useful feature in
an ORM (usually explicitly eager-loaded), so I'm sad to see it's not
supported.

~~~
WkndTriathlete
Agreed on the N+1 query problem, but I'm a bit mystified why people still
choose ORMs for any projects with even a moderate level of database
complexity. When using a straight SQL layer (JDBC or the basic features of
KISS-orm) the query is in SQL form and the performance characteristics of the
query are obvious from the query or can be analyzed easily by taking the query
and running it through the database's query analysis tools. Using an ORM just
adds extra steps: instead of optimizing a query in SQL, the query needs to be
optimized using directives or methods or annotations that the ORM provides in
the _hope_ that the SQL that is ultimately generated is efficient; that is,
we're programming the ORM which programs the database instead of just
programming the database. Why bother with the extra step? With modern
programming languages there really isn't that much extra boilerplate to
implement the DTOs for straight SQL and it usually results in code that is a
lot easier to maintain and extend in the examples I've seen.

~~~
Dragory
In my experience, 99% of the relationships I fetch fit the basic one-to-one,
one-to-many, many-to-many definitions that pretty much all ORMs support. For
these cases, the queries are generally more than efficient enough and there's
little reason to reinvent the wheel and implement the code for fetching those
relationships yourself.

For anything more complex, I agree. But for the common case of fetching simple
and often (depending on your project) nested relations, I definitely enjoy the
abstraction provided by ORMs.

------
holgerw
Thank you for creating Kiss ORM. I have even created a HackerNews account to
be able to comment on it.

I have been searching for this type of ORM in Typescript for a while. I agree
to write raw SQL for queries. So easy and expressive and one less layer of
abstraction. I also agree on the value of the respository pattern and methods
for CRUD operations to not write this SQL by hand. Making the loading of
associations an explicit decision is also the right way to go. The Rails
community has good experience with performance surprises of automatic loading
of relationships.

Personally I found the most useful ORM in Ecto for the Elixir (Erlang)
language:
[https://hexdocs.pm/ecto/Ecto.html](https://hexdocs.pm/ecto/Ecto.html)
(ignoring the query capability). It follows very much the repository pattern
like Kiss ORM. The API is a bit more succinct (you only define a schema for
each table and use a generic repository instead of subclassing for each table)
but that might be possible only due to Elixir's language capabilities like
meta-programming.

One piece of Ecto that might be a win to implement in Kiss ORM is the
"Changeset" pattern to give a canonical, succinct and productive solution to
validate data
([https://hexdocs.pm/ecto/Ecto.Changeset.html#content](https://hexdocs.pm/ecto/Ecto.Changeset.html#content)).
For example have a look at how Ecto unifies validation (checked without
hitting the DB) and constraints (checked by hitting the DB) in a single API.
This type of functionality increases the usefulness of the repository's CRUD
operations.

Thank's for your initiative to create KISS ORM. I will sure try it out and
follow along it's evolution.

~~~
holgerw
Just forgot to mention Ecto's "Multi API", that is worth knowing. Allows to
construct a chain of operations as a data structure and to execute it later
transactionally. You may even include operations that are part of
transactional business logic but that do not hit the DB (like sending an
email). ([https://hexdocs.pm/ecto/Ecto.Multi.html#module-
run](https://hexdocs.pm/ecto/Ecto.Multi.html#module-run))

As I understand KISS ORM's sequence function would also allow to express
business logic transactionally and operations beyond the DB. Obviously the
rollback would only effect the DB, but other failing operations can at least
trigger the rollback, right? I think this is usefull as integration with
external services (like email providers, payment APIs..) are really the source
of runtime surprise that might fail a business operation and demand a DB
rollback.

~~~
Seb-C
This multi-api indeed seems similar to the sequence function.

If you can try-catch the failure in the external service, you can rollback the
transaction with kiss-orm. Actually kiss-orm does not abstract the transaction
itself, so you can do whatever you want.

I just realized a flaw in my current implementation, which is that directly
using the repository CRUD methods from inside the sequence (rather than a
query) function would execute those operations outside the scope of the
sequence.

------
golergka
Love to see more options in this space! I've made a switch from traditional
ORM (TypeORM and Sequelize) to a similar "light ORM", Pgtyped, and never
looked back since.

Like in Kiss, you write queries in SQL. But unlike other "light ORMs", it also
provides type safety by generating type declarations by directly connecting to
your database instance and type-checking your query templates.

Honestly, I think it's the best of both worlds, and would love to see more
developers finally learning SQL and ditching "fat ORMs" that try to hide it
under abstraction layers that always end up leaking.

~~~
davecardwell
I agree. I’ve been using pgtyped for a recent project, along with
[https://github.com/graphile/migrate](https://github.com/graphile/migrate) for
migrations, and it has been great to just write SQL and not have to battle
with an ORM’s abstractions.

------
timmy-turner
When using raw SQL in strings, I really miss the automatic formatting that is
provided for HTML, TSX and TS with prettier.

Raw SQL query strings also do not compose well and I miss auto completion when
writing them (yes, I'm a spoiled kid after so much Typescript usage).

As with everybody else, I didn't like existing ORM/builder approaches, so I
built and use my own with type-inference: [https://github.com/hoeck/typesafe-
query-builder](https://github.com/hoeck/typesafe-query-builder). Any feedback
would be great because I have the gut feeling that this one has gone way too
far on the type astronaut side of things.

~~~
hv42
You should be able to do this with IntelliJ e.g. where you can inject a
language into a string. This is quite handy as you can reformat and open the
string in a separate editor if needed.

The caveat is that it does not work well if you are composing the SQL queries
from multiple small parts.

see [https://www.jetbrains.com/help/idea/running-injected-sql-
sta...](https://www.jetbrains.com/help/idea/running-injected-sql-
statements.html)

I suppose that other editors or IDE can do similar things.

------
wayneftw
I started using Objection.js and Knex last year and I think it might be the
best ORM I've used on any platform.

There's no way I'm going back to writing raw queries. If I did that,
eventually I'd rewrite Knex.

If and when I need a raw query, I can already do that with Knex.

------
CoffeeDregs

       sql"SOME SQL";
    

This looks to be a "tagged template":
[https://basarat.gitbook.io/typescript/future-
javascript/temp...](https://basarat.gitbook.io/typescript/future-
javascript/template-strings). I'm a TS user but hadn't seen that feature
before. AFAICT, the library is using a side-side-side-feature (tagged
templates) of TS as the core abstraction [1]. Impressive. Might be a little
brittle in the face of significant SQL or query composition?

Anyhow, I'm a heavier-ORM user but that's impressive.

\--- [1] [https://github.com/Seb-C/kiss-
orm/blob/c4b6c9ad2f81337938a9c...](https://github.com/Seb-C/kiss-
orm/blob/c4b6c9ad2f81337938a9c609b4bc9ef05b08c300/src/Queries/SqlQuery.ts#L10)

~~~
exevp
Not specific to TS, just one of the modern JS features:
[https://developer.mozilla.org/en-
US/docs/Web/JavaScript/Refe...](https://developer.mozilla.org/en-
US/docs/Web/JavaScript/Reference/Template_literals)

Scroll down to the part explaining how to use custom tags.

------
blaufast
I think its funny that 'opinionated' could be considered a virtue. I'm
imagining a charity that uses 'opinionated' to describe itself, or even worse,
a person. Even Apple, a brand famous for its strong design stances, does not
use that word as a description of its values.

------
ryanmarsh
This is very nicely done, kudos to the author. I'm sure many will find this
useful.

Personally I've gotten away from using SQL RDBMSs. Since I primarily build on
AWS I use DynamoDB but the same principle would apply elsewhere. I like to
store data in the format that best supports the read model. Event sourcing
allows me to change the structure of the read model, or add new read models,
or vary strategies depending on data, as needed. I like that I no longer have
the impedance mis-match of the normalized relational model.

It was a big jump to make, and I had to unlearn a lot. I'm not dismissing the
value of RDBMSs at all. I love them, especially star schemas for analysis. I
just want to share that _it 's ok_ to not use an relational model for your
transaction store.

------
capnorange
still yet to find an ActiveRecord equivalent for javascript.

~~~
racedude
What about this?
[https://github.com/typeorm/typeorm/blob/master/docs/active-r...](https://github.com/typeorm/typeorm/blob/master/docs/active-
record-data-mapper.md#what-is-the-active-record-pattern) (Forgive me if this
isn't what you are looking for, not super familiar with ActiveRecord myself,
just recalled seeing that yesterday!

~~~
jdauriemma
Yes, TypeORM is definitely the JavaScript answer to ActiveRecord

~~~
agustif
Can vouch for TypeORM, hopefully it's creator will pick it back up soon and
get it to a stable v1.0.0!!

------
haakts
Seems similar to PureORM[1]. It allows you to write regular native SQL and
receive back properly structured (nested) pure business objects.

The name pureORM reflects both that it is pure ORM (there is no query builder
dimension) as well as the purity of the mapped Objects.

[1] [https://github.com/craigmichaelmartin/pure-
orm](https://github.com/craigmichaelmartin/pure-orm)

------
gigatexal
As a never-ORM guy I really like what this is trying to do. It seems like the
best of both worlds. Kudos!

~~~
Seb-C
I am glad that it is appreciated! Thank you.

------
nhumrich
I actually built something almost exactly like this internally. This is
awesome. But, its a far cry to call this an ORM. Its just a safe query
serializer. ORM takes an object and writes the query for you.

------
LorenzA
the template syntax reminds me of an other postgres libary
[https://github.com/porsager/postgres](https://github.com/porsager/postgres)

------
nesarkvechnep
There's still nothing like Elixir's Ecto in JS land...

------
cryptica
History keeps repeating itself. People never seem to learn anything. At first
there were no ORMs, then ORMs became extremely popular and everyone was using
them, then everyone learned that ORMs were a very bad idea and we stopped
using them, vowing never to make the mistake again... And here we are again in
2020, ORMs are back. They will be in for a while, then out again, then in
again....

Same with statically typed vs dynamically typed. First everything was
statically typed, then dynamically typed (interpreted) languages gained
popularity, developers LOVED not having to wait for the compiler to finish to
test their changes; this was a revolution... Now again, we're going back to
statically typed languages, everyone uses clunky bundling and transpilation
tools which add bloat and everyone is happy to wait 20 seconds to a minute for
their code to compile.

Every few years, developers believe the opposite of what they believed before
and the consensus seems to be close to 100% every time. It's ridiculous.

~~~
lemontruth
That is because many people believe in extremes. After a while people should
learn that there is no absolute truth, everything has it's place.

ORM is good sometimes, pure queries are better on others. So what is needed is
more like relaxed ORMS.

