Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Flyweight: An ORM for SQLite

github.com

Post reply on HN
Print view View original

61–70 of 105 posts

Re: Flyweight: An ORM for SQLite

#61

Post by matharmin »

chrsig wrote: post #18
MichaelCollins wrote: post #4

const fights = await db.fights.get({ cardId: 9, titleFight: true }); translates to select * from fights where cardId = 9 and titleFight = 1; Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

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 som…

With SQLite, I've started using json_each on a JSON parameter for bulk inserts or updates. Other SQL databases should have something similar.

It's much cleaner than generating SQL, and doesn't run into issues with exceeding the maximum number of parameters.

Re: Flyweight: An ORM for SQLite

#62

Post by JustLurking2022 »

sgt101 wrote: post #59
mrweasel wrote: post #36

Earlier quoted context omitted.

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…

I've never bought this : why not user.updateGroups('new@example.com','admin') Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base. I've had some terrible experiences with them...

That is an atrocity on so many levels.

For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.

Re: Flyweight: An ORM for SQLite

#64

Post by plugin-baby »

matharmin wrote: post #61
chrsig wrote: post #18

Earlier quoted context omitted.

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 som…

With SQLite, I've started using json_each on a JSON parameter for bulk inserts or updates. Other SQL databases should have something similar. It's much cleaner than generating SQL, and doesn't run into issues with exceeding the maximum number of parameters.

This sounds very neat. Do you have an example handy?

Re: Flyweight: An ORM for SQLite

#65

Post by zzzeek »

randomdata wrote: post #57
MichaelCollins wrote: post #4

const fights = await db.fights.get({ cardId: 9, titleFight: true }); translates to select * from fights where cardId = 9 and titleFight = 1; Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

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 theoreticall…

it is so refreshing to see the correct answer stated so succinctly, even though it's buried in the middle of yet another one of these "duh, ORMs suck, write raw SQL" threads (isn't everyone here bored of these discussions yet?). congrats on being one of so very few who gets it.

Re: Flyweight: An ORM for SQLite

#66

Post by asalahli »

JustLurking2022 wrote: post #62
sgt101 wrote: post #59

Earlier quoted context omitted.

I've never bought this : why not user.updateGroups('new@example.com','admin') Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base. I've had some terrible experiences with them...

That is an atrocity on so many levels. For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.

> you're creating a hard coupling to a specific flavor of SQL.

I fail to see why this is a problem. Switching databases is a costly move, and is pretty rare as far as I know. When it does happen, it is usually from one type of db to another type, not between two RDBMSs.

IMO it doesn't, by itself, justify sticking to an ORM rather than raw SQL.

Re: Flyweight: An ORM for SQLite

#67

Post by nikeee »

MichaelCollins wrote: post #4

const fights = await db.fights.get({ cardId: 9, titleFight: true }); translates to select * from fights where cardId = 9 and titleFight = 1; Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

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.

Re: Flyweight: An ORM for SQLite

#68

Post by lambdafourtwo »

MichaelCollins wrote: post #4

const fights = await db.fights.get({ cardId: 9, titleFight: true }); translates to select * from fights where cardId = 9 and titleFight = 1; Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.

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.

Re: Flyweight: An ORM for SQLite

#69

Post by ajgrover »

JustLurking2022 wrote: post #62
sgt101 wrote: post #59

Earlier quoted context omitted.

I've never bought this : why not user.updateGroups('new@example.com','admin') Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base. I've had some terrible experiences with them...

That is an atrocity on so many levels. For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.

Many/most ORMs (the ones that follow the ActiveRecord pattern) do this as well though. I prefer to avoid mixing concerns and use datamapper-based ORMs myself, but what GP wrote is fundamentally not that different than what a lot of ORMs do.

Re: Flyweight: An ORM for SQLite

#70

Post by gwbas1c »

JustLurking2022 wrote: post #62
sgt101 wrote: post #59

Earlier quoted context omitted.

I've never bought this : why not user.updateGroups('new@example.com','admin') Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base. I've had some terrible experiences with them...

That is an atrocity on so many levels. For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.

> you're creating a hard coupling to a specific flavor of SQL

You're just trading one coupling (specific flavor of SQL) to another (your ORM.)

Assuming your application is layered correctly, when you write your own queries, all of your SQL queries are in a single place and can be updated.

BUT: If you're using an ORM, and you let your data bound objects leak into all layers, the coupling is much much much harder to fix if you chose to change your ORM. IE, if you do things like lazy loading, or construct your queries in business logic, switching ORMs will be extremely painful.

I've done it both ways (write my own SQL and use an ORM) and I would say the single biggest mistake is to assume that you absolutely should (or shouldn't) use an ORM.

Post reply on HN