Flyweight: An ORM for SQLite (opens in new tab)

You're coming at it from a slightly wrong angle. You are completely right that for queries, there's really no gain, you just end up having to learn both SQL and whatever your ORMs DSL is.

Where ORMs are useful is once you have your objects. The usefulness of an ORM is being able to say:

  user.email = 'new@example.com'
  user.groups.append('admin')
  user.save()

Also being able to work on your data in objects or structure native to your programming language means that you can leverage both the strengths of the language and the database server. Some operations are much simpler to do in Python or C#, compared to SQL. In other scenarios you really need to let the database do its thing. Again, you do have a point, because believing that you can skip learning SQL and just rely on the ORM will get you into trouble.

One other feature of ORMs is that they allow you to write code that will run on multiple databases, but at the cost of not being able to use the more advanced database features. For many CRUD applications that is a price worth paying though.

I mostly agree. I think sql's achilles heel in this regard is where prepared statement parameters are needed but aren't supported, or need a better representation

- during bulk inserts, having to generate a list of values. It'd be wonderful to be able to just supply a single `?`, or use some other symbol to note that it's a value list. Making the user generate a bunch of (?,?),(?,?)... is not at all friendly, and something everyone has to do. and the cherry on top is that there can't be a dangling comma at the end, so it's gotta be chopped off, or omitted.

Not at all a hard problem. It's an annoying problem that I don't understand why it hasn't been solved at the prepared statement level.

- things like database, table, or column identifiers that may be variable based on application context

Basically anywhere that currently winds up getting interpolated should have a way to be parameterized.

without those two, i think it's inevitable to arrive at one of:

- an orm

- a sql query template renderer

- a bunch of functions to do very specific string interpolations

ORM is orthogonal to SQL. The purpose of ORM is to transform relations (sets of tuples) to object graphs and back again. ORM toolkits provide some kind of declaration method to describe how that mapping should occur to save you the slog of doing it by hand.

SQL is the usual mode for receiving those relations, and so many ORM toolkits also include query builders to help with that level of abstraction, but theoretically an ORM toolkit could require you to write SQL in the raw.

You would really like sqldelight[1] then. It takes the concept of an ORM and flips it on its head. Instead of mapping function calls to SQL statements, it lets you write SQL statements and then generates classes for you that have methods for those statements.

For instance, you could have a SQL statement like getCardsForFight: select * from fights where cardId = ? and titleFight = ?, and it would generate a class that has a method getCardsForFight(cardId: number, titleFight: number).

[1]: https://github.com/cashapp/sqldelight

They are indispensable when you need to build queries dynamically. Suppose you have some sort of list UI where users can query for data and filter down on rows they are interested in. They can filter on one of a dozen different columns in any combination. They can also sort on whatever column they want. There's no way you can implement this without building the SQL select statement dynamically, adding to the WHERE clause based on what the user wants to filter things. So you either start appending strings or you reach for an ORM.

I agree that often the full ORM isn't very helpful, I prefer something that's more of a light layer over SQL to enable dynamic query building and that's it.

It's not as if you're expected to use ORMs to do ad hoc querying of a database. Just fire up a db connection and start writing sql.

If you work on a large application with lots of transactional processing, it makes sense. I've worked on apps with 10's of 1000's of lines of Ibatis scripts. And on applications where you're expected to just invoke stored procedures for every little operation. I'll take Hibernate over that situation any day.

People really overstate the pitfalls of ORM's. If you're running reports against a data warehouse, don't use an ORM. If you are trying to optimize a complex query, don't use an ORM. For an app that is write heavy, with very many simple updates to a complicated object graph, it makes sense.

> Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL

I'm with you on that. I use ORM's, yet, being perfectly comfortable with SQL my perspective ends-up in a range between "this complexity isn't worth it" and "why not just write this in SQL?".

One of the arguments for ORM's is being able to move to different db engines. Frankly, I can't remember the last time I had to do that for a mature/released application.

Once again, I'll admit my perspective is biased because SQL isn't a problem for me. When I look at ORM code it looks and feels very detached from the database. I look at SQL and everything is clean and clear. In addition to that, you don't have to create and manage a bunch of objects that take-up memory and slow things down. Adding layers of abstraction isn't always the best idea.

> Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

I tend to agree, I'm more partial towards Micro-ORMs, ideally with a 'natural' DSL; something that is a good blend of 'SQL'-ness and 'target_language'-ness.

I can think of SlickDb (Scala), Linq2Db (C#), Ecto (Elixir) was good if definitely 'elixir-y'... IDK what other languages have such magical things; I know all of the above lean heavily on certain language features to provide their magic.

In the case of SlickDb, while I don't write Scala I read a lot of it, and could always understand what it was doing. In the case of Linq2Db, it just plain has a DSL that is 'close enough' to SQL. I love it and have saved multiple teams from EF with it. Ecto, as mentioned above... it wasn't -bad- but definitely walked the line of 'just write SQL' to me. (I'll admit however I am at best a good elixir app -maintainer-...)

In general,

- 'object tracking' is bad; I have yet to see a good use case where it doesn't wind up masking scope of modifications in the long run.

- 'overly native' DSLs are usually bad; the closer a DSL is to the 'metal' of SQL, the less likely you are to be surprised by the generated SQL (e.x. the 'surprises' of linq2Db tend to be far fewer than in, say, Entity Framework)

- If your language doesn't have a good DSL-providing MicroORM... try to find a basic MicroORM that just takes SQL, and write tooling around it.

It's more about the Mapping than the querying. Luckily good ORMs let you fetch your objects using... SQL, which is a fine language for querying (doh):

    for p in Person.objects.raw('SELECT * FROM myapp_person'):
        print(p)

>Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

You are not alone on that sentiment. The opaque nature of the translation often result in poor performance, and to me the apparent ergonomics gains are not worth the trouble either (at least in most cases).

Yeah, they suck and we have moved on.

Nowadays we still use libraries to abstract over SQL dialects and generate SQL in a typesafe and convenient way, but it's not an ORM in the sense that it maps from the object oriented domain into the relational one and back.

One of the major benefits of ORMs was that you’d write a query once and it would work on any relational database or nosql data store. Imo it’s kind of pointless when an ORM only targets one specific database.

Your example is a bit disingenuous. A SQL query isn’t native in most programming languages, so you’re missing a lot more boilerplate code

That's what I feel like, too. Every ORM that I've worked with is a separate DSL that I need to learn. Also, abstracting away the database is something I don't get - why abstract away something that I explicitly chose because it does something different than the other alternatives?

I've yet to encouter a project where I'd need to switch to a different database. Even if that happens, there is likely some raw SQL that someone wrote because the ORM didn't do something as expected. Or some part of the code uses DB-specific ORM features that don't map to different databases. The only thing I can imagine where this would be useful is when you don't have control about what DB is being used, for example, when building a product that should be compatible with Postgres and MariaDB (and each is getting used). However, in the age of containerization, this isn't a big problem any more.

In some ORMs, I need to create types that the result of a query containing JOINs is mapped to. Others don't support them _at all_. In TypeORM, there is a query builder which forces you to put in _some_ SQL for things like "WHERE a in (b, c)". Most ORMs I've used have a cumbersome handling of relations, for example when I need to specify which relation should be fetched eagerly.

I created a proof of concept of a different approach: Just embrace SQL and provide static typing based on the query. The return type of a query is whatever that thing is that the query returns in the context of the database schema. It's possible to do in TypeScript, by parsing the SQL query at development time:

https://github.com/nikeee/sequelts

One benefit is that it does not need any runtime code, as it's just a type layer over SQL. You don't have to rely on some type-metadata that TypeScript emits. That's why it also works with JavaScript only. You don't have to fit every result into some type - it just returns an interface that can be used wherever you want. That's especially useful because TS's type system is structural.

One major downside is that it's rather complicate to implement a parser and evaluation of the result type in TypeScript's type annotations. A different story is debugging type-level code; it's basically try-and-error. Providing error messages in case a query is wrong is also something that needs work. That's why it's only a PoC.

I think it's intrinsic to the problem. It was a popular topic back in the day. https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...

Probably partially because you (like me) know SQL pretty well. I'm dealing now with an application at $currentjob whose employees are really, really good at Ruby on Rails' ActiveRecord, and the Rails/AR code they come up with seems to me INCREDIBLY complex, taking (I think) more lines than the equivalent SQL would. And not really any more readable. But they're very much into do it the Rails way because Rails says you should.

I think it's one reason that I'm leaving at the end of next week.

Sure, this is a simple example -- and you are right, if you are just writing a few selects, you may not find any value.

ORMs (or query-builders, as some like to draw a distinction there) become more valuable as you use them to compose and transform queries. SQL is decidedly not composable.

One big point is the breaking of the chicken/egg cycle on whether or not the DDL has been run.

This is expanded when migrations are brought into the project.

We can train the data model to have a single management point, that being the file where the ORM classes are defined.

Your code example is just wrong. You didn't use parameter binding. You didn't even execute the query. You didn't even store the returned rows in a usable structure.

You are missing the steps to get that select statement into JavaScript (running the database command, passing in the parameters, parsing the result, etc).

I don't like ORMs but there is a benefit here. Your where clause is re-usable. You can assign it to a variable and use it again somewhere else.

but orms and sql aren't competing at the same task, so comparison isn't really meaningful is it?

one thing is trying to present an internal dsl for context dependent data access, the other is a generic text api to represent queries and commands.

the analogy would be comparing aws java sdk, and commenting that HTTP rest apis are so simple why would anyone use the sdk?

I said it once many years ago and I still say it: "ORMs are for people who don't know SQL"

However, I understand the problem it tries to solve: Object-Relational Impedance Mismatch. I only wish there were other idioms, not the convolution of classes and mappers.

Redbean is really nice. Long ago I wrote a python/sqlite implementation as an exercise. Module here: https://gist.github.com/johndoe46/40c0f993a641fe7a3eb74bd6d6...

Also tests: https://gist.github.com/johndoe46/17eacf0f12772dfb870732479b...

(This is proof of concept quality, don't use for real work)

ORMs where you don't write sql make me nervous, though it doesn't help the main version of this I used was raw linq-to-sql (not Entity Framework), and it could be very hard to convince linq to generate the correct sql for what I was doing (I once had to write my relationships backwards else it kept generating sub queries).

But .NET also has Dapper where it lets you write all the SQL and then it just handles the binding of data into objects, which having that handled for me is great.

I loved redbean for a while. Sort of a mix of 'nosql' up front, but it's being baked in to a relational DB. I did a number of small projects extremely quickly with redbeanphp. You're making me want to go explore it again :)

EDIT:... well... I see it's still got an aim of being PHP5 compatible. Upgrades for PHP 8.1, but that seems off a bit. I seem to remember composer was an issue... and there still is. Impressive that the author is still evolving and supporting it :)

ORM is a great way to scale yourself.

People usually switch to ORMs because they want to use in-language primitives for their application of choice. It's jarring and inelegant to switch between SQL and javascript or something like that.

The problem with an ORM is that it's a high level abstraction On top of what is ALREADY a high level abstraction: SQL. ANd it's not even a one to one abstraction... they are very different and this actually adds more complexity when it comes to optimizing SQL.

You optimize SQL with hacks to get it to compile into an efficient query. With an ORM you have to hack the ORM in order to hack the sql in order for it to compile into an efficient query. It's nuts.

The fix for this problem is to not use an ORM. You want to use in language primitives? Make an abstraction that is one to one with SQL. A library that gives primitives that directly represent SQL language primitives. That's what we actually all want. We don't actually want an orm.

That's pretty great because getting types with SQL is a massive pain in the rear! I have looked at some other libraries in the past but they all tend to use a build step to generate their types.

Not really, they (I) wanted `insert(Item)` that just inserted the item into the table.

I don't really care about the query, just that it's inserted correctly, along with other operations that an ORM provide (get/delete/update,etc)

I think the data mapping part is pretty important: a programmatic way to map from SQL result sets to objects. That stuff is incredibly tedious (and error prone) when you do it manually.

What’s a good solution for that without a classic ORM?

This doesn't look the same at all. The point of flyweight seems to be use code generation based on sql code; sqlitedao seems to be more like a normal orm.

You can use ts-sql-query [1]. It has a complete query builder API, but you can also use sql fragments similar to slonik. SQlite is supported along with most other mainstream databases.

[1] ts-sql-query.readthedocs.io/

MikroOrm is a pretty fantastic project IMO.

It uses Knex as the query builder so maybe you can just use that directly: https://knexjs.org/

Although, I would use Mikro still to manage schema and migrations. Then drop down to Knex: https://mikro-orm.io/docs/query-builder#using-knexjs

Yes. Yes it is. So much so that I will, as much as possible, try to not use an ORM that creates queries for me.

Simple ORMs that map columns in rows to attributes or properties in an object are fine. ORMs that handle complex relationships and migrations and the rest (a la Entity Framework, Hibernate, ActiveRecord), are all pretty much a vote of no confidence from me in any project.

In an ORM, you will write

  include: ['posts']

in SQL, you will write

  join posts on p.authorId = a.id

I would argue an ORM is more about getting the flat structure of the result set into an hierarchical set of objects with more complex types.

it's npm.

Jokes aside, as someone who wrote a python sqlite ORM (shameless plug: `pip install sqlitedao`), my reason was to have a minimal ORM for personal project, the entire active source is contained in one file and it works for majority of the use cases (i.e. insert_item, get_item, etc).