When Behavior Driven Development BDD was introduced, some of the key principles were
- Requirements are behavior,
- Provides “ubiquitous language” for analysis,
- Acceptance criteria should be executable.
- Design constraints should be made into executable tests.
All of these principles can be applied to database development. When interacting with the database, we tend to assume certain behavior of the database. Some of this is universal, like when a row is inserted in a table, the same row can later be retrieved. There are other behaviors of the database on every project that are not that universal, like Person table should have at least firstname or lastname. This behavior changes based on the functionality being developed and thus needs to be properly specified and executed. The database lends itself very well to the new way of thinking in the BDD space, where the behavior of the objects is considered. BDD is similar to describing requirements in code. Here we are going to see how the BDD techniques can be applied to database development and how these techniques can be used to develop and design databases, in an iterative and incremental way. Lets call these technique as Behavior Driven Database Development (BDDD).
While designing database objects, we are expecting these objects to behave in a certain fashion and we tend to rely on this behavior. Let’s say we make a column NOT NULLABLE. We assume that the database server will throw an exception when a NULL value in inserted in this column, making the same column NULLABLE later, can alter this behavior of the database. When the behavior of the database is changed this way, all the assumptions that the application made about the database NOT allowing NULL values in the column are no longer true. To avoid these kinds of mistakes in assumptions of behavior, we can test the database behavior to assert that the database does throw an exception when NULL values are put in column.
In this blog article, lets talk about
- Design a Table
- Design a Primary Key
- Design a Not Null Column
- Design a Constraint on a Column
- Design a Foreign Key Constraint
- Design a Sequence for Object ID
- Design a Unique Index
Lets assume we are building a Movie Rental system to be deployed at stores (not many of them are around nowadays). The store wants to track what DVD’s they have and who has rented the DVD’s. For simplicity sake lets assume we are using Java, Hibernate, Oracle and JUnit as some of the technologies. You can substitute these technologies with any others you like.
Design a Table
When starting to work on the feature “As a Store Manager I should be able to select a DVD to rent”. The attributes of the movie Object and movie table have to be decided first. Once you decide the attributes, these attributes need to be mapped in Hibernate mappings.Start with a test first that tries to create a domain object, save the domain object and fetch the domain object back from the database. Let’s see the test
1 2 3 4 5 6 7
The above test, just verifies that it can create a Movie domain object and then save it in the database, the behavior we are driving out here is that a valid Movie domain object can be saved. At this stage the database script for Movie table looks like
1 2 3 4
Design a Primary Key
The next task we want do is to make sure the MOVIE table has a Primary Key and there is a value assigned to the primary key when the MOVIE domain object is saved. We can also check the Hibernate mapping behavior to make sure that the correct SEQUENCE is used to assign the next ID to the MOVIE object. The test looks like.
1 2 3 4 5 6 7
The above test shouldAssignPrimaryKeyValuesFromSequence checks the behavior of the Hibernate mapping, the Primary Key constraint and makes sure that the correct sequence S_MOVIE is used to populate the ID value for the Movie object. At this stage the database script for the Movie table and S_MOVIE sequence looks like
1 2 3 4 5 6
## Design a Not Null Column ##
The next feature to work on is “As a Internal User I should be able to assign a year the movie was made”. Lets also say one of the requirements is to make sure that every movie has to have the year it was made. We will use Make Column Non Nullable database refactoring on the database. Starting with the test as shown below.
1 2 3 4 5 6 7 8 9 10 11
As can be seen in the above example, the behavior of the database to throw an exception when one of the rules set on the table is not satisfied. If this assumption That the database does not allow NULL values in the year column is enforced by the test ShouldNotAllowNullYear, when this assumption on the database is changed this test will fail. During refactoring of the database, these tests help to enforce the assumptions made on the database. After this stage the database table looks like
1 2 3 4 5 6 7
Design a Constraint on a Column
The next feature to work on is The store does not carry any movies made before 1999. Lets start with Introduce Column Constraint database refactoring to create a column level constraint on the YEAR column so that it does not allow any value less than 1999.
1 2 3 4 5 6 7 8 9
The behavior of the database to disallow values before 1999 is asserted by the test “ShouldNotAllowMovieYearBeforeYear1999”. At this stage the database script looks like
1 2 3 4 5 6 7 8 9
Design a Foreign Key Constraint
The next feature to work on is Movie has details about itself that need to persisted. To store the details about the movie we will Introduce New Table MovieDetail with a MovieId on the MovieDetail table. So effectively we will have a collection of MovieDetail objects on the Movie object. Having a MovieDetail object created without the MovieId on would create dirty data in the MovieDetail table, so we will Add Foreign Key Constraint on the MoveDetail table. Having a NULL MoveId would also invalidate the MoveDetail object, since a MovieDetail cannot exist without Movie, making the MoveDetail.MovieID not null.
This assertion can be done by the domain object, but I have seen over years of consulting at many different companies that the database gets used eventually without the domain layer (Reporting, Data Extract, Data Import etc.), so it’s a better to move these kinds of constraints on the database. The following test will ensure that the database behavior matches what we expected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
The above test tries to delete the Movie object from the database when it has the MoveDetail as its child, this forces the database to raise a Foreign Key violation, which is verified by the test, similarly we can write a test where the MoveDetail.MovieId is null.
1 2 3 4 5 6 7 8 9 10 11 12 13
At this stage the database script looks like
1 2 3 4 5 6 7 8
Design a Sequence for ObjectId
The next feature to work on is a Technical Story to reduce database trips to get the ObjectID for every new Object created. To reduce the number of database trips for every object created, we can create a sequence (Oracle database specific), which returns values in Increments of 1000. Then we can make the application return one ID at a time from the value that was returned by the database, reducing the number of round trips to the database from the application. When the application exhausts the 1000 IDs it asks the database for the next 1000.
Which means that we are relying on the database behavior to return values in increments of 1000. The following test will ensure that the database behavior matches what we expected.
1 2 3 4 5 6
The above test tries to get the next value from the “S_MOVIE” sequence and compare if the consecutive values returned have incremented by 1000. At this stage the database script looks like
1 2 3
Design a Unique Index
The next feature to work on is “The system should not allow duplicate Movie.Name in the system”, to implement this feature we use the Introduce Index refactoring, the behavior of the database ensures that the Movie.Name cannot be duplicated.
1 2 3 4 5 6 7 8 9 10
The above test tries to persist the Movie object with duplicate names and expects that the database throws a Unique Index violation. At this stage the database script looks like
These behavior specifications in code make sure that the database provides the specified behavior for the application and that the database cannot be changed inadvertently. These kinds of tests are also important if there is a need to have multiple database compatibility in your application code base. These kinds of tests are also really useful if you are depending on some other applications database and are expecting certain behavior.