SQLAlchemy 0.9.0 Released | Hacker News Reader

SQLAlchemy 0.9.0 Released

sqlalchemy.org

109 pointskbambz39 comments

a really solid, perhaps unique, library that, as far as i can tell, completely addresses the "impedence mismatch" argument. if you're directly using a database api in a structured way, you've probably already reimplemented the sqlalchemy core, and refactoring your program to use sqlalchemy will give you an orm, should you want one, and make your code portable across database implementations even if you don't. if you're already using an orm, refactoring to use sqlalchemy will allow you to to start optimizing the low-level database operations in a coherent way (i.e. not just sprinkling SQL throughout the models). there's a nice writeup in the architecture of open source applications ( http://www.aosabook.org/en/sqlalchemy.html )

now, fantods aside, is there anything like this for java? according to the aosa writeup, sqlalchemy's author found python's multiple inheritance useful for implementing certain ideas, so porting probably isn't straightforward. also, the relevant stackoverflow question ( http://stackoverflow.com/questions/10537766/closest-equivale... ) seems inconclusive.

this is not to criticize java as a language, but rather because i really wish there was something like sqlalchemy for java.

> i really wish there was something like sqlalchemy for java.

Ebean[1], perhaps? I've only used it for a project in Play 2.0 that was built on Java (it's built into Play 2.0), but it was far less painful compared to other Java ORM libaries like Hibernate.

[1] http://www.avaje.org/ebean/getstarted_props.html

I find Jooq to be similiar in some ways.

http://www.jooq.org

http://www.querydsl.com/ as well.

You could also check out Crudzilla (crudzilla.com), it has a middleware that simply allows you to specify your SQL and forget about everything else. Includes a bunch of other goodies that'll make web development a pleasure :)

Full disclosure: Founder.

This might be a "LMGTFY" type question, but having never used SQLAlchemy, what sets is apart from something like Hibernate?

It's very similar to a "Hibernate for Python", and I believe it started out that way. However, it has since surpassed Hibernate in capabilities, and presents elegant APIs that cover a more substantial fraction of "things you'd want to do in SQL".

MyBatis (formerly iBatis) might be a good option. It's been years since I used it but it's a lightweight alternative to Hibernate: http://blog.mybatis.org/

> support for Postgresql JSON types,

This. Finally :) I know I was searching this a few months ago and there was a long due ticket for this to be added. Thank you whoever did this patch.

A side note. I personally use Pyramid (I use Pyramid more, however) and Flask. For Flask project I never use flask-sqlalchemy because mitsuhiko doesn't seem to update the repository as often as he should. A lot of outstanding tickets and pull requests. Some are merged but they are never closed. So I always end up bringing up SQLAlchemy myself into Flask because I just don't trust the library. Is that how people feel too? For Pyramid, SQLAclemy is the "standard" ORM to use and with ini file it is supposed easy to integrate SA into Pyramid app.

Currently on a big project using flask + sqlalchemy and didn't want to use flask-sqlalchemy as well. At first, it was because it didn't seem like i needed to, then it was for a better understanding of the sqlalchemy session / flask request lifecycle coordination. That knowledge proved to be very useful once i needed to reuse my stack in a celery task.

To solve this, I have a custom Celery instance that wraps each task in my flask app's context. So you can treat celery tasks as just another request.

Yeap, that's what i did as well, and i think that's the documented way ( using a flask test request context i think). But having to do this let me understand a lot more about sqlalchemy session, flushing and object states, in a multithreaded environment. I recommend doing that for an in-depth understanding.

In this combination SQLAlchemy feels a bit mismatched because it is heavier than Flask itself, somewhat negating the joy of using a micro framework. On small projects I enjoyed using things like the web2py Data Access Layer, a single module sweetening access to a large number of databases.

https://code.google.com/p/web2py/source/browse/gluon/dal.py

I don't find any mismatch in using a heavy framework like SqlAlchemy with Flask - I choose Flask because I want my web framework to be lightweight, but I use SqlAlchemy because I want the full power of that library. I think it's fine to take a component-level view and choose light- or heavy-weight components as you see fit.

I also don't use flask-sqlalchemy because it's too infrequently maintained, and I also don't like that it deviates from pure SqlAlchemy style in (eg) model decarations - I really don't see the need for that.

sqlalchemy is the best database toolkit for python developers. you can use it as an orm or you can just use its core functionality; write sql expressions in python, define your tables, columns and types. anything you can think of, in terms of dbs, you can do with sqlalchemy.

i'd like to take a moment and thank to @zzeeek [1][2] and all of the contributors.

[1] https://news.ycombinator.com/user?id=zzzeek

[2] https://twitter.com/zzzeek

Thanks for this release. Learning SQL before ever touching an ORM, SQLAlchemy's approach makes a lot more sense to me than something "simplier" like Django's ORM.

Anyone know, is there any chance Django would pull in SQLAlchemy core as a dependency and implement their ORM on top of it?

The ORM is mediocre compared to SQLAlchemy, the template system is weak compared to Jinja2.

If you swap Django's ORM with SQLAlchemy and replace Django Templates with Jinja2, you end up with something like Flask.

The killer feature for me is Django's "admin" app. When I'm not using the "admin", the rest of Django kind of gets in the way. Nowadays I prefer a less opinionated framework like Flask if I'm not using the "admin".

Also, Django lacks any concept of multi-tenancy and does not support composite primary keys nor schemas, which makes it a poor choice for SaaS projects. That said, Django REST Framework is awesome for API-first development and makes it a whole different game (in a positive way).

In my experience the true killer feature of Django is the "batteries included" approach. If you stay within the boundaries of the framework you get a standard project structure, a decent ORM, a template language (dogmatically crippled, but that's a different discussion), a forms handling library, and the typical assorted framework toolkit (internationalization, logging, development server, utilities, etc) -- all equally documented and maintained. Compare this with having to choose and figure out individual libraries for every one of the things above, each coming with different levels and styles of documentation, communities, and resources.

The end result is that you spend less time deciding on small things and can just use what's in the box. The real value is that the next person working on the code is already on the same page and can start working with it a lot quicker.

Nah, I'm not sure having all the libs bound together is the killer. I think it's having all those libs with a cohesive set of docks and with a "single" voice. I'm not sure that packaging Flask, Jinja2/Babel, WTForms, SQLAlchemy, and Werkzeug (dev / debug server) together -- that should cover the non-admin Django -- would improve the usage. Technically, Jinja2 and Werkzeug are already dependencies of Flask, and their extensions page [0] are fairly instructive on what other libs to pull in for the other needs. There's also no reason one can't use Django, Jinja2/Babel, WTF, SQLA, and Werkzeug... we do!

[0] http://flask.pocoo.org/extensions/

I don't think Django should ditch their ORM, but for the time being simply re-implement it on top of SQL Alchemy core.

I think it would have at least three positive outcomes:

* It would greatly increase support for the number of databases and database drivers.

* It would give developers something in between using the ORM or forgoing it and using SQL strings in raw() or db.connection().

* It would provide an easier path forward to adding additional capabilities into the Django ORM.

Django 1.7 will support composite keys (it's been worked on for several years.)

https://code.djangoproject.com/wiki/Version1.7Roadmap

I don't see this ever happening because Django has no dependencies on any other Python libraries. It wouldn't make sense to start requiring SQLAlchemy only to reimplement the existing ORM on top of it for little to no benefit.

That's not true. Django doesn't install any other libraries. It simply includes them in Django itself, like simplejson or six.

I think the packaging and setup tools are getting better so maybe it doesn't always have to be that way.

I see what you mean, but arguably those aren't really dependencies if they are included in the Django tree. Also, it's definitely not something that would work with a large library like SQLAlchemy.

You can also see the "no dependencies" claim on the Django website: https://www.djangoproject.com/download/, it's a selling point.

that idea has come up on many occasions and was seriously discussed some years ago. I don't think it's ever happening in that specific form; mostly because it would require that the Django ORM's behavior and usage contract not change at all in order to support backwards compatibility. This would be a very large task, at the very least for Django and probably for us as we make adjustments to support them. The feel and behavior of Django's ORM would invariably change quite palpably - even if you can get every function input and output to match, you still have what it looks like when you get a stack trace, an error message, etc. Django users would see lots of unfamiliar "sqlalchemy" lines in these stack traces and tracking down issues would then involve both Django and SQLAlchemy devs. Performance would also be a concern - keeping behavior exactly as is within Django might require some odd workarounds or additional logic which could prove to be complex and/or less performant.

That said, I have on occasion floated an alternative idea, which is to basically port the Django configurational and query system to SQLAlchemy entirely, doing away with full backwards compatibility and essentially providing a "transitional" platform, where you take an existing Django application and basically run it on top of a new ORM layer that actually uses the SQLAlchemy Session and unit of work model, but the majority of the Django configuration and queryset behavior remains available, thus making porting a relatively painless process (but still, there'd be a porting process). This approach has two advantages. One is, it's actually doable, we're providing a new platform and not trying to duplicate an existing one. The other is, we're actually delivering to a Django application some of the biggest advantages SQLAlchemy has, which are namely the ORM features like unit of work/identity map, the instrumentation model including collections and eager loading support, and inheritance support.

Nobody has time / motivation for that idea either, but I think if it ever happened, it would be more pragmatic.

I dunno. I just don't get ORMs. I certainly see their use case in easy prototyping, but with the advent of NoSQL, why not just prototype with MongoDB, or similar, and if it turns out your data is close to uniform in structure, just switch over to MySQL, or similar.

To keep my code portable, I stick to standard SQL and don't use any stored procedures. I'm sure this is probably not a tenable strategy for web-scale apps, but with 100s/1000s of users and only millions of records, it seems to work just fine.

This comment is no way meant to disparage SQLAlchemy which by all accounts seems to be a first rate work of engineering. I am speaking of ORMs in general, and why should we use them.

Here are 3 primary advantages I can think of:

* Ability to programmatically add to/chain queries. It's really simple to add an additional "WHERE" clause in an ORM query; it's much harder to do so with a SQL string, where you have to account for escaping, comma placement, etc.

* Ability to automatically join entities in some cases, which can save on verbosity.

* Ability to add methods and attributes to models, which can abstract away additional queries or queries that might normally be really complex.

It's debatable if ORMs really add that much to productivity. I use them in some projects, and forgo them in others. For Python, I'm a big fan of Pony ORM because it can greatly reduce verbosity and basically maps Python code directly to SQL; I don't really use it for the relational "mapping" though: http://ponyorm.com/

Why is SQLAlchemy still under 1.0? Are there good reasons, or is it just the apparently typical (and typically irrational) fear of the version number "1.0"?

I suspect it's because there are backwards-incompatible changes between 0.x versions, whereas 1.x should (arguably) be backwards-compatible.

If I had to venture to guess, the project most likely started with 0.x notation because the first few releases didn't have major planned features such as the declarative ORM. After that there were never any truly backwards incompatible versions to merit a full number increment.

Really, it's a credit to just how much Mike got right on the first go.

originally i was going to skip from 0.8 just to 1.0, but then a lot more new features and behaviors came into 0.9, so i figured lets just do the one more non-1.0 number. There's not going to be an "0.10", it'll be 1.0.

I'd love to have something as good as sqlalchemy in go

That would probably make me switch from python to go. Not just because of the lib itself, but because it would help me gain confidence in the modeling and abstraction power of Go.

I looked at Go a little bit as maybe a place to write a kind of "uber-Core" system, e.g. a high performance database abstraction layer that could power libs in other languages. But apparently Go is not at all designed to be embedded in other runtimes, it isn't even possible. Also it seems to lack a standard database API and the connectors that were there seemed a little all over the place.

Did you check out rust? That's within rust's feature set, though it isn't "production ready" yet.

how does SQLAlchemy support for PostGIS compare to GeoDjango ORM?

Looks like it's pretty robust: http://www.geoalchemy.org/tutorial.html

It is, though if you're using PostGIS, I'd recommend GeoAlchemy2[1], which ties into SQLAlchemy a bit more naturally (because they get rid of support for all other engines). Also, seems to be more actively developed/maintained.

[1] https://geoalchemy-2.readthedocs.org/en/latest/

Reply on news.ycombinator.com