DenoDB • Hacker News Board

Re: DenoDB

#191

Post by nine_k » Sat, Jun 19, 2021, 3:10 AM UTC

supermatt wrote: post #98

nine_k wrote: post #91

Earlier quoted context omitted.

No, ORMs do much more. They turn slices of resultsets into objects. They lazy-load related objects instead of using one efficient join and doing one query; they in general have trouble representing results of projections and joins. They fetch all "attributes of the object" when you need to select a couple of columns from two dozen. They make the objects mutable, and introduce dirty state without transactional control…

Sounds like all your gripes are from a naive usage of ORMs. Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof). Theres a lot more in the docs than you will see in "toy examples". Of course, not all ORMs are created equally...

Indeed, I have seen a lot of semi-naive usage of ORMs, and enough expert usage that sidesteps the leaking abstractions and drops into non-ORM access.

Table models are hugely useful. "Business object" models, not as much.

Re: DenoDB

#192

Post by ttfkam » Sat, Jun 19, 2021, 3:16 AM UTC

hn_throwaway_99 wrote: post #179

zomglings wrote: post #119

Earlier quoted context omitted.

The "Stop using knex.js" blog post misses a very important point in favor of using knex.js: database migrations. Migrations allow a development team to synchronize database schema changes across multiple branches of development. It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python). As far as knex is concerned, you can use it to manage migrations without using it in your application c…

I don't really think that blog post misses that, I just think that DB migrations functionality is very different from SQL builder functionality, and that blog post is referring to the SQL builder part of this. FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements. This was discussed at length in this slonik issue, https://github.com/g…

Idempotent SQL updates are my preference.

``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ```

Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state.

Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)

Re: DenoDB

#193

Post by ttfkam » Sat, Jun 19, 2021, 3:29 AM UTC

handrous wrote: post #154

Osiris wrote: post #12

I hate ORMs with the fury of a thousand suns. The problem is that I know SQL but now I have to spend a bunch of time trying to figure out how to convert SQL into ORM X just so it can convert it back to inefficient SQL. SQL mostly translates between various databases but ORMs are unique and you have to learn a new API for each one. I'm on a project using TypeORM and it has been fantastic at helping developers on my te…

Agreed. My reasoning: 1) Experience. I've seen so many terrible performance issues because of ORM-centric programming. Talking pages that take tens of seconds or a couple minutes(!) to load when they should take under 5s. 2) Ditto on the "ORMs apparently encourage people to write terrible schemas" observation. 3) "Database-agnostic" (not strictly an ORM thing and not required for an ORM, but strongly associated with…

"Database-agnostic" is a pervasive and pernicious lie. It almost always means "lowest common denominator amongst all supported databases".

Some databases are so far behind, they really shouldn't be abstracted by the same library. They have VERY different use cases and abilities. The access model for a SQLite-based app is quite different from a Postgres-based app.

It's like having an F-150, a Cybertruck, and a Prius while hiring a driver that won't take either off-road or for more than 100 miles at a time because he also has to be able to drive a Nissan Leaf the exact same way.

But folks still hire him because he claims to handle anything with a steering wheel and pedals. Technically true, but misses the point of the different options.

That's an ORM.

Re: DenoDB

#194

Post by ttfkam » Sat, Jun 19, 2021, 3:40 AM UTC

Aeolun wrote: post #151

redact207 wrote: post #60

Earlier quoted context omitted.

I find people hate/are ok with ORMs based on how they're used. If you're using it to ad-hoc query your db then it's understandable you'll hate it - a leaky and poor abstraction over sql. Probably a bad fit. Projects where I've seen it work well is when most of the logic is in the app with per row/per aggregate changes. In these apps it's only used for the "object relational mapping" side of things - ie to marshal typ…

> I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt. Just to offer a counterpoint. Every project I worked on that did not have automatic migrations was extremely flawed in other ways as well. Manually keeping track of your DB schema, and indeed, seeing it as something separate from the code that needs to interact with it is a bad idea in my opinion. It’s same…

That's why I prefer idempotent SQL DDL updates. A disk full of up/down scripts makes me nervous.

Most folks test their up scripts. They almost NEVER adequately test their down scripts, so you're left with this false sense of security moving forward even though revision 63 of 64 has a bug in the down portion…which you only find when you're trying to revert to the state of rev 60.

As for ORM migrations, of course they work. They've dumbed down your use of the database to the lowest common denominator (looking at you, MySQL).

IF EXISTS and IF NOT EXISTS are your good friend. Run the script, the database will be at the target state. When all databases are past a certain point, remove the appropriate ALTER TABLE IF EXISTS ADD COLUMN statements.

Makes source diffing much easier, is a consistent single source of truth, and allows for pruning old parts as needed.

Re: DenoDB

#195

Post by ttfkam » Sat, Jun 19, 2021, 3:41 AM UTC

kyleee wrote: post #29

29athrowaway wrote: post #28

Earlier quoted context omitted.

The premise of an ORM is that you will be saving time using simple abstractions. But in reality, those abstractions will be leaky, and you'll be spending significant time trying to understand what the ORM is doing, and finding workarounds for ORM problems. Also, each time something fails, there will be one more moving part to troubleshoot. Once you troubleshoot your problems and analyze what the ORM is doing, you'll…

Yep, and most projects probably don't switch underlying DBs so that part of the allure of an ORM is wasted

Yes! 100x this!

Re: DenoDB

#196

Post by ttfkam » Sat, Jun 19, 2021, 3:43 AM UTC

supermatt wrote: post #98

nine_k wrote: post #91

Earlier quoted context omitted.

No, ORMs do much more. They turn slices of resultsets into objects. They lazy-load related objects instead of using one efficient join and doing one query; they in general have trouble representing results of projections and joins. They fetch all "attributes of the object" when you need to select a couple of columns from two dozen. They make the objects mutable, and introduce dirty state without transactional control…

Sounds like all your gripes are from a naive usage of ORMs. Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof). Theres a lot more in the docs than you will see in "toy examples". Of course, not all ORMs are created equally...

All uses of any technology are naive given enough members on a team.

Re: DenoDB

#197

Post by ttfkam » Sat, Jun 19, 2021, 3:50 AM UTC

lifthrasiir wrote: post #82

mozey wrote: post #70

Earlier quoted context omitted.

> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to In theory, in practise my experience is the oppose. It's easier to understand and tweak the SQL to work across DBs. You can easily diff and compare the SQL files. The ORM is another moving part, it adds convenience for simple queries, and complexity for anything advanced or non-standard.

SQL dialects vastly differ especially past SQL-92. And those differences tend to be very annoying (e.g. RETURNING). There is a reason that people do not generally work across different RDBMSes and instead pick and stick to a single RDBMS because it is really a PITA; I do dislike ORMs for usual reasons, but a bare SQL is not the alternative either.

And yet the strategy most ORMs take is the common denominator where you have no idea if it'll actually use RETURNING or fire off a completely separate query with a potential race condition because SELECTs go to the read-only replica.

(Ask me how I know about this kind of problem with ORMs.)

Re: DenoDB

#198

Post by ttfkam » Sat, Jun 19, 2021, 3:58 AM UTC

phplovesong wrote: post #93

threeseed wrote: post #64

Earlier quoted context omitted.

> SQL is universal No it's not. A small subset of it will work consistently across databases. But if you want to get the most of your database then in almost cases you will be working with proprietary SQL. And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.

I mean universal in the sense that going from mysql to postgres is really not a big shift. I have worked mainly with postgres/mysql but i would imagine i would be up and running at full speed in days/a few weeks with mssql if i ever chose it for a project. sure there are syntax differences, but the "how do i do this" translates very well across databases.

How do you make event triggers in MySQL?

How do you make a system-versioned table/query in PostgreSQL?

How do you port regular expressions to MS SQL Server?

How do you set up an exclusion constraint on a range type (without race conditions or invalid data) in anything except Postgres?

Most relational databases fit niches that the other don't. No one RDBMS is best, but they are most certainly not interchangeable.

Even using the lowest common denominator SQL means you'll be missing out on major performance boosts because they each have their own hints/shortcuts.

Re: DenoDB

#199

Post by ttfkam » Sat, Jun 19, 2021, 4:06 AM UTC

hnbad wrote: post #122

phplovesong wrote: post #59

During my years as a dev i have really started to dislike ORMs. They always fail in the end. SQL is universal, and transfers between languages and tech fields. This is why im pro-sql, and always try to avoid unnecessary abstractions. I have actually went back to writing pure SQL in files, and using those as params for whatever db engine i use, this makes it even possible to reuse the code in other projects (even its…

I agree that SQL is generally superior to using an ORM. However I'd argue that SQL is a lot harder to learn and significantly harder to master than any given ORM for most developers. It's very easy to write code for basic queries that performs terribly when writing SQL by hand, whereas ORMs will frequently produce code that is reasonably optimized. In many situations the performance difference between SQL and an ORM…

I wonder if you've ever worked on a Hibernate project that's more than a couple years old.

Once you exhaust the limits of what the ORM author deemed important (or within their ability to adequately deal with), you are left needing deep knowledge in both the ORM and SQL to debug/optimize.

Also, SQL knowledge transfer well between different engines. ORMs tend to be unique snowflakes with very few common patterns beyond the impedance mismatch that is object-relational mapping.

Re: DenoDB

#200

Post by ttfkam » Sat, Jun 19, 2021, 4:15 AM UTC

If you want an ORM, use GraphQL. It has the one thing missing in all other ORMs: it's a spec, not an implementation. Don't like one vendor, swap in another without breaking upstream services. Seriously, folks really underestimate the benefits of a published spec over a sea of inconsistent implementations.

Learn one ORM, you're SOL when the new team uses a different one. Time to start over again. Got a bug in your ORM? Hope they fix it, because migrating to another ORM is more painful than migrating SQL syntax. Need to work around the ORM? Why do you even have an ORM?

Can you imagine browsers if they didn't standardize on HTTP? Tied to a particular vendor's server or having ten different wire protocols competing?

That's where ORMs are now without a spec. It's insanity.