5 ms·
So frustrating. You got like 80% of the way there, and then went "nope, too much work" and diverted to add more complexity. The answer is to write the SQL your
by marcus_holmes 5y ago
So frustrating. You got like 80% of the way there, and then went "nope, too much work" and diverted to add more complexity.
The answer is to write the SQL yourself, and the scan methods yourself. Code generation is better than ORM, but still a wrapper, still adds complexity, and still brings problems.
Yes it's a pain in the arse to write all that boilerplate in one go (pun intended). But if you'd started without an ORM you'd have written them one at a time as you needed them and barely noticed it.
Keeping your code aligned with your database schema is very little effort - database schema changes are usually rare and significant.
I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). It takes maybe 20 mins to write the whole set each time I have to add a feature with new data, which is a rounding error in the time it takes to write all the rest of it.
The point is that the database schema is optimised to storing data in the best way possible. The middle layer is optimised for processing data in the best way possible, and the front end is optimised for displaying data in the best way possible. None of these three things are equal. Use an interface between each of them. The interface is important and needs to be carefully considered.
- e12e 5y ago> I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). Database view and stored procedures?
- marcus_holmes 5y agoyeah. Postgres has functions, SQL Server has sprocs.
- butMyside 5y agoWhat if there are no layers and you can’t think outside hierarchy? There’s no reason for anyone else to take your view as sacrosanct since many many many apps and services are working just fine without ORMs. You might accomplish something interesting today if you set aside protecting unimportant ephemera you talked yourself into believing.
- 5e92cb50239222b 5y ago> I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons). God how I hate supporting such architecture. It loses edit history unless you're very careful (which many developers are not) and do absolutely every piece of DDL in a versioned migration, and makes DDL extremely painful because you frequently have to drop two dozen functions and views (which depend on each other recursively) and then recreate them.
- aidos 5y agoWe’ve started using views and friends a lot more recently (now I’ve recovered from my PTSD from a lifetime ago when I worked in a poorly managed sql server shop). As you point out, they’re often not first class citizens. On the flip side, we’ve just moved ours to files on disk (one per object) and hacked in some tooling to autogenerate the migrations from them (we use alembic / sqlalchemy which already does a good job here). It’s kinda the promised land. Edit code on disk, commit and review as usual.
- marcus_holmes 5y agothis. Don't write your SQL into the database. Write them as scripts (starting with "drop XYX", then "create XYZ", check them into git, and treat them as code. Migrations are for schema changes. Views and functions are not schema.
- 2StepsOutOfLine 5y agoI'm shocked more people arent talking about SQLBoiler in threads like these. It solves this exact problemset. You write the SQL schema and it generates all the scan and helper functions for you. We've had a great experience with it at work after running into similar woes as OP with ORM's. https://github.com/volatiletech/sqlboiler https://github.com/volatiletech/sqlboiler
- mattcwilson 5y agoWhat are your thoughts on what the article author has to say about SQLBoiler?
- zeroc8 5y agoFor larger and longer running projects, I prefer sqlc. There is zero magic involved, but it still generates the boilerplate for you. If for some reason you decide you don't like it, you can just pretend it never existed.
- irq-1 5y agoPutting all SQL into the database gets you two huge advantages: First, all relations and dependencies are visible for DDL changes, and what the code uses is easy to see without looking through the code. Second, a test database can use (near) live data with the new DDL and new code, making testing much better especially for non-development staff who can spot issues based on the data or UX.
- domlebo70 5y agoWhat is the idea behind using views? I have never used them
- mcny 5y agoNot OP but two main things: 1. Access control - you can create new SQL accounts and only give them access to select from specified views 2. Encapsulation and implementation hiding - the underlying table structure can change as long as the views exposed doesn't change. At least that's all I know. I'm sure people will add or correct when they read this.
- noisy_boy 5y agoThese are correct - some others are: - ability to do transformations uniformly e.g. if you want to represent a value in a certain way uniformly instead of having to do that in multiple places - ability to get generated values (like 'create_ts() - now()' to get the age of the record) directly from the database instead of computing it repetitively - move the join mechanisms on the database side to do those efficiently instead of the ORM having to do it (sometimes suboptimally); also helps with joins that are repetitive - ability to apply certain "default" filters e.g. if you are always fetching with predicate 'active = true', then you can include that in the view
- macintux 5y agoI used views several months ago when I implemented a tracking database for AWS SSM parameters: I wanted to retain a history of all changes to the parameters over time, and the schema was complex enough that queries for simple things like “what’s the current value?” weren’t simple at all. So, I wrapped common queries in views. (None of this should be taken as advice for how to approach such a problem.)
- imposterr 5y ago> I write a view for each access method (so I can change the schema without worrying about changing every access method), and a function for each update/insert/delete (for the same reasons) I’d appreciate it if you could expand on what this means exactly.
- unclebucknasty 5y agoNot OP, but sounds like they're using views for reads and functions (stored procs) for writes. Idea is to insulate code from schema changes as much as possible. If the underlying schema changes, it's possible to sync the views/functions to those changes so the code itself isn't impacted, as it's just talking to the views and functions. Kind of an exposed interface to the DB. It's not perfect, and some schema changes will invariably require code changes, but should be minimized. There are pros and cons to this approach and I personally don't favor it, although I am aware of some shops that made it an absolute requirement--especially pre-ORM.
- perl4ever 5y agoI spent years writing ad-hoc SQL, and had no occasion to learn about or use ORMs, but I did dabble a little in T-SQL and PL/SQL, and they just seemed wrong to me. Not integrated in a clean manner, not derived from a single vision, and so on. I guess what people call an "impedance mismatch". There is excessive overhead just from switching from SQL to the imperative language. I've gotten four orders of magnitude speedup by reformulating a T-SQL script as a single SQL query. But recently I learned about Power Query (the "M" language standing for Mashup) and I immediately wanted so badly for SQL to acquire all the good aspects of it. Not as a separate module, but perhaps as a successor language. In particular, the way it allows functions (and higher-order functions) to be defined is wonderful. Many people don't like SQL, but I never had the feeling that here is what it's missing until now. I qualify my comments above by mentioning I have no experience with OLTP or in general SQL that needs to be performant on the millisecond to second timescale. Instead, I've always been working with queries on the basis that they need to run in 5 minutes, or ten, or fifteen, or an hour, as opposed to a day, or a week, or a month.