5 ways to make your database better - by Tim Abell
[1] Documentation
Shoot me okay, but maintenance of software is [insert large number here] times the cost of creation, especially with relational databases. You are a pro working for a client, you owe it to them to make it possible for them to have future staff (and yourself!) be as effective as possible. You put all that effort into figuring out why a column should exist and have that name, now share that knowledge before you move on to the next greenfield project
- Redgate SqlDoc is great for rapidly adding missing documentation.
- SchemaSpy generates static html sites making it easy to see what documentation there is (or isn’t!) and share it with the team. It’s free & open source (although a bit fiddly to set up and run). It has particularly nice clickable diagrams.
- Dataedo generates static html sites & pdfs as well, and is commercial and slicker than SchemaSpy
- This gist for source-controlling ms_description attributes gives you a two-way source-controllable / editable list of your documentation in SQL Server
- SQL Schema Explorer generates dynamic html sites making it easy to see what documentation there is and share it with the team.
[2] Refactor your database
Migrations are a thing now. Use them. You refactor your code, why wouldn’t you refactor your database? Stop leaving landmines for future people - misleading names, bad structures etc. Use the redgate tools (ready-roll etc), use your orm’s tools (EF migrations, active record migrations). Yes you have to deal with data, but it’s the exception not the rule that it’s going to take hours to run because of data volumes.
- Redgate’s SQL Change Automation (formerly ReadyRoll) is an opinionated tool for creating and running database migrations, it even generates Database Administrator (DBA) friendly pure-sql deployment packages. Very impressive!
- Redgate’s SQL Source Control supports migrations
- I’ve been using EF Core migrations recently and they work well. There are equivalents for all the major platforms.
[3] Enforce data integrity
Does your app fall over if the data is bad? Databases have many powerful ways of enforcing the rules your code relies on: nullability, foreign keys, check constraints, unique constraints. Stop the bad data before it even gets in there. Now your database is enforcing these rules your code doesn’t have to handle violations of them when reading data because they’ll never happen
[4] Integration testing
You have an ORM. Great. You have unit tests. Great. But where the rubber hits the road and your code sends SQL to a real database it breaks at runtime more often than you’d like to admit because the generated sql didn’t jive with the real database structure or data in some obscure fashion. Automate the creation/test/destruction of your db and run full end to end integration tests. I suggest automating from the layer below the UI to keep the tests fast. There are many techniques for keeping the tests quick but still realistic: do end to end smoke tests instead of individual pieces, use an in-memory database, use database snapshots or the fancy sql-clone tool from Redgate to make creation / rollback virtually instant. Can you pull realistic (anonymised) data from production? Better still, now you’ll catch a whole new class of bugs before they hit prod.
[5] Make it visible
Are the only people that can see the database structures the coders and DBAs? do the business owners, support people, Quality Assurance (QA) people find it a mystery? You should be just as proud of your database as you are of your code, by shining a light on this dark corner of your digital estate you can make it as good as it should be, not an embarrassing backwater. By sharing the database in an accessible form to the non-coders in your team you can help them be more effective in their jobs.
- The html generated by SchemaSpy can be shared on any webserver to let your whole team see your schema structures
- SQL Schema Explorer can be run on your network or cloud hosting (schema explorer is dockerized!) to give your team easy access to both the schema and data within the database.
Combine these tools with a continuous integration system and you have easy access to the bleeding edge of your databases development.
Take action now!
1.Make a start on at least one of these improvements today. 2.Share this article with your team - get everyone motivated to improve. 3.Share this article on social media - help spread the word that our databases deserve better!
I hope this has inspired you to make an improvement in the often unloved underbelly of your applications.
What do you think needs improving in the way we deal with databases? What change did you make because of this? Let me know!
Originally posted at http://schemaexplorer.io/blog/2018/07/10/5-ways-to-make-your-database-better.html