5 ms·

"We are now using SqlAlchemy via schema reflection; no declarative layer." (emphasis mine) What next? Will web development come full circle to using raw SQL qu

"We are now using SqlAlchemy via schema reflection; no declarative layer." (emphasis mine) What next? Will web development come full circle to using raw SQL queries like Phil Greenspun suggested years ago? (Not that there is anything wrong with it)

snprbob86 17y ago
My next post will be about ORMs and the declartive paradigm in particular. It may prove to be more controversial than this post :-)
wehriam 17y ago
I wish. I find raw SQL much easier to use than any of the abstraction layers.
- ams6110 17y ago
  Nice to see at least one other person thinks so.
jp_sc 17y ago
Web.py does something like that. Not raw SQL but almost: result = db.select('table', what='id, name', where='id>3', order='id', limit=5) Is better than cursors, but for medium to large web applications is too much work. I used to be against ORMs ("Another layer? How inefficient"), but now I see them like compilers: They're easier to work with and, most of the time, SQLAlchemy does the job better than me (lazy-loading, etc.).
gecko 17y ago
I've worked with a number of different ORMs, including two on the project I'm on right now, one custom one, and a handful of open-source ones on Ruby and Python and the like, and every single time, the story's the same. At the start of the project, when queries are few and datasets are small, all of the above worked great. But the further you get into the project, and the more complex things you are trying to do with larger and larger datasets, the ORM ends up blowing up in your face. For example, LINQ to SQL--which I want to emphasize I'm picking on only because I'm using it righ tnow--happens to silently and transparently convert from IQueryable (meaning that your LINQ will execute in the DB) to IEnumerable (which means you'll suck in all data required for filtering past that point), which means that you never have a good idea whether your query is going to be optimized or not unless you check the SQL being generated. When you have to do this a few dozen times, you start wishing you'd just written SQL in the first case. The situation gets worse when you start getting into join-heavy queries. I've never seen an ORM that actually can cover everything you need to make an app performant. It doesn't handle full joins, or compound indices, or distinguishing between clustered and nonclustered indices, or calculated columns, or some other thing that, if you were in raw SQL, would easily solve your problem, so at that point, you start introducing raw SQL. The older your app gets, the more instances of this you get, until you'd have been better off simply using SQL the entire time anyway. I love the idea of an ORM in theory, but I'm pretty solidly convinced these days that if you want objects, you should us an OODBMS, and otherwise, you should just embrace the relational model for what it is, and not pretend you're working with objects. For most people, that'll mean writing SQL and getting back query sets.
- snprbob86 17y ago
  I haven't used Linq to SQL except for to play with it for a few minutes. It seemed like a very capable ORM, but I sensed that the IQueriable to IEnumerable conversion may cause some headachs. Linq to Objects, however, is freaking amazing. C#'s functional and querying features are like Python's generator expressions on crack, speed, steroids, and acid. Absolutely love them.
  - gecko 17y ago
    LINQ to Objects is C#'s (very good) take on Python's list comprehensions, and is awesome. However, that's just providing a declarative, functional way to query/iterate objects--not an ORM. LINQ to XML is likewise also okay, since XML's tree-based model happens to map extremely well to objects in a way that a relational model cannot do. LINQ is great. LINQ to SQL is actually very good, so far as that goes; it just happens to be trying to do something that I'm not convinced can be done well.
moe 17y ago
My bet would be schema-free databases, such as MongoDB. Schema synchronization is just a major headache, no matter from which end you approach it (declarative -> evolution or Raw DDL -> reflection). Once your number of databases exceeds 1 you are in trouble either way. The implicit assumption that a schema definition would ideally be set in stone once and then never changed is a legacy carried over from the very early RDBMS days. And it's one of the biggest albatrosses to date.
deleted 17y ago
[deleted]

snprbob86 17y ago

My next post will be about ORMs and the declartive paradigm in particular. It may prove to be more controversial than this post :-)

wehriam 17y ago

I wish. I find raw SQL much easier to use than any of the abstraction layers.

ams6110 17y ago
Nice to see at least one other person thinks so.

jp_sc 17y ago

Web.py does something like that. Not raw SQL but almost: result = db.select('table', what='id, name', where='id>3', order='id', limit=5) Is better than cursors, but for medium to large web applications is too much work. I used to be against ORMs ("Another layer? How inefficient"), but now I see them like compilers: They're easier to work with and, most of the time, SQLAlchemy does the job better than me (lazy-loading, etc.).

gecko 17y ago

I've worked with a number of different ORMs, including two on the project I'm on right now, one custom one, and a handful of open-source ones on Ruby and Python and the like, and every single time, the story's the same. At the start of the project, when queries are few and datasets are small, all of the above worked great. But the further you get into the project, and the more complex things you are trying to do with larger and larger datasets, the ORM ends up blowing up in your face. For example, LINQ to SQL--which I want to emphasize I'm picking on only because I'm using it righ tnow--happens to silently and transparently convert from IQueryable (meaning that your LINQ will execute in the DB) to IEnumerable (which means you'll suck in all data required for filtering past that point), which means that you never have a good idea whether your query is going to be optimized or not unless you check the SQL being generated. When you have to do this a few dozen times, you start wishing you'd just written SQL in the first case. The situation gets worse when you start getting into join-heavy queries. I've never seen an ORM that actually can cover everything you need to make an app performant. It doesn't handle full joins, or compound indices, or distinguishing between clustered and nonclustered indices, or calculated columns, or some other thing that, if you were in raw SQL, would easily solve your problem, so at that point, you start introducing raw SQL. The older your app gets, the more instances of this you get, until you'd have been better off simply using SQL the entire time anyway. I love the idea of an ORM in theory, but I'm pretty solidly convinced these days that if you want objects, you should us an OODBMS, and otherwise, you should just embrace the relational model for what it is, and not pretend you're working with objects. For most people, that'll mean writing SQL and getting back query sets.

snprbob86 17y ago
I haven't used Linq to SQL except for to play with it for a few minutes. It seemed like a very capable ORM, but I sensed that the IQueriable to IEnumerable conversion may cause some headachs. Linq to Objects, however, is freaking amazing. C#'s functional and querying features are like Python's generator expressions on crack, speed, steroids, and acid. Absolutely love them.
- gecko 17y ago
  LINQ to Objects is C#'s (very good) take on Python's list comprehensions, and is awesome. However, that's just providing a declarative, functional way to query/iterate objects--not an ORM. LINQ to XML is likewise also okay, since XML's tree-based model happens to map extremely well to objects in a way that a relational model cannot do. LINQ is great. LINQ to SQL is actually very good, so far as that goes; it just happens to be trying to do something that I'm not convinced can be done well.

moe 17y ago

My bet would be schema-free databases, such as MongoDB. Schema synchronization is just a major headache, no matter from which end you approach it (declarative -> evolution or Raw DDL -> reflection). Once your number of databases exceeds 1 you are in trouble either way. The implicit assumption that a schema definition would ideally be set in stone once and then never changed is a legacy carried over from the very early RDBMS days. And it's one of the biggest albatrosses to date.

deleted 17y ago

[deleted]