Fear of the Unknown

[jducoeur]

It is occasionally fascinating to step back, watch myself program, and critique it.

Today's observation is that I am still scared of database programming. I'm *doing* it, mind you, but I don't have that innate comfort with it that I do with nice in-memory OO programming. There's a lingering sense that I'm impersonating a real DB programmer.

This is particularly coming up today because I am realizing that I need to do a slightly nasty database refactoring for CommYou. (Currently, Communities are linked to Persons -- that is, a Person is a Member of a Community. I am realizing that this is just making my life harder: Communities should be linked to the Identity that they are imported from, which would make synchronization vastly easier and more efficient.)

In principle, this is straightforward, and it's the sort of thing I do in ordinary code without even blinking. After all, the IDE protects me from most of the simple errors, and the test harness from the rest. But refactoring the DB is just plain *scary* to me. I understand the process just fine, and can outline the steps I need to take in detail with no difficulty, but I find myself procrastinating about actually *doing* it.

I can give some objective reasons for that. For one, the IDE does *not* protect me from errors in this very well. And while it's very straightforward for me to test new data created after this change, testing the migrated data comprehensively is much harder. It would be easy for subtle bugs to sneak in.

But mostly, I suspect I'm just still a bit intimidated by the prospect, because I haven't done it much. Nothing for it but to press through, make the changes, and convince myself that the world didn't end...

Comments

[metahacker.livejournal.com]

I hate that feeling.

Would it be helpful to have someone with more DB experience take a look at your design?

The thing about DB design for me is that it's so bleeding obvious, and all the black magic really doesn't matter...until you have 1e09 records and it's too late to transition to a new schema. So it feels like there's this steep ramp, where it's too easy at the start, and then too hard (since I don't understand the black magic) very shortly thereafter.

[jducoeur]

Would it be helpful to have someone with more DB experience take a look at your design?

Possible -- I'll think about it -- but that isn't really the issue. The problem isn't the schema: I have reasonable confidence that I'm going in the right direction. In general, by making Identity more important, I fix a lot of synchronization problems that I see looming in the currently schema. (Which lumps too much under Person, making multiple-Identity Person records hard to synchronize correctly.)

The problem is *getting* there. It's not even the number of records (which at this point is relatively small), it's making sure that I catch all the dependencies and fix all the queries to work correctly in the new model, as well as doing the fairly complicated transforms of the existing data correctly. Perhaps worst of all, it's mostly going to have to be done as a "big bang" -- changing the DB, the existing data, and many queries scattered around the system all at a shot, because they're all interdependent. That's a bad way to do refactoring, and I don't look forward to it.

Hmm. I wonder if I'm going about this wrong. The problem may well be that I've been leaning too much on Hibernate, and really should be doing more at the stored-procedure level. What this may be telling me is that I need to push more abstractions down into DB procedures, so I could decouple the high-level code from them more. That's worth thinking about: if nothing else, it would centralize the problem more, and make it easier to find all the relevant code...

[siderea]

Huh. I often feel like a db programmer masquerading as an application programmer.

I still spend much of my life in low-grade terror of making changes, however. Man, you guys with IDEs are spoiled.

[jducoeur]

'Tis true. It's one of those fear-of-loss situations. I never used to have all these wonderful backstops, but having gotten used to them, it's really painful to lose them.

More significantly, there's a principle that I was taught a few jobs ago, which I've internalized quite deeply, called "Coding With Courage". (I don't know whether that's gotten common enough to be an SDBP staple.) Basically, this says that, if you have your disciplines right, and you apply them rigorously, then you don't need to spend an excessive amount of time worrying and over-thinking. Between a strong IDE, good OO practices and a rigorous test harness, you can generally trust your instincts and just fly along. That's remarkably freeing, leading to days like yesterday, where I did several refactoring passes, moving and reworking about a thousand lines of code without a blip. (There were no bugs, but it helps matters enormously to have enough test coverage that I *know* there were no bugs.)

So losing that freedom *hurts* at a remarkably visceral level. That low-grade terror really rather sucks, all the moreso when you've gotten away from it. Granted, the test harness will help my confidence a lot, but the loss of strong typing is going to make this a very slow and painful process compared to what I'm used to: I'm going to have to re-examine every handcrafted query and think about its implications. I suspect the biggest long-run effect will be to provide incentive to reduce the number of those handcrafted queries...

[mindways]

You could do something like what we do at work: whenever mucking with the database schema or doing data conversion of some sort, we write a pair of SQL scripts: one which makes the changes, and one which backs out those changes. If the migration involves dropping data and/or transforming it in a way that can't be reversed, we copy that data over into tables in a storage database (with table name prefixed by the name of the script-pair which creates/uses it, so we know what it's for and can blow away really old ones if we need to).

If you wanted, with such a scheme you could write an automated test for migrations:
1. Back up database;
2. Run update SQL;
3, Run backout SQL;
4. Compare contents of current database to contents of the backup - all your tables should be identical. [System tables are likely to be different.]

For large databases, step #4 could take a while, so you might want to limit it to comparing data only in some specified set of tables (those you touched in the update/rollback), or it might not be something you'd always want to run.

[jducoeur]

That's good food for thought, and probably a fine approach for the long run. For the short run, it's probably overkill, though: a simple backup really *ought* to be good enough. We back up both the schema and data every night, and I re-backup the data before applying any changes -- that's enough protection against most likely failures. Also, I apply the process to the most recent backup on the build machine and test that before going anywhere near the live site. At the moment, when the amount of data is small (and really, I suspect that I'm the only person who would be *deeply* put out if something went horribly wrong), that should be enough.

Really, my reaction is pretty irrational, which is why it annoys me. I do pretty much know what I'm doing -- I just don't have quite as much confidence in it as I do in the OO side...