Don't just learn SQL. An ORM like SQLAlchemy will allow you to save time and write cleaner, more comprehensible code.
But do learn SQL. Do not use an ORM before you have learnt SQL.
What ORMs have taught me: just learn SQL (2014)
481–490 of 654 posts
Re: What ORMs have taught me: just learn SQL (2014)
#482Earlier quoted context omitted.
Yes, yes, yes. Why is this the case? 1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database. 2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy servi…
The simple solution to 1 is to never allow direct database access. Api only.
So you do all your analytics by running a series of service calls and then writing a script to collate them into the needed results? Seriously?
Re: What ORMs have taught me: just learn SQL (2014)
#483Earlier quoted context omitted.
Yes, yes, yes. Why is this the case? 1. If you are doing anything interesting, people are going to ask questions about what you are doing, and the best way to answer those questions is going to be by querying your database. 2. One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore: the legacy servi…
> One day you might want to rewrite some of your service/s, split them into microservice/s, etc. At that point, there will be a minimum of two services talking to your datastore. You should not do this. It removes almost all of the benefits of extracting things into a separate service (services should own their data and the only means of accessing it should be via their APIs). That's not utopian; that's one of the ma…
Right, so let’s suppose you already segmented the data to two different backing datastores, and your monolith is now connecting to both of them instead of just the one. Now you can do the service migration, at which point you still run into the situation I’m discussing.
Re: What ORMs have taught me: just learn SQL (2014)
#484Earlier quoted context omitted.
An in house solution is almost always better than an external dependency
Haha, so enterprise-chat. I rewrote our entire database layer in Hibernate for our (incredibly complex monolith) webapp. Then I was tasked with rewriting a major core piece of search functionality that builds a query from user selections / saved queries. I was told that contrary to previous work, Hibernate Criteria would not be allowed, since it was deprecated. Hibernate's official replacement for programmatic querie…
>JPA Criteria
Feature parity aside, I also found this to perhaps be the most verbose API for query building I've ever used.
Re: What ORMs have taught me: just learn SQL (2014)
#485Earlier quoted context omitted.
I live in a world where tables have 10^12 rows and all the queries need to be manually optimized. In my world, an ORM is the most useless thing in the world. Different people, different needs. However, in all my projects SQL was more useful than ORM, except where the data models were so simple my mom could write the code for it.
If you have one table that you’re doing a query on, what types of optimizations are you doing to the query as opposed to the database - or even if you’re doing joins?
In general for one table an index helps, but we have almost no queries on a single table. The Query Profiler is telling what part of the query takes the most (CPU, disk reads) and that is the starting point. Tuning a single query can take hours, depending how slow it is and how often it will run.
There is some information on Internet on query optimizations, look it up. There are also server side optimizations, specific to the RDBMS you use and the flexibility it has.
Re: What ORMs have taught me: just learn SQL (2014)
#486This is a great article, I've really enjoyed reading it! I want to react to a few of the points made in the post, and point out some potential techniques for solving them!
Most ORMs are heavily inspired by object-oriented programming which fosters the object-relational impedance mismatch. In addition to logic for storing and retrieving data, models often also implement business logic (ActiveRecord is a good example here). This makes for bloated and complex objects that are difficult to work with in the application. A solution can be to lower the level of abstraction and use a more lightweight query builder (like knex.js for Node.js). These kind of tools give you more control in constructing your queries as well as the ability to optimize them.
These tools still require you to understand quite a bit of SQL though and the productivity leap compared to writing manual SQL isn't as high. I believe that query builders are the best compromise we have today for accessing a database from an application.
Regarding the dual schema dangers that are mentioned by the author, I strongly believe that these can be alleviated using code generation tooling that helps to keep your database in sync with your application models (approaches like SQLBoiler in Go where application code is generated based on the database schema are an example here).
Re: What ORMs have taught me: just learn SQL (2014)
#487This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly. I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One th…
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you con…
Fully agree! Some mapping code is _required_ in your application, otherwise it wouldn't be able to talk to your database at all.
However, I've never understood why people write this mapping code manually. I believe in code generation tooling as a potential solution for this (where types and maybe a full data access API is auto-generated based on the database schema).
Re: What ORMs have taught me: just learn SQL (2014)
#488Earlier quoted context omitted.
There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you con…
QueryDSL ( http://www.querydsl.com/ ) does something like this for Java. It can generate classes from tables, but even with those, all queries / statements that hit the database are manually built using a query builder to avoid syntax and type errors. I.e. no caching or automatic database updates.
knex.js is another example of such a query builder library in the Node.js ecosystem
Re: What ORMs have taught me: just learn SQL (2014)
#489Re: What ORMs have taught me: just learn SQL (2014)
#490Earlier quoted context omitted.
It was a Flask app using SqlAlchemy (so Python). I'm not sure functional programming would have changed the situation much. I imagine there would still be repeated patterns involving reading and writing to the database in slightly different ways, and it would still make sense to use some sort of library. But I haven't used functional languages much, so I can't say for sure either way.
Well, the difference is that in a data oriented language, you do not need to map Objects to relations. You can get the data back in the relational format and use it as is in your app. So you don't need an ORM. You might still have a library to help you build dynamic SQL queries, but no object-relational mapping needed.
> Well, the difference is that in a data oriented language, you do not need to map Objects to relations. You can get the data back in the relational format and use it as is in your app.
You can do that in an OO language, too; relations (whether constant or variable, and whether there data is held locally by the program or remotely, as in an RDBMS) are perfectly valid objects.