Using DDL commands in programming language [duplicate]

Data definition language (DDL) statements are rarely used anywhere. Often, they are used in scripts that are processed by a SQL front end, initiated manually.

However, programming the SQL front end requires that some Java (C, C++, what have you) programs are directly applying DDL on the database.

Similarly, if you have a large application, it is often necessary to provide a set of maintenance screens, available to highly privileged users, which emit DDL statements. For example, certain kinds of maintenance activities might require dropping and recreating tables, changing indices, and adding or removing triggers. By providing the capability in your program, you can significantly reduce the chance of errors by constraining operations in kind or in time.

Finally, testing of database-oriented applications requires that the test scaffolding be able to emit DDL in many circumstances.

In my experience (and it's possible others will have other experiences), an application that modifies its own schema directly is usually a sign of a bad design, and it is probably unnecessary. I've yet to work on an application where the schema itself needed to change dynamically at runtime.

If you start modifying your schema dynamically from your application, all of the queries in your code will need to keep up with the schema changes. Dynamic schema changes may also make it impossible for other applications to share the database if common components of the schema get modified. Stored procedures may also run into similar problems if the schema changes in such a way that they no longer function. Tracking changes to the database schema also gets tricky, unless you log every DDL.

There might be certain cases where it is better, or even necessary to modify an application's database schema while the application is running, but it would seem to me that this is the exception rather then the rule. One situation where application code might modify database schemas directly is applications that are actually designed for this specific purpose, such as Oracle SQL Developer or Microsoft SQL Studio. However they are modifying the schemas of other applications, not their own schemas.

The problem with altering the DB scheme in the code is that DDL commands only need to run once per database installation - not once per program instance. However, if you want to run the DDL commands from the application, you can use a migration framework. For example, in Java's Flyway you write migration classes, and the Flyway framework executes them for you. The migration framework makes sure that the DDL are ran in the right order and that no migration is ran twice on the same DB installation.

Generally, schema changes aren't run by a database client application. If an application needs to create and drop non-temp tables regularly during its normal usage, I'd say that's a sign there might be a problem with the design. For example, if an application is using ALTER TABLE to add columns to support additional values (address1, address2, etc.), its a sign that the DB wasn't normalized.

But... you have to keep in mind the breadth of software development. Issuing DDL statements would not only be common, they'd be necessary, for managing local applications connecting to local databases. So an application that uses SQLite may need to alter the schema automatically when the application updates to support new features.

It is a better practice to treat your database like a separate codebase. I keep my DDL and whole database under source control. There are products like RedGate SQL Source Control which allow you to "check your database in", and I have also developed a similar product. This allows you to do diffs and merges and deployment scripts.

DDL is a one or few time thing. Don't manage it from Java just because it seems novel. There are indeed ORMs that support this, but in all groups I've worked with we frowned upon the practice and did not allow it.

A database may (rarely, but not unheard of) be replaced by a competing vendor's. (Started with MySQL, upgraded to Oracle or SQL Server). Storing DDL in a Java program simply obfuscates the whole life cycle of your app and database.

The real place for your database schema / metadata is in:

1. An ER modelling tool like ERwin or ERstudio or one of the cheaper alternatives.
2. A DB source control
3. The database data dictionary itself. You can simply do DDL-only exports after changes.