Over the previous 2 blog posts, we have seen that managing the lifecycle of database objects in an idempotent manner is impacted by the imperative nature of most SQL statements, which require a known initial state for changes to be applied repeatably.
Under certain conditions, notably for objects that carry no data (or whose data can be easily reloaded), maintain no state (or whose state is unimportant), simply dropping and recreating the object gives an idempotent approach to their management. Most CREATE statements provide an OR REPLACE modifier to achieve this in a single operation.
Additionally, many ALTER statements provide idempotent methods, such as SET, UNSET and RENAME, which enable repeatability in the automation of object management.
Grants can be applied in an automated manner without worrying about the specific action taken to create/update the object (or even if anything was done at all), as these operations are idempotent.
However, for objects where the above approaches are insufficient to meet the database model’s requirements, a more targeted method of change detection and specific alteration would be required. The various strategies, in particular imperative (and the different flavours of imperative) and declarative are discussed in our next series of blog posts.