DbFit: Test-driven database development


Important: From January 2013, DBFit has moved and the new home is http://benilovj.github.com/dbfit. This page is outdated and will no longer be updated.

DbFit is a set of FIT fixtures which enables FIT/FitNesse tests to execute directly against a database. This enables developers to manipulate database objects in a relational tabular form, making database testing and management much easier then with xUnit-style tools. The library is free to use, released under GNU GPL.


Features

  • Regression testing for queries and SQL statements
  • Functional testing for stored procedures and functions
  • Automatic transaction control and various short-cuts to make writing test scripts easier and more efficient
  • Support for DB2, Oracle, SQL Server (java and .net versions), MySql 5 and Derby (only java)

For more details, see DbFit pages on fitnesse.info.


Why DBFit

After introducing FitNesse into a big .NET project, I found that we were still lacking test-coverage for a lot of DB (legacy) code, and that DB developers have a long feedback loop, so they were not really benefiting from TDD. FitNesse was a good solution because it enabled DB developers to write functional tests much easier than in pl/sql based unit testing libraries, and without getting Java developers involved to write those as JUnit tests. Although FitNesse is typically used for acceptance tests, the combination of FitNesse+DbFit filled the testing gap for our DB code with excellent results. In fact, as the natural DB interface is relational, FitNesse tables allow writing database tests much more efficiently than using function-centered unit testing libraries. For a more detailed view into the background of DbFit, see Fighting the Monster.


Project goals

  • Enable managing database state from FitNesse as an utility for integration tests involving databases, by .Net and Java developers
  • Enable the use of FitNesse for effective database acceptance and unit testing by DB developers (with no .Net/Java knowledge)

The first group mainly needs reusable fixtures which they can use, either directly on the page or embedded in their project-specific fixtures, and will not mind digging into a bit of Java/.Net code. But the other group must not be made to wait for Java/.Net developers to do effective TDD. So the project is taking a dual approach – developing test fixtures which will be reusable and extendable, but also developing one arch-test fixture which can control the connection, transactions and test flow in a fitlibrary flow mode. So Java/.Net developers can utilise the fixtures directly, and DB developers will be able to utilise the test fixtures through the over-arching fixture control.

See other FitNesse resources on this site


Q&A

For support and to send questions, comments and ideas, join the DbFit discussion group on google groups. Comments are no longer enabled on this page.

289 thoughts on “DbFit: Test-driven database development

  1. Hi Gokul,

    absolutely no idea. I’ve never used fitlibrary folder runner. try to ask on the fitnesse mailing list, maybe someone will have a better idea.

  2. Hello Gojko,

    I am having a little trouble using dbfit as part of a larger fixture structure.

    I want to test a legacy system that uses a database by injecting some test data into said DB, running the system batch processes then comparing the data in the DB with what I expect it to be.

    Now using Fit tables I was thinking the only possible way to model this test system was to use a workflow fixture, the doFixture, and have it return a DatabaseTest fixture whenever I want to processes SQL requests.

    However when I expect say some SQL query to be coloured by fit/fitnesse the tables and all the cells in them are greyed out and counted as ignored. Is this because DatabaseTest is infact based on the doFixture itself and the original fixture code was never designed to allow workflows to create others?

    Matthew

  3. Hello Gojko,

    I have a little problem when running dbfit in standalone mode. I receive this exception in the table that initialises the connection in the DatabaseEnvironment fixture.

    “Missing method: public TypeOfResult dbfitDotFixtureDotDatabaseEnvironment(Type1 arg1) { } in class dbfit.fixture.DatabaseEnvironment”

    I dont quite know what this relates to, but I don’t really want to have to modify third party code.

    Matthew

  4. PS: In addition, if those method sigs are added, the code in the doTables method overidden by DatabaseEnvironment is never executed, do I have an incompatable version of dbfit with my current version of Fitnesse?

    I am using dbfit 1.1 and fitnesse release 20080812.

  5. Matthew,

    the problem is caused by packaging an incompatible version of fitlibrary with fitnesse. I complained about this on the mailing list but nobody from ObjectMentor seems to be interested in fixing it. You should not be required to add those methods to plain fit fixtures, only to fitlibrary stuff. DatabaseEnvironment doTable should be executed, not doTables.

  6. Apologies, I did mean to say “doTable” not “doTables”. Having added instrumentation code to DatabaseEnvironment it seems that method is never run and the default environment never instantiated.

    !|dbfit.fixture.DatabaseEnvironment|informix|
    |connect using file|infrmxCon.txt|

    This is the first line of my fitnesse test page.

  7. I seem to have fixed the problem by adding an import statement to the page.

    !|Import|
    |dbfit.fixture|

    Have you any idea what might have been causing the problem?

  8. Hi Matthew,

    when import is the first table on the page, then the normal fit page processing is used. when DatabaseEnvironment is the first table on the page, fitlibrary kicks in and since an incompatible version of FitLibrary is packaged with the latest fitnesse release, we run into problems.

  9. I am trying to use dbfit and fitserver(c++).
    each pages with dbfit or fitserver is testing well, but i couldn’t run the suite. Gojko, do you have any idea to run with these two difference architectures mixed?

    Thank you for listen to me.

  10. Hi,
    I’m really not sure how c++ fitserver would support dbfit. .NET and Java are supported, C++ is not out of the box. I guess you would need to rewrite dbfit to run under c++.

  11. Gojko,

    Is it possible to pass the table name of a query as a parameter?
    Example: SELECT 1 AS X FROM @tablename

  12. if you can do that directly in the database, you can do it with dbfit as well. dbfit just passes queries to the db. if this is not possible in the db, you might use markup variables to parameterise queries (but this is then not dynamic in run-time), or create your own fixture that creates the query string dynamically.

  13. Hi Gojko
    We’ve been having a great time at Halcrow Rail MIS with Fitnesse and DbFit and have come up with some productive ways to test our ETL code and SQL Server stored procedures. We’ve become huge fans.

    One question: we’ve been having an issue with SQL timeouts while running stored procedures on SQL Server 2005 using the Query fixture. Is there a way we can amend the SQL command timeout of DBFit? We’ve tried adding a “Connection Timeout” to our connect strings but this has no effect.

    Thanks for any help.

  14. Hi Pete,

    not sure, how would you do it if it was not going through DbFit? would you use the connection string or change the way that the call is executed?

  15. I can’t connect to my Derby db from Fitnesse.

    I use the following connection string
    !|dbfit.DerbyTest|
    !|Connect|jdbc:derby://localhost:1527/testdbfit;|

    I’ve started the derby networkServer.

    When I run my Fitnesse test I get the error below
    java.lang.Error: Cannot register SQL driver org.apache.derby.jdbc.ClientDriver
    ……………..

  16. Hi, gojko,

    In SQL Server stored procedures, you indicated that you cannot validate the results of a RETURN call because the type is not known. In SQL Server, the return type of a stored procedure call is always an integer. Does this “rule” make it possible to treat the stored procedure call like a function so we can validate the return value?

    Great work on a great tool, btw!

    Tim

  17. Is there a possibility of a config item that would define the special text to denote a check for a zero length string (or all spaces)? We often want to validate that an OUTPUT parameter has been set to a blank string and Fitnesse itself doesn’t allow this since a blank validation implies “no check.” I’m thinking something along the same lines as the special handling of “null.” It doesn’t necessarily have to be configurable, though this might address a future complaint that someone’s stored proc is actually returning the special text used to denote an empty string.

    Thanks again for a very cool tool.

  18. Hello,

    I just got a new machine. Loaded java (it has 5 and 6) and made sure the Path is correct (c:\Program Files\Java\jre1.5.0_12;c:\Program Files\Java\jre1.6.0_7). But I keep getting the following error:

    C:\Fitnesse\DbFit>java -cp lib\fitnesse.jar fitnesse.FitNesse -p 8085 -e 0 -o

    Exception in thread “main” java.lang.UnsupportedClassVersionError: fitnesse/FitN
    esse (Unsupported major.minor version 49.0)
    at java.lang.ClassLoader.defineClass0(Native Method)

    My old machine still works just fine. I did download the newest release.

    Thanks for the help.

  19. Tim,

    see fixed length string parsing on the query page, this might help check empty strings. regarding sql server stored procedures, I’m still unsure about how this might work. Do you suggest assigning a “return” stored procedure argument to every stored procedure?

  20. gojko, I did try out the fixed length query capability earlier (and retried it today just to be sure):

    |set option|fixed length string parsing|true|
    !|Execute Procedure|usp_ValidateCartonLoadingDestinationForStart|
    |Build Lane|One Carton At A Time|Is On|ReturnLog?|
    |null|Y|N|100,@BuildLane,NULL|
    |BadDivert|Y|N|Invalid Divert. Refer Store Map Table for valid diverts|
    |BadDivert|Y|Y|’ ‘|

    @ReturnLog is declared as varchar(200) and is returning a zero-length string. I’ve tried ”, ‘ ‘, ‘(200 spaces)’ and all produce a failure when comparing. I don’t know if this is relevant, but the error message shows the quoted string (including the quotes) in the expected field, and just the word “actual” in the other field. Also, once you go to one space between the quotes, the error message shows one space between the quotes, no matter how many spaces you put between the quotes.

    I messed around with the stored proc, returning an explicit ‘ ‘, ‘Hi ‘, ‘Hi’ with corresponding quoted validation values in dbfit. None of them matched, so, I’m thinking that the “fixed length string parsing” setting isn’t actually affecting the comparison logic for stored procs (?). I pasted the line for “Set option” so I don’t think it is horked over. Is there anything obviously wrong with my approach?

  21. gojko, I think I now understand the issue with a return value for a stored proc. You’re implying that a change like this would break every existing check since the line would now *require* a reference to |?|, right? Is it at all reasonable to think that you could make the presence of the |?| optional? If it is there, it compares against the return value and if not, treat the stored proc as if it had no return value.

    Sorry for just asking the question. I really should do a little code spelunking…

  22. gojko, is there a way to escape the ‘|’ character in DbFit. I’m have a need to use this character in my query.
    Thanks.

  23. hei gojko,

    I found your software recently through a SQL Server Central article:

    http://www.sqlservercentral.com/articles/Testing/64636/

    and in attempting to follow it I’m running into trouble. I’m running against a SQL Server 2000 box and have the following code:

    !|Insert|DBFitTest|
    |EmpName|FavoriteColor|
    |Ian Curtis|NULL|
    |Peter Hook|Grey|

    to which I get:

    System.Data.SqlClient.SqlException: Incorrect syntax near the keyword ‘precision’.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    I do have connectivity to the database because I have queries that return values (including one that indicates the presence of the table)… and I also have the most recent (as of a couple of days ago) version of the software… I’ve looked at docs and the syntax seems right… what am I running into?

    1k thx – e

  24. Ekkis,
    use SqlServer2000Test instead of SQLServerTest when running on 2000. that will give you most of the functionality with legacy meta-data extraction. SQLServerTest only works with 2005 onwards

  25. Does DBFit support SQL 2008? I am getting the following error when I run the following query SELECT CONVERT(BIT, ISNUMERIC(’12’)) AS ‘result':
    fitlibrary.exception.InvalidMethodException: Missing method ‘query’ with 5 argument(s).
    at fitlibrary.Method.FindFirst(Object theTarget, IdentifierName theIdentifierName, Int32 theParameterCount)
    at fitlibrary.FlowFixtureBase.FindMethod(CellRange theCells)
    at fitlibrary.FlowFixtureBase.FindRowMethod(Parse theRow)
    at fitlibrary.FlowFixtureBase.ProcessFlowRow(Parse theCurrentRow)

  26. David,

    the error suggests that your table format is wrong (5 arguments for the query method). Can you send me the fitnesse page that you are trying to use?

  27. Gojko,

    I am having the same sort of timeouts as Pete Johnson above (http://gojko.net/fitnesse/dbfit/#comment-36523). In your reply to him (http://gojko.net/fitnesse/dbfit/#comment-36534), you asked “How would you do it if it was not going through DbFit? would you use the connection string or change the way that the call is executed?”

    In C#, I would do it by setting the CommandTimeout property of the Command object. As far as I know, CommandTimeout cannot be set through the connection string. (ConnectionTimeout can be set, but that only governs how long it will wait to make the initial connection, not how long each command will be given.)

    –Bill

  28. Bill,

    thanks for the info, I’ll add that option to dbfit 1.2. I’m a bit behind on dbfit features because my upcoming book is taking all my time, but I promise to get this done as soon as the book is out.

  29. Gojko,
    To make use of DbFit in my project I have added Postgres support to the Java version. If it is possible, may I send my code to you for an expert review. I’m sure there are places where things may be done a little more optimally.
    Thanks.

    -Linh

  30. Robert,

    PostgreSQL support is in development. As soon as i finish my book i’ll be back on polishing dbfit, so I expect the real version to be released in the next month or so.

Comments are closed.