Over-thinking Database Build Scripts

  • Phil Factor

    SSCoach

    Points: 19823

    Comments posted to this topic are about the item Over-thinking Database Build Scripts

    Best wishes,
    Phil Factor
    Simple Talk

  • Site Owners

    SSC Guru

    Points: 80385

    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: 16763

    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

    SSCoach

    Points: 19823

    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
    Simple Talk

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    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

    SSCoach

    Points: 19823

    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
    Simple Talk

  • Phil Factor

    SSCoach

    Points: 19823

    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
    Simple Talk

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    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.

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

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