TDD on SQL Schema. Why not? A normal Test Driven Development approach means that you start with writing a test case which creates the requirement to write appropriate code in order for the test to pass. This results in a very quick iteration where you add new unit tests, verify that they fail, implement the code for the test to pass and verify that the test passes. A single iteration can be just few minutes or less and the test set usually executes in just a few seconds. The end result is that you will end up with great test coverage which helps refactoring and in itself it helps explaining the user stories in the code.

Applying TDD to SQL

At first writing CREATE TABLE declarations doesn’t sound like something worth testing, but modern SQL database engines offer a lot of tools to enforce proper and fully valid data. Constraints, foreign keys, checks and triggers are commonly used to validate that invalid or meaningless data is not stored in the database. This means that you can certainly write a simple CREATE TABLE declaration and run with it, but if you want to verify that you cannot send invalid data to a table then you need to test for it. If you end up writing triggers and stored procedures it is even more important to write proper tests.

I picked up Ruby with it’s excellent rspec testing tool for a proof-of-concept implementation for testing a new schema containing around a dozen tables and stored procedures. Ruby has a well working PostgreSQL driver and writing unit test cases with rspec is efficient in term of lines of code. Also as Ruby is interpreted executing a unit test suite is really fast. In my case a set of 40 test cases takes less than half a second to execute.

Example

Take this simple twitter example. I placed a complete source code example in github at https://github.com/garo/sql-tdd

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    login VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(40) NOT NULL
);

CREATE TABLE tweets (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) NOT NULL,
    tweet VARCHAR(140) NOT NULL
);

The test suite will first drop the previous database, import the schema into the database from schema.sql following with any optional and non-essential data from data.sql and then run the each unit test case. Our first test might be to verify that the tables exists:

it "has required tables" do
  rs = @all_conn.exec "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"
  tables = rs.values.flatten
  expect(tables.include?("users")).to eq(true)
  expect(tables.include?("tweets")).to eq(true)
end

Maybe test that we can insert users into the database?

it "can have user entries" do
  ret = @all_conn.exec "INSERT INTO users(login, full_name) VALUES('unique-user', 'first') RETURNING id"
  expect(ret[0]["id"].to_i).to be > 0

  ret = @all_conn.exec "SELECT * FROM users WHERE id = #{ret[0]["id"]}"
  expect(ret[0]["login"]).to eq("unique-user")
  expect(ret[0]["full_name"]).to eq("first")
end

Verify that we can’t insert duplicated login names:

it "requires login names to be unique" do
  expect {
    ret = @all_conn.exec "INSERT INTO users(login, full_name) VALUES('unique-user', 'second') RETURNING id"
  }.to raise_error(PG::UniqueViolation)
end

What about tweets? They need to belong to a user, so we want to have a foreign key. Especially we want that you can’t violate the foreign key constraint:

describe "tweets" do
  it "has foreign key on user_id to users(id)" do
    expect { # the database doesn't have a user with id=0
      ret = @all_conn.exec "INSERT INTO tweets(user_id, tweet) VALUES(0, 'test')"
    }.to raise_error(PG::ForeignKeyViolation)
  end
end

If you want to test for a trigger validation using a stored procedure then that violation would raise a PG::RaiseException. Using invalid value for an ENUM field would raise a PG::InvalidTextRepresentation. You can also easily test views, DEFAULT values, CASCADE updates and deletes on foreign keys and even user privileges. Happy developing!