Ask HN: Using Django with PostgreSQL schemas

https://news.ycombinator.com/item?id=662901

by jawngee • 17 years ago

13 6 17 years ago

news.ycombinator.com

I know, I know ... this isn't stack overflow, but that site is too cumbersome to use and I trust the brains on HN ...

I'm trying to wire up Django to a pre-existing Postgres database that makes heavy use of schemas and am not having any luck. Anyone have experience with this? Should I skip Django's ORM and use SQLAlchemy?

Any advice would be appreciated...

olefoo• 17 years ago

You could patch your models to execute "SET search_path 'schema_name';" before emitting generated SQL.

This will work fine until you need to do a cross schema join; at which point you will need to write SQL.

It looks like support is slated for Django 1.1 (maybe) see http://code.djangoproject.com/ticket/6148

That should at least point you in the right direction.

nimbix• 17 years ago

It's possible to set search_path to include multiple schemas, so a cross schema join would be easy as long as tables have different names.

ubernostrum• 17 years ago

Well, it won't be in 1.1; the feature window came and went without significant progress on that ticket.

I have a suspicion, though, that (like many things people commonly claim Django's ORM can't do) it is possible to come at this through lesser-known/lesser-considered features. Off the top of my head I can think of two approaches.

In either case, I suspect the key thing to do is write a subclass of django.db.models.sql.query.Query which properly applies schema information when constructing the SQL (Query.get_from_clause() is probably the place to start).

Given the Query class implementing the proper behavior, the two approaches I'd look at are:

1. If schema information is only important for some models but not others, a manager class with an overridden get_query_set() (which returns a QuerySet initialized with an instance of the schema-aware Query) can be applied to the relevant models.

2. If schema information needs to be applied to all models, a subclass of the database backend can have its attribute uses_custom_query_class set True, and then the backend's query_class() method will be called to obtain the default Query class used for all queries from that backend.

Down the road, maybe somebody who really needs this feature will step up during the 1.2 feature-proposal window and work up the necessary bits to have Django do this natively.

brutimus• 17 years ago

In any situation like this I've been in and tried to attempt django, it's turned out to be a complete failure. You need an ORM that knows how to construct SQL properly and map existing tables. Go with your second option: SQLAlchemy. Use that with SQLSoup (an extension), mapping tables on the fly is a breeze and you can manually tweak your queries to do about anything you need: polymorphism, correlated subqueries, etc. I have a feeling that if you attempt the Django route, you're going to spend more time wondering "well why the hell doesn't this ORM do _____?" That's my opinion on the subject.

jerryji• 17 years ago

Yes.

And to anyone who does not care about a fancy admin interface, I'd serious recommend Pylons.

lacker• 17 years ago

If you have an existing database it's easiest to skip Django's ORM. I don't know about SQLAlchemy but I have heard good things, so I think your plan sounds good.

Ask HN: Using Django with PostgreSQL schemas

Related Stories