Support for IF EXISTS/IF NOT EXISTS DDL constructs

[Rémy Roy]

IF EXISTS and IF NOT EXISTS are already part of many DBMSs DDL vocabulary. They have many use cases which are highly useful in some situations.

For instance, creating a table if it does not already exists or dropping a column if it exists.

It would be nice to have those directives with standard SQLAlchemy DDL methods. I guess they could be implemented using the native support for DBMSs that support them or with some introspection for those that do not.

[Rémy Roy]

This would be quite useful for migration tools like Alembic for instance. see https://bitbucket.org/zzzeek/alembic/issue/151/if-exists-if-not-exists-directives

[Mike Bayer]

this is always something people can help along via pull request, we've been doing well with those lately

[Rémy Roy]

Would it make sense to merge the currently implemented checkfirst logic with the IF EXISTS/IF NOT EXISTS implementation? What I mean is that for dialects who supports IF EXISTS/IF NOT EXISTS for a specific object type, no introspection would be done whenever checkfirst=True, only the CREATE or DROP statement would be issued with the proper IF EXISTS/IF NOT EXISTS vocabulary. That would save some roundtrip to the database for those who support IF EXISTS/IF NOT EXISTS.

I would also rename "checkfirst" to "checkexists" and update the documentation accordingly.

Would it be a good idea to include a long list of supports_[| "create|drop_type" ]_checkexists attributes for each dialect class? I believe some DBMS have mixed IF EXISTS/IF NOT EXISTS support for different object types and for CREATE or DROP and other statements. For instance, PostgreSQL 8.2 supports DROP TABLE IF EXISTS but does not support CREATE TABLE IF NOT EXISTS or ALTER TABLE DROP COLUMN IF EXISTS while PostgreSQL 9.2 supports DROP TABLE IF EXISTS, CREATE TABLE IF NOT EXISTS and ALTER TABLE DROP COLUMN IF EXISTS.

I might be able to work on this and get you a pull request with documentation and tests.

[Mike Bayer]

well the downside is that different database backends would now take an entirely different approach for create/drop, which would remove a certain consistency of approach we've had for many years in this area, as well as place a new and unproven feature at the center of a very stable and critical function. if performance is a concern (which it's generally not) I was thinking we could instead amend dialects to include a new "has_tables" function, that could query the existence of many tables all at once, providing a performance improvement that applies to any backend (at least as long as they support querying for multiple table names at once via system views). the case where most tables exist already would otherwise mean there'd be a lot of fully rendered CREATE TABLE statements being parsed for no reason.

[Rémy Roy]

Would it be acceptable to implement this checkexists, ie the use of IF EXISTS/IF NOT EXISTS for DDL, along side the current checkfirst even though they serve the same purpose?

[Mike Bayer]

I can enthusiastically make IF NOT EXISTS a part of the ddl.py sequence, that is the sequence which is pretty much used only by methods like create_all()/drop_all()/create()/drop(), if I understand exactly what use case it serves - this because a feature is best designed when I have some idea what it's used for. Perhaps it's useful if someone wants to capture a create_all() sequence as a SQL string?

[Rémy Roy]

My personal use case is for migration. I'm currently migrating from using sqlalchemy-migrate to using alembic for schema migration. I have a bunch of databases for a single application which are in different states because they use different version of the same application which use different schema.

In some cases, I would need to create tables, columns, indexes if they do no already exists or drop tables, columns, indexes if they already exists without alembic aborting. I believe the IF EXISTS/IF NOT EXISTS constructs to be well suited for this need.

[Mike Bayer]

yup so adding this to CreateTable/DropTable etc. and then into the appropriate alembic.op as discussed would be all you need; create_all()/drop_all() etc. and the ddl.py sequence isn't used by alembic.

[Kawing Chiu]

Is there any progress on this? I'm having issues on meta.create_all() when trying to create and use the same sqlite db in multiple processes. I noticed that the current implementation of checkfirst uses a separate PRAGMA table_info() before CREATE TABLE. This is really problematic, since there is some time gap between the two operations. When multiple processes trying meta.create_all() at the same time, it leads to a table already exists error. I think IF NOT EXISTS is the proper solution here.

[Mike Bayer]

you should never use meta.create_all() in concurrent processes at the same time.

[Ying-zong Huang]

Reason please?

[Mike Bayer]

Issue #3982 was marked as a duplicate of this issue.