Give PonyORM a chance
jakeaustwick.me
jakeaustwick.me
I understand that the ORM is trying to smooth over differences in implementation, providing the possibility of change from one DB access layer to another.
I have yet to see anyone do that on a real project, which makes me really wonder at the point of using an ORM at all.
I cannot quantify the amount of time I've lost figuring what the hell I must do to generate a relatively simple SQL statement.
Now write this in SQLAlchemy without strings, without concatenation, and let's see which provides you with more clarity.
Second, how is it a black box? It is the particular query provider implementation that is a black box.
Third, there's absolutely no correlation between the amount of LINQ and success of a project.
Yes, I am aware. How is this relevant at all to the context of this thread?
The rest of your comment: You are simply wrong. A heavy use of LINQ is one of the surest sign that one needs to run from a project. It is almost always used and embraced by people who have no concept of the consequences, imagining that the conciseness of LINQ = programming goodness, when the opposite is generally true.
No strings, and I get an actual expression object instance that I can, for example, store in a variable and reuse in any number of queries.
If we insist on it, SQL itself was supposed to smooth over differences in db implementation. Ehm.
If all you need in one query, then figuring out how to make ORM running may be waste of time. If you need gazimilion of them and you need results cached reasonably and so on and so forth, ORM starts being very useful.
Data never goes out of fashion, it's easy to use, it's usually text, and it's usually returned in convenient lists.
If you want to select a gazmillion rows, you're probably doing something wrong if you're doing it with an ORM.
The overhead of storing all that metadata and the object structure overhead for each row would make it a poor choice for massive volumes.
Caching results from a database seems a bit pointless to me, since databases do that already anyway.
Rather figure out how to optimize your SQL so that you don't have to build a cache. The database is almost never the bottleneck with well-written SQL.
An ORM will give you a description of the database you can use by reflection, and an abstraction layer that avoids using hard to use lists and tables. The first one is way more usefull than the later, but both are good things.
Also, application servers are normaly much more numerous than database servers, and much easier to scale up. Thus, anything that offloads work from the database to the application will simplify your environment when you grow.
I also wrote a little utility to handle migration. Each migration is a file with SQL in it; forward migrations only, no going back. The tool makes it easy to apply a given migration to a given database (dev, staging, production or whatever) and does the book-keeping to ensure they're only applied once. Couldn't be simpler.
I'm really happy with this set-up. It gives access to all the features of the database instead of just the lowest common denominator, and makes it much easier to read the code and know exactly what's going on.
The only times I've seen an ORM work well in a non-CRUD application is when it was designed and written from scratch to work with one database to supply exactly the operations and semantics that the application needs.
Sometimes a schema can even be correct and the ORM will have trouble with it, Hibernate has (or had, I don't follow nowadays) a problem with n-n relationship tables that have aditional information on them, which is a perfectly fine normalization thing to do.
In fact, I would go so far as to say that if my tool dictates my data modeling, than there's something wrong with my framework.
This is the thing that many people seem to forget. Every ORM I've used has allowed you to write your own SQL for those times where the DBAL is limited or not performing well. In my experiences, those times are rare. Some ORMs (like Doctrine) even have their own DSL so that you can write complex queries and still have the benefit of a DBAL.
I'm currently working with a team on a large project where all the SQL is written by hand, and results are hydrated into plain ol' arrays. The maintenance overhead for schema changes is huge due to the number of queries which need to be updated, and there are constant bugs caused by columns being left out of SELECT statements. On top of that, the testability of the entire app suffers because most methods talk directly to the DB. It's truly a nightmare. I've never experienced these problems with an ORM.
Anything that isn't CRUD or a simple aggregation. Real world applications tend to have: oddball joins, subqueries, window functions, case statements and all other sorts of crazy once you get into the value-add parts of the system. What I really need is a clever way to project the value or set into a structure that I can easily process or convert into UI elements.
On the subject of CRUD, I would be really impressed with an ORM that would detect my constraints and enforce them in the application layer. I am a big fan of DRY, but I am a bigger fan of bulletproof relational models that prevent bad data from ever getting to the system. So rather than declaring these sorts of things in the app code, where they won't be enforced in things like stored procs or ad hoc SQL, I'd much rather have the database be the one source of the data constraints. For really complicated things (e.g. validation triggers), I'd want a way to communicate a violation back up to the ORM layer.
Not sure if this is what you wanted, but for some reason I felt compelled to brain dump what I have been thinking on the subject as of late.
In the latter case it's also unclear when your queries run in a transaction, which isolation level the transaction is, heck, in many ORM you barely know when the server gets called at all (it's usually not when you expect it's called), nor you have any guarantees about the order in which your updates are fed to the server.
You also have no control to supply direct SQL when you need to as you have no direct access to the connection, or the ORM relies on caching where direct access would cause inconsistent data.
The difference between an ORM, and a simple query generator under your control seems like a subtle detail, but as the wise ones said, the devil's always in the details.
You also can supply direct SQL to the DB via Pony, the documentation covers this: http://doc.ponyorm.com/database.html#using-parameters-in-sql...
ORMs normaly default to either "in transaction" or "not in transaction", and require explicit code to change that. They guarantee the order of commands that change data, and let you get into the database any time you want.
The only characteristic that fits my experience is that you don't know when SELECTs are executed. And, if this is a problem for you, you are doing something very strange.
Yes, an ORM saves you the time of writing out the code to map the fields on the row to the fields on the object. But it doesn't do that for free. Let's say the results of your query are, shortly thereafter, looped over and one property is checked. Well, in NHibernate, that property is lazy-loaded by default, so now that you're touching it, it generates an individual select query for every element in the list. That's simple enough to fix: change that property to not be lazily loaded. Now suddenly a completely different part of the system is getting an OutOfMemoryException because you're querying over an unfiltered dataset and now it's loading a huge table into memory.
Your trivial query in SQL is not a trivial query in an ORM. Let's keep trivial problems trivial.
There are tools that do this better, by essentially providing a domain-specific language that generates SQL queries (LINQ-to-SQL, parts of sqlalchemy, and, it looks like, PonyORM). These certainly result in more aesthetically pleasing code, but you're still essentially writing SQL, except that you don't have access to the entire SQL implementation. You want to remove constraints, apply an update, and then add the constraints back? Good luck doing that with anything other than SQL. This probably won't hurt you for trivial queries, but having the ORM in your code means that it's only a matter of time before someone on your team starts doing something non-trivial with it, and then the beast will raise its ugly head. And besides, while you can be careful with these DSL-style ORMs and they won't hurt you, they won't help you that much either. Typing a little more isn't the bottleneck.
I don't want magic on my database layer, because when it backfires, and it will, it might ruin the most precious thing that my application relies on.
I absolutely do not want a lack of understanding on my part to cause permanent damage to my data (already got burned by that). And since ORMs are usually complex beasts, I certainly don't want the burden of learning every single use case before even deciding how to model the thing.
The level of complexity does not balance the value added. It does not hide SQL, because you WILL have to learn both the framework and SQL to debug, and debug you will, often. It does not, in my opinion, increase productivity since I will lose many an hour figuring things out when things do not work as expected, and they won't. Simple bugs and performance issues become a great burden.
Simply put, I don't won't to forfeit control of the most sensitive part of the system, the data. Everything that goes on with data persistence should be really clear and simple, no magic.
Also I find it's implementation to be very questionable.
What really annoys me is
1. when people stamp a AGPL license on something (that was previously licensed under GPL or even less restrictive) and claim that it is still just GPL and everyone could still continue using their system as before.
2. when people make something brilliant and only offer it under AGPL without providing a commercial licensing option.
The fact that there exists good free software out there doesn't mean that one should adjust his price expectations based on this. Take Linux, it's free, but this doesn't mean it's development was not fueled by up to $100M worth of contributions, so if Linux it's free, this doesn't mean that a team developing a new OS by themselves should not charge a price big enough to quickly amass a tens-to-hundreds-of-millions-$ class profit to recoup their investments.
You give something fully for free IF and AFTER you've recovered the development cost, or after you go bankrupt or pivot to another product and you no longer have any use for it. If you do it before, you at least make damn sure nobody else can make a profit from/based-on your not-yet-paid work without giving you at least some of it, because this is how it's fair to be, this is what the (A)GPL is for!
I would never use it, though, because decompiling Python generator expressions just feels so unpythonic.
Providing the things happening underneath don't break (and they haven't on me yet), and it generates efficient SQL (which is does), then I'll take the hidden complexity for the advantage of the higher level generator syntax.
Agreed but it really left me thinking that it'd be extraordinarily useful if Python gained some supported path for doing this – a standard way to get the AST for a generator expression would be really useful and a relatively general tool. A few years back I would have loved a cleaner way for a JSON decoder to know I only needed a couple of fields to avoid creating objects for everything else and it seems like there could be some very interesting optimizations for something like numpy/scipy as well.
1. I briefly liked LINQ awhile back before Microsoft's usual baroque design and poor QA convinced me that ASP.Net wasn't actually a time-savings. The LINQ-REST support seemed nice, albeit complicated, until you hit bugs where a change in one view would break unrelated views and running in the debugger "fixed" it.
The dev mode approach of 'just do stuff and I'll modify the tables' has scared off many people I've showed it to, but I love the approach - sort of a moderate 'nosql' approach without giving up structured tables for more complex queries later.
It's basically the opposite of "pythonic". I can't imagine something like redbean written in a language like Python, and maybe it's for the best :)
Uhm, anyone knows what are the performance costs of this?
Very performant: (1) Bytecode decompiling is very fast. (2) Since each query has corresponding code object, this code object can be used as a cache key. Because of this, Pony ORM translates each query only once, whereas Django and SQLAlchemy have to translate the same query again and again. (3) As Pony ORM uses IdentityMap pattern, it caches query results within the same transaction. There is a post (in russian) where author states that Pony ORM turned out to be 1.5-3 times faster than Django and SQLAlchemy even without query result caching: http://www.google.com/translate?hl=en&ie=UTF8&sl=auto&tl=en&...
>>> from pony.orm.examples.estore import *
>>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:]
SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
FROM "Customer" "c"
WHERE 'iPad' IN (
SELECT "product-1"."name"
FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
WHERE "c"."id" = "order-1"."customer"
AND "order-1"."id" = "orderitem-1"."order"
AND "orderitem-1"."product" = "product-1"."id"
)
>>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:]
SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
WHERE "product-1"."name" = 'iPad'
AND "c"."id" = "order-1"."customer"
AND "order-1"."id" = "orderitem-1"."order"
AND "orderitem-1"."product" = "product-1"."id"Why the name PonyORM btw? I know it's superficial but I much prefer the name SQLAlchemy - has more meaning.
The idea of Pony ORM is to provide a Pythonic way to work with the database. We think that the generator syntax is very concise and convenient.
It is named Pony because a pony is a small, smart and powerful creature - these are the features which our mapper has. Our goal is to provide non-leaky abstraction and good user experience.
Now the hand-written SQL people leave in SQL injection possibilities. They build up complex queries with crazy string concatenation. They either have no or a shitty data mapping layer (I mean, I really enjoy having to look at the database to figure out what fields select * from articles returns).
Obviously there are going to be queries outside of what any normal ORM can do, but every ORM I have used gives you an escape hatch to just write raw SQL when needed.
at the end of the day the AST idea looks very nifty but IMO is too rigid to translate to SQL in a flexible enough way, and also works against the main reason you use code structures to produce SQL which is composability. When I last saw the Pony creators do a talk, the approach seemed that each time you have a given SELECT, and you'd like to add some extra criterion to it, it pretty much will keep producing subqueries of the original, because each time you can only wrap the AST construct you already have. It similarly had no ability to produce a JOIN - at that time at least, the only way to join things together was by rendering inefficient correlated subqueries. This was asked explicitly.
If they've found a way to resolve these issues while keeping true to the "AST all the way" approach and not dropping into a SQLAlchemy-style approach, that would be great. There's no reason SQLA ORM or Core couldn't be behind a similar approach as well except that nobody's really had the interest in producing it.
Pony had the ability to produce JOINs from the very beginning, but during that presentation we found that Pony produced subqueries for MySQL and that was not very performant, correct. Since than we've improved Pony and now it got a query optimizer which replaces subqueries with efficient JOINs where it is possible. Here is the query from that presentation:
>>> select(c for c in Customer if sum(c.orders.total_price) > 1000)[:]
SELECT `c`.`id`
FROM `customer` `c`
LEFT JOIN `order` `order-1`
ON `c`.`id` = `order-1`.`customer`
GROUP BY `c`.`id`
HAVING coalesce(SUM(`order-1`.`total_price`), 0) > 1000
myquery = select(c for c in Customer if sum(c.orders.total_price) > 1000)
mynewquery = select(c for c in myquery if c.name > 'G')
Given "myquery", how do I add, after the fact, a simple "WHERE customer.name > 'G"" to the SELECT? Just continuously wrapping in subqueries is obviously not feasible.
mynewquery = myquery.filter(lambda c: c.name > 'G')
SELECT `c`.`id`
FROM `customer` `c`
LEFT JOIN `order` `order-1`
ON `c`.`id` = `order-1`.`customer`
WHERE `c`.`name` > 'G'
GROUP BY `c`.`id`
HAVING coalesce(SUM(`order-1`.`total_price`), 0) > 1000As far as the "AST allows caching" advantage, over at https://bitbucket.org/zzzeek/sqlalchemy/issue/3054/new-idea-... we're working out a way to give people access to the "lambda: <X>" -> cached SQL in a similar way, if they want it.
The traditional problem that ORMs are supposed to solve is change. Changing your database schema means you have to change all your queries. So instead you have a a system whereby the program that executes your queries also understands your schema and can make that change for you.
Data migrations are similar to this, only much harder and more time consuming.
Is that long enough experience?
Then, out of the 6 months you save by not-doing-that, you spend about 6 days dealing with bad performance, ORM bugs and limitations, and like matters. (And the bad-performance areas are the ideal place to put in your choice of raw SQL.)
But your project may have different needs. Depends on how many tables you're managing and how many different ways you need them and how easy you need the "get me a test object for my integration test" to be.