Support for IF EXISTS/IF NOT EXISTS DDL constructs
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.
Comments (14)
-
reporter -
repo owner this is always something people can help along via pull request, we've been doing well with those lately
-
reporter 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.
-
repo owner 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.
-
reporter 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?
-
repo owner 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?
-
reporter 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.
-
repo owner 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.
-
repo owner - changed milestone to 1.0.xx
-
repo owner - edited description
- changed milestone to 1.x.xx
-
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 ofcheckfirst
uses a separatePRAGMA table_info()
beforeCREATE TABLE
. This is really problematic, since there is some time gap between the two operations. When multiple processes tryingmeta.create_all()
at the same time, it leads to atable already exists
error. I thinkIF NOT EXISTS
is the proper solution here. -
repo owner you should never use meta.create_all() in concurrent processes at the same time.
-
Reason please?
-
repo owner Issue
#3982was marked as a duplicate of this issue. - Log in to comment
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