Kuze <kuze.to...@gmail.com> writes:
> I'm also aware of `reflection` capability provided. However, it'd be
> hitting the database with a query to grab the necessary data points
> for generating the schema. For production, hitting the db when using
> reflection does not sound compelling.
>
> With my assumption that reflection isn't for production. What's the
> bare minimum required for data access?
This doesn't directly address your subsequent questions on minimum
definitions, but I also chose to manage my schema purely on the
database (PostgreSQL) side, and use reflection to initialize my
SQLAlchemy layer. You may not want to give up on the reflection
approach too quickly, even for production. I originally thought along
similar lines to the above, but have yet to feel the need to switch
away from reflection after almost 2 years of run time.
In my case, for example, complete restarts of my production
application server are infrequent, so the actual reflection only
occurs every so often. And as it is, it just adds a few seconds to
the start up time. I restart my development server nightly, but the
reflection overhead is a minor part of that process.
Now, this is currently a schema with only about 60-70 tables, so a
larger database is probably more expensive to reflect but I'm not
anticipating any issues as I grow given how well it performs now. In
the end, reflection is just a bunch of queries to system tables (I
think about 500 individual queries in my case), so really just a blip
in the overall query stream from the application.
The reflection even occurs in command line support utilities I use,
which I was originally sure I'd want to switch away from reflection,
but to be honest, it just hasn't been an issue to date. Running the
utilities over a WAN connection to the database does take a little
longer to initialize, but not enough to impede usability (or push me
to change anything). If it gets bad, I'd probably first implement a
way for the utilities to indicate the subset of tables they're
interested in, than do away with reflection.
My SA mapping module isn't completely maintenance-free, as I do need
to update it to match significant schema changes (such as new tables
or foreign key relationships), but it's pretty minor:
* Empty class definition for each mapped table. I chose to do this
to control the tables mapped, and also to more easily manage the
mapping of multiple source schemas into a single set of mapped
classes. I also throw in any helper properties into these classes
that I may want at application level.
* Manual foreign key declarations when using SA features like backrefs
or to control lazy loading.
* Manual association_proxy mappings for junction tables when desired.
There is one tricky situation I've run into, and that's when SA (at
least the version I'm using) can't automatically identify the primary
relationship between tables (such as when a table has two foreign keys
to the same remote table, or when you have tables with foreign keys
referencing each other). For those tables, the reflection has to
happen first, so I have a final "cleanup" stage run after reflection
that installs such relationships by explicitly referencing the loaded
columns.
But for me, I still prefer the general model where the database design
(and DDL to create/update it) is mastered outside of SA, so don't think
I'll be moving to declarations in SA any time soon.
I have also tried some tools that use reflection to dump a more static
definition that could then be loaded by SQLAlchemy, but haven't found
them an ideal match yet compared to what I'm using, though that's
another possibility (even if a locally developed approach) I'm keeping
in the back of my mind if the reflection gets too heavyweight.
-- David
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
