Database development tools

2010/07/26

If you were starting out again in database development where would you go to find useful developer tools?

Compared to general software development, not only is there a lack of tooling in the database development space – there does not seem to be that many sites that list tools in this area.

So, I thought I’d put together a list that I use – or have used in the past.

Most are free to use or require a small payment.

Area Tool Notes
Source control Subversion There are a number of similar tools out there with various pros/cons.
I have found this more than adequate for my needs.
Build tool Nant Similar to the point above. Relatively easy to create a library for automating database specific tasks to reuse in all projects.
Database unit testing tool DBTestUnit There are number of others out there including dbFit , tSQLUnit and utPLSQL
Schema compare RedGate SQL Compare Great tool for comparing environments, will display differences and generate change scripts. Does require payment.
Searching SQL scripts SQLDigger Search SQL scripts for specific words. Really useful when refactoring / changing a db eg finding dependencies between SQL objects. Does require a small payment.
DB sizing tool SQLDBsize Displays the number of rows, disk space reserved and disk space used by your tables or databases.
Auto generating insert scripts SQLStatementGenerator Generates insert, update, or delete SQL statements eg to generate
insert SQL scripts based on existing data in a table.

The Database Testing Framework contains a SQL script in the ‘DatabaseTestingHelperScripts’ that does a similar thing for insert scripts.

Have you got alternatives for the tools listed above?

Or suggestions for tools in other areas such as static code analysis, performance tools?

Last modified: 2010-07-31


Defensive Database Programming with SQL Server

2010/07/24
 
I have just completed reading the ebook edition of this book by Alexander Kuznetsov’s  – which can be downloaded from here.

I would highly recommend it. It is a definite must read for ALL developers.

Jonathan Allen has done a short review of the book which gives an overview of the contents.

A theme that particular resonated with me was Alex’s constant reminder – with example scripts – of the need to test the objects in your database.  Whilst unit testing has become more mainstream in terms of ‘software’ development, in my experience, database unit testing is still not widely practiced.  Hopefully, books like this will encourage more developers to adopt database testing practices.

In Chapter 3 there is a an example of how to handle changes to data types/length on base tables and the issues this can cause for sprocs parameters – he discusses this in this blog article.

This has prompted me to think about adding a new feature to the next release of the database testing framework. This will allow you to test that a sproc/function parameter is the same data type/length as a column.

If someone changes a column without also changing the parameter – the test will fail – and vice versa.

You will also be able to use it taking a test first approach – ie write the test and then create your sql script. If you do not make the parameter the same as a particular column – the test will continue to fail.

A test would be written in a similar way to the script below and would part of the current set of tests for a given sproc.

It would test that the param @Title has the same data type and length – in this case nvarchar(50) – as the column HumanResources.Employee.Title  – (using AdventureWorks uspUpdateEmployeeHireInfo sproc).

Adding new ‘Rows’, as appropriate, would mean that you could test each sproc param that you expect to be the same as a column.

[RowTest]
[Row("@Title", "HumanResources.Employee", "Title")]
public void T06_ParameterAndColumn_AreTheSameDataTypeAndLength(string parameter, string table, string column)
{
	bool areTheSame = sproc.ParameterSameType(parameter, table, column);
	Assert.IsTrue(areTheSame, "Param and column are not the same");
}

Let me know what you think and, perhaps, after you have read Alex’s book you might have further suggestions for features that could be added to the framework.

Last modified: 2010-07-24


Stored procedures – why use them?

2010/07/19

Piotr Rodak has posted an interesting article on the use of instead of triggers. The discussion extended to one about applications having direct access to SQL base tables.

This reminded me of the intense debates on the pros/cons of using stored procedures. These debates often centre on things such as security, performance – very rarely do they mention what I consider to be the most important reasons for using sprocs – the ability to decouple database schema from external applications allowing a more maintainable data contract to be provided.

The problem

After a number of years of use a typical corporate database  will often have a number of external applications that use it eg:

  • Web apps
  • Reporting apps
  • ETL/SSIS components to import/export data
  • Linked server access

These applications often have direct access to base tables. This can make it is very difficult to react quickly to business changes. Even basic refactoring eg base table name changes – become very onerous. A change to a base table will often require changes to all external applications that access it – there is no flexibility in approach all will have to change at the same time.

Allowing direct access to base tables often means that the database schema effectively becomes ‘static’ – as change is seen as being too difficult. In my experience this can lead to data quality issues as, for example, base tables then get reused for purposes that they were not intended for.

How to get around this?

1. Enforce the principle of not giving direct access to base tables to external apps. Direct access should be limited to a small number of views. Changes to base tables can then be isolated to only those views that directly access them.

2. External access should be via sprocs – in some cases this can be extended to views. If a base table needs to be changed then existing data contracts/interfaces are more easily maintained via sprocs – for views ‘instead of triggers’ can be used. In this way external applications are decoupled from these changes.

What about ORMs?

By default ORMs often use direct access base tables. Some such as NHibernate can use sprocs  – but they lose some of ‘their flexibility’ – see Ayende Rahien’s article Should you use NHibernate with Stored Procedure? In the scenario where dev teams are using ORMs and do not want to use sprocs – then the use of views can be a reasonable compromise.

What do you think?

Are sprocs the most appropriate way to allow the decoupling of external apps from databases?

Links

Why use stored procedures?

The public interface of a database

Alternative view point

Why I do not use stored procedures

Last modified: 2011-05-15