Agile Behavior Driven Database Development (BDDD)

Pramodkumar J Sadalage, https://www.sadalage.com/, https://databaserefactoring.com/

When Behavior Driven Development (BDD) [1] 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 Agile 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.

I am going show how the BDD technique can be applied to database development and how this technique can be used to develop and design databases in an iterative and incremental way.

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 column NULLABLE 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 mistake, we can test the database behavior to assert that the database does throw an exception when NULL values are put in column. In this article I will 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

The example domain being discussed here is Movie rental store. 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

@Test
public void ShouldBeCreatedAndSavedSuccessfully() {
Movie movie = new Movie();
movie.setName(name);
saveDomainObject(movie);
assertNotNull("Insert failed", movie);
}

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

CREATE TABLE MOVIE (
MOVIEID NUMBER(18),
NAME VARCHAR2(64)
);

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.

@Test
public void shouldAssignPrimaryKeyValuesFromSequence() {
Movie movie = createAndSave("PKASSIGNED");
Long currentPKValue = getCurrentValueForSequence("S_MOVIE");
assertNotNull("Movie should have been assigned a ID", movie.getId());
assertEquals("ID should have been same",movie.getId(),currentPKValue);
}

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

CREATE TABLE MOVIE (
MOVIEID NUMBER(18),
NAME VARCHAR2(64),
CONSTRAINT PK_MOVIE
PRIMARY KEY (MOVIEID)
);
CREATE SEQUENCE S_MOVIE;

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 [2] database refactoring on the database. Starting with the test as shown below.

@Test
public void shouldNotAllowNullYear() {
Movie movie = new Movie();
movie.setName(name);
try {
saveDomainObject(movie);
fail();
} catch (ConstraintViolationException e) {
assertContains(e,"ORA-01400: cannot insert NULL");
}
}

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

CREATE TABLE MOVIE (
MOVIEID NUMBER(18),
NAME VARCHAR2(64),
YEAR VARCHAR2(4) NOT NULL,
CONSTRAINT PK_MOVIE
PRIMARY KEY (MOVIEID)
);

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 [3] database refactoring to create a column level constraint on the YEAR column so that it does not allow any value less than 1999.

@Test
public void shouldNotAllowMovieYearBeforeYear1999() {
try {
createAndSave ("1998", "YearBefore");
fail("Movie year is before 1999");
} catch (Exception e) {
assertContains(e,"CHK_MOVIEYEAR_GT_1998");
}
}

The behavior of the database to disallow values before 1999 is asserted by the test "ShouldNotAllowMovieYearBeforeYear1999". At this stage the database script looks like

CREATE TABLE MOVIE (
MOVIEID NUMBER(18),
NAME VARCHAR2(64),
YEAR VARCHAR2(4) NOT NULL,
CONSTRAINT PK_MOVIE
PRIMARY KEY (MOVIEID),
CONSTRAINT CHK_MOVIEYEAR_GT_1998
CHECK( YEAR > '1998')
);

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 [4] 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 [5] 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.

@Test
public void shouldNotAllowMovieDetailsToExistWithOutMovie() {
Movie movie = createAndSave("DoomsDay");
MovieDetail detail = new MovieDetail();
detail.setDescription("DoomsDayMovie");
detail.setMovie(movie);
detail.setUrl("www.doomsday.com");
saveDomainObject(detail);
try {
removeDomainObject(movie);
fail();
} catch (Exception e) {
assertContains(e, "FK_MOVIEDETAIL_MOVIE");
}
}

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.

@Test
public void shouldNotAllowMovieDetailsToExistWithNullMovie() {
MovieDetail detail = new MovieDetail();
detail.setDescription("DoomsDayMovie");
detail.setMovie(null);
detail.setUrl("www.doomsday.com");
try {
saveDomainObject(detail);
fail();
} catch (Exception e) {
assertContains(e, "cannot insert NULL into");
}
}

At this stage the database script looks like

CREATE TABLE MOVIEDETAIL (
MOVIEDETAILID NUMBER(18),
MOVIEID NUMBER(18) NOT NULL,
DESCRIPTION VARCHAR2(4000),
URL VARCHAR2(400),
CONSTRAINT PK_MOVIEDETAIL
PRIMARY KEY (MOVIEDETAILID)
);
ALTER TABLE MOVIEDETAIL
ADD CONSTRAINT FK_MOVIEDETAIL_MOVIE
FOREIGN KEY (MOVIEID)
REFERENCES MOVIE;

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.

@Test
public void shouldIncrementIdBy1000() {
Long firstValue = getNextValueForSequence("S_MOVIE");
Long secondValue = getNextValueForSequence("S_MOVIE");
assertIncrementsBy1000(firstValue, secondValue);
}

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

CREATE SEQUENCE S_MOVIE
START WITH 1
INCREMENT BY 1000;

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 [6] refactoring, the behavior of the database ensures that the Movie.Name cannot be duplicated.

@Test
public void shouldNotAllowDuplicateNames() throws Exception {
Movie movie = createAndSave("NODUPE");
try {
Movie dupe = createAndSave("NODUPE");
fail("Duplicate Movie name is allowed");
} catch (ConstraintViolationException e) {
assertContains(e, "UIDX_MOVIE_NAME");
}
}

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

CREATE UNIQUE INDEX UIDX_MOVIE_NAME
ON MOVIE (NAME);

Conclusion

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.

References

[1] Introduction to BDD, Dan North, http://dannorth.net/introducing-bdd

Refactoring Databases: Evolutionary Database Design

[2] https://databaserefactoring.com/MakeColumnNonNullable.html

[3] https://databaserefactoring.com/IntroduceColumnConstraint.html

[4] https://databaserefactoring.com/IntroduceNewTable.html

[5] https://databaserefactoring.com/AddForeignKey.html

[6] https://databaserefactoring.com/IntroduceIndex.html

Related Methods & Tools database articles and Agile resources

• Database Locking: What it is, Why it Matters and What to do About it
• The Return of ACID in the Design of NoSQL Databases
• Scrum and Agile resources portal

---

Click here to view the complete list of archived articles

This article was originally published in the Winter 2008 issue of Methods & Tools