Over-thinking Database Build Scripts

Question

Post reply

Over-thinking Database Build Scripts

Phil Factor

SSC-Insane

Points: 20244
More actions
June 29, 2019 at 11:00 am

#3658491

Comments posted to this topic are about the item Over-thinking Database Build Scripts
Best wishes,
Phil Factor

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

call.copse SSCoach Points: 18064 More actions · Answer 2

No conflict, it's all good. I use state based when there's no conflict and migration if data needs to be split apart, populated in a particular way or similar (which tbf is rarely if you plan right but will happen).

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

I wonder if the confusion is caused by the false idea that it is somehow wrong to add migration code to object-level build scripts. I put all sorts of stuff like that in the object-level build scripts of a table if it is necessary, checked so migration stuff is only executed when necessary. A table build script, after all, will usually have a batch of CREATE statements in it. and will call a system procedure to insert the extended properties. Then there is the access control stuff. Why not any necessary migration stuff too?

Best wishes,
Phil Factor

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

I think the challenge is that the actual implementation of how you deploy changes presents challenges. You can't easily mix state and migrations if your deployment method is a particular process that runs ordered migration scripts, or one that just runs the object script. After all, adding a migration script to a CREATE table doesn't work. While I wish I could have v1 as

create table sometable
( somekey int not null
, somevalue varchar(100)
, constraint sometablePK primary key (somekey)
)
go

and v2 as

create table sometable
( somekey int not null
, somevalue varchar(100)
, anewval varchar(100)
, constraint sometablePK primary key (somekey)
)
go
update sometable
 set somevlaue = ''
 where somevalue is null
go
alter table sometable alter column somevalue varchar(100) not null

with the platform knowing that v2 has a new column (just add it) and if this has run, don't bother with the last alter table. I'd think a compiler would be able to figure that out easily and no-op some of those items.

Since SQL is a broken language, however, what happens if I have v2 in the VCS? What if V2 is just

update sometable
 set somevlaue = ''
 where somevalue is null
go
alter table sometable alter column somevalue varchar(100) not null

That's how I've worked in some environments and it was fine. We were evolving an app and were never going back to the CREATE statement. We had a DevOps style flow, but all intermediate environments were built with refreshes from prod backups (not sanitizied, ugh, how awful we were).

If you want to mix state and migrations, that means you're really working in one or the other and then creating an exception for some changes. That likely works with talented people being careful, but it's risky. Esp if said talented person goes on holiday.

I'm a migrations guy, because I think this is the best way. I also think that we can edit these scripts in the VCS until they're deployed to the final environment. we can remove both initial and reversing operation scripts if those never get deployed, or we can change things we've caught in intermediate spots that cause issues.

However, I get the simplicity of state/model changes, as long as you know the problem domains that it can't solve. In those cases, what do you do? Most people use some pre/post scripting to address this, perhaps editing the script to remove the state based "guess" of how to make the change. That's risky to me. Really, I think you need to know what you can and cannot do, and always have a migration process, or maybe just a separate script runner process, for those ad hoc changes that don't work well with state.

In that case, I might make the ad hoc changes and once they're done, feed back the end result into my dev environment (and VCS) so this won't produce some duplicate change operation at a later date.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 5

Some interesting thoughts there, Steve. I once wrote an article in early 2012 on using migration scripts with databases but it never occurred to me at the time that it represented a different way to building databases. I really don't think that doing migration scripts is much different to putting in 'guard clauses'. These allow you to 'rebuild' a database as well as build it. Migrations only affect tables and can surely meet every eventuality if you create a table build that allows you to ALTER the table if it already exists while preserving the data (as I illustrated in the article), then surely that is sufficient without introducing a new methodology. My code even allowed a backward migration as well. Sure it requires a bit of skill and care the first few times you do it but it isn't egg-head territory.

Best wishes,
Phil Factor

Phil Factor SSC-Insane Points: 20244 More actions · Answer 6

given your problem, I would have done this, Steve.

IF Object_Id('dbo.sometable') IS NOT NULL
   DROP TABLE dbo.sometable;

--we script version 1
CREATE TABLE dbo.sometable
  (
  somekey INT NOT NULL,
  somevalue VARCHAR(100) NULL,
  CONSTRAINT sometablePK PRIMARY KEY (somekey)
  );
GO
INSERT INTO dbo.sometable (somekey, somevalue)
VALUES
  (1, 'Un'),
  (2, 'Dau'),
  (3, 'Tri'),
  (4, 'Pedwar'),
  (5, 'Pump'),
  (6, 'Chwech'),
  (7, 'Saith'),
  (8, 'Wyth'),
  (9, 'Naw'),
  (10, 'Deg'),
  (11, NULL),
  (12, NULL),
  (13, NULL);

GO

--we now script version 2
IF Object_Id('dbo.sometable') IS NULL
  BEGIN
    CREATE TABLE sometable
      (
      somekey INT NOT NULL,
      somevalue VARCHAR(100) NULL DEFAULT '',
      anewval VARCHAR(100) NULL,
      CONSTRAINT sometablePK PRIMARY KEY (somekey)
      );
  END;
ELSE
  BEGIN
    IF NOT EXISTS
      (
      SELECT *
        FROM sys.columns
        WHERE name LIKE 'anewval' AND object_id = Object_Id('dbo.sometable')
      )
      ALTER TABLE sometable ADD anewval VARCHAR(100) NULL;
    IF NOT EXISTS (SELECT * FROM sys.default_constraints WHERE name LIKE 'MyConstraint')
      BEGIN
        ALTER TABLE sometable ADD CONSTRAINT MyConstraint DEFAULT '' FOR somevalue;
        /* You can specify NOT NULL in ALTER COLUMN only if the column contains no null values. 
The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed,*/
        UPDATE sometable SET somevalue = DEFAULT WHERE somevalue IS NULL;
      END;
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE name LIKE 'somevalue'
AND is_nullable = 0
)     ALTER TABLE sometable ALTER COLUMN somevalue VARCHAR(100) NOT NULL;

  END;

GO

You'll see that the pure CREATE statements are there and executed if conditions are appropriate. Otherwise guard clauses take care of the data. it brings the table to the right version whatever its existing state. I see this as a typical build script that takes care of the existing data. It is not a migration script is it?

Best wishes,
Phil Factor

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

It's not, but it's a lot of work, and easy to make a mistake in complex guard clauses. Add to this the evolution across a dozen changes and the scripts become quite cumbersome to examine.

Works fine, but when I think of this and staff changing, training junior DBAs, I cringe a bit.