You write scripts to deploy your stored procedures, DDL, and functions and you store them in version control. You run those scripts from a remote connection with the appropriate database client installed using DB credentials with the appropriate privileges. Alternately, you run the updates directly on the database server as part of an Ansible/Puppet/Chef script.
My point is that you make the database deployment part of your pipeline just like everything else.
I think that the underlying problem here is that most developers, and some DBAs, aren't keeping DDL under version control. Instead, they are just using GUI tools to alter tables and, on very special occasions, create procedures.
They don't treat the database with the same reverence as their app code and then blame the database for being out of sync.
Onebox deployment typically deploys the new revision of the application code to a single host/box and leaves the rest of the fleet in the older revision and then you run your tests against the new revision running in that single box. You also might monitor the metrics from that single box and attempt to find regressions from the rest of the fleet.
If the code is deployed to the database (version controlled or not) all application servers will start using the new code immediately which is not what we would want from a onebox deployment first strategy. Usually all the servers will share the same database and thus the code in the code in database model. What am I missing.
You could easily create a new revision of the function you want to change, and have the first box to use the new revision while the rest keep using the old one.
In postgres that would be trivial with revision schemas and a clever usage of the SEARCH_PATH.
Sure, it works for a narrow use-case like a pure function that just queries and returns data. What about stored procedures/triggers that mutate data on insert/delete etc?
With one-box, you can assign a specific set of customers/canaries to be served by the new 1-box and validate your results. If there is a problem you rollback and only your canary data was affected. It is just not very simple IMO.
That is a neat idea. I was sort of thinking of that, but was concerned about invalid and unused database objects.
You'd avoid name collisions pretty handily, though. I'm guessing that you would just need some policy to drop all of the old versions after some period.
You'd also just need to bundle grant exec statements with the procedure DDL.
That would work well with Oracle and DB2 as well. I'm not totally sure about SQL Server, though.
Ah. I see my misunderstanding. This is my fault for trying to think at 4am.
That is a bit trickier. You will absolutely not be able to do that without computing stored procedure names using a version number, which seems awful and not worth the trouble.
That said, you can avoid application errors during rollout by keeping procedure signatures the same and not removing columns from returned result sets. The unfortunate side effect is that errors in your procedures can work with the new nodes and cause errors in the previously functional ones. Although, I guess you already had that potential exposure with any other database schema change that was made, right?
You write scripts to deploy your stored procedures, DDL, and functions and you store them in version control. You run those scripts from a remote connection with the appropriate database client installed using DB credentials with the appropriate privileges. Alternately, you run the updates directly on the database server as part of an Ansible/Puppet/Chef script.
My point is that you make the database deployment part of your pipeline just like everything else.
I think that the underlying problem here is that most developers, and some DBAs, aren't keeping DDL under version control. Instead, they are just using GUI tools to alter tables and, on very special occasions, create procedures.
They don't treat the database with the same reverence as their app code and then blame the database for being out of sync.