Hacker News Reader

Comment by jjice | Hacker News Reader

The biggest thing holding me back from moving to Djagno or Rails is the DB interaction. As someone who is comfortable and prefers raw SQL, do I just have to man up and embrace the ORM for everything but situations where I _need_ to make raw queries?

I've also grown like sqlx (both the Rust and Go versions, despite them not being related as far as I know). The methods for mapping directly to structs work really well and captures one of the biggest issues I've had (literally the object-relational mismatch).

I feel like I'm in a state of decision fatigue with web frameworks. I've used many shallowly, but I just never know if I'm making the right decision. It's probably safe to assume the best bet is the one I choose and stick to though...

View on HN

ActiveRecord (Rails ORM) provides an interface for raw SQL queries if that's really what you want to do (`connection.execute`). You can use it whenever you want. You can also swap out ActiveRecord for something else like DataMapper (or whatever) if you really want.

But, the whole point of using a framework like Rails is to let the framework make certain decisions for you, so you can develop quickly according to convention, and not think of lower-level technical decisions. Bypassing the ORM certainly works against that.

DO NOT USE `connection.execute`. It does no kind of escaping. It's fine (and encouraged) to use in migrations but `ActiveRecord::Base.find_by_sql` is what you want for production code. I wish this was made more clear because it's crazy how much I've seen the former in production code.

SQL injections on code without an ORM is just a function of how many developers, are working on the project, eventually someone will write something that goes around the escaping.

I mean sure, but if you're already working around the framework's ORM...

Breaking from the ORM isn’t ideal but if decide you need to in a or two or two that you want to, that isn’t an excuse to write in secure code.

I think the main thing about using an ORM (and I believe that Rails' active record is among the very best ORMs out there) is that you should never just blindly let it run without inspecting the queries it produces.

I'm constantly monitoring the logs during development to make sure it's doing exactly what I want. And the odd time when it's not the answer is usually within ORM api itself and doesn't require me to shift to raw sql.

This is why I never understood the argument that ORMs are for people that don't know SQL (not to suggest you were making such an argument). If anything being proficient at SQL makes you that much better at handling the abstraction that the ORM provides and that much better at examining the resulting queries.

You can use raw sql in django where it matters and let the ORM handle standard CRUD operations (https://docs.djangoproject.com/en/4.0/topics/db/sql/). I usually start with the ORM and if I need to do something complex or if there's a slow query I just rewrite it in SQL when it becomes an issue.

Also if you'd like to manage the tables with SQL there's an easy way to generate the models from the database schema (https://docs.djangoproject.com/en/3.2/howto/legacy-databases...). I've used it before at a startup to migrate from hand coded schemas and migrations in SQL that only one person was brave enough to touch (they had no way to run things locally) to a full consistent REST API for like 20 tables ready in 2 days.

I'm also managing an SQLite db on a mobile app that uses hand written SQL (with 20ish tables and a few 100 queries) and any changes to the database end up being a nightmare of manually editing raw strings.

If you've never really gotten over the hump of going all-in with an ORM, I think it's worth giving it a shot. It's a different way of thinking than always-raw-SQL, and it can be uncomfortable at first, but I've found it ultimately freeing to be able to offload nearly all the SQL I had to write back in the day. YMMV.

This is where I was before I picked up Rails. What I realised was that while I preferred SQL in general, what I actually cared about was the ability to really go to town on the 5% of queries that actually needed attention, and the other 95% were boilerplate and entirely unsurprising. Letting the framework make those ones just go away under a layer of syntax I didn't even have to specify, but that got automagicked up from the database schema, was a breath of fresh air.

I believe DB interaction is the principal value in web frameworks, the primary productivity boost. Easily turning ephemeral in-memory data into queryable ACID records unlocks a ton of important features for web apps.

However, most of the other features of these frameworks are just bloat, added complexity that doesn't really make things easier than writing vanilla $lang with rock-solid libraries.

For example, in Go: URL routing, request handling (middleware), html templating, websocket communication are all trivial to implement with standard or well-known libraries. But it's the DB interaction where things get tricky and very code-heavy.

I'm trying out an approach, leveraging pgx and the new Go generics, where I define a simple generic Wrapper struct with some low-hanging fruit methods: CRUD, filtered+pagified lists, unique constraints, etc.

How? `encoding/json` and Postgres JSONB columns. The generic Wrapper is hardcoded with a simple and flexible table schema: an id column and a JSONB column (I also added some timestamp metadata columns as an experiment).

The end result is that I implemented a dumb "ORM" with around 200 lines of idiomatic Go, and can do the following:

wrapper := Wrapper[MyStruct]{pgx.Conn, "mytable"}

record := wrapper.Insert(MyStruct{some,real,data}) // returns a generic Record object which includes the DB id and pointer to the (actual, not interface{}!) struct, as well as other metadata perhaps

record.Data.myField = time.Now()

wrapper.Update(record)

for i, record := range wrapper.List(Params{}) { \\ do stuff with all the MyStructs }

...and so on and so forth. The goal here is not to implement a perfect ORM (e.g. pointer/FK spaghetti) but to secure that initial productivity boost of easily ACIDifying my data. As my data model gets more mature, I can individually migrate these "weak" JSONB schemas to proper, (de)normalized, relational Postgres schemas and define their data access appropriately.

Have you heard of Ecto? You can run raw sql, and even if you use the library methods they're similar to sql

Reply on news.ycombinator.com