Best practice for cross-package foreign keys

[Petter Urkedal]

I have a package which implements users, groups, and access control
using
SQLAlchemy. On top of that I'm writing applications as separate
packages.
These will make foreign keys into the tables of the user management
package.
At the moment I use separate metadata for each package, and the
foreign keys
are just integers. This is unfortunate due to the lack of foreign key
constraints to ensure consistency of the data, and also inconvenient
when
using ORM.

I'd like to add the foreign key constraints. As far as I can tell,
requires
sharing metadata across packages. This means that create_all and
drop_all
no longer acts locally on the package, but their use can be avoided.
Are
there other consequences? Are there ways to allow cross-package
foreign
keys while keeping metadata separate? What is the common practice and
recommendations for inter-package table dependencies?

[Michael Bayer]

pretty much one of only two usages for MetaData() is to provide a
context for Tables to locate other tables that are referenced within
their ForeignKey objects. Your different packages are by definition
interdependent due to these ForeignKey objects, so each should import
a single MetaData object which lives in a common, shared module. The
create_all() and drop_all() methods accept a list of tables which will
limit those objects which are created/dropped, however this can be
problematic if creating tables for another un-created package which
satisfies foreign key dependencies, or the reverse scenario when
dropping tables. So in that sense I don't exactly see the use case
for creating/dropping the tables in just one package and not the
other, but the tables argument to create_all()/drop_all() supports it.

[Petter Urkedal]

Thanks, that's what I needed to know. The use for limiting the scope
of drop_all is to avoid removing data from the base package when
uninstalling one of the dependent packages. In the case of
create_all, it's unlimited scope is probably ok, since it will only
create the missing tables.

PS: Sorry about the line wrapping in my original post.