Using SQLite in test, and why you should be carefull.

I recently started using phpunit. Yeah, don't shout at me...! I am just a self taught programmer, and I have to do everything all by myself. Anyway, I become to like testing.

As I am using Symfony a lot, I always watch for tips and tricks. Then I came along this video series of Gary Clarke, which by the way, I respect highly as I am learning much of his videos.

In this video he shows a trick of using an in-memory SQLite database for very fast test-runs. After a little investigation, I started to use it as well.

All went fine, until I started to test my (date-driven) validations. In a specific case, I had to test if a new requested date was bigger than a value in the database. This is for preventing overlapping date ranges.

The used validation runs a DQL in the repository, and if records are returned, the dates overlap.

It worked perfectly in my dev environment (MariaDB), but when I started to write the tests, one assertings continually failed, which actually was valid in my manual dev test.

Here's the QueryBuilder logic involved in the validations:

 return $this->createQueryBuilder('e')
     ->andWhere('e.asset = :asset')
     ->andWhere('(:requestDateTo > e.fromDate AND :requestDateFrom < e.toDate)')
     ->setParameter('requestDateFrom', $booking->getFromDate())
     ->setParameter('requestDateTo', $booking->getToDate())
     ->setParameter('asset', $booking->getAsset())
     ->getQuery()
     ->execute();

Now this worked perfectly fine in my dev environment, but a certain assertion (which I knew had to be right) did fail in test every time.

Long story short, after a lot of debugging I discovered that SQLite (via DQL) is treating a DateTime variable differently. This translates in SQL like:

select * where '2021-01-01 00:00:000000' > date_from;

When a database record exist in the database with type = datetype and value is '2021-01-01', this doesn't return a record in MariaDB, but it will in SQLite. It seems SQLite treats '2021-01-01 00:00:000000' > '2021-01-01' as true, while MariaDB treats this as false.

I eventually solved it by forcing the date parameters in the (SQLite) matching formats, which also works in MariaDB. Ended up with:

 return $this->createQueryBuilder('e')
     ->andWhere('e.asset = :asset')
     ->andWhere('(:requestDateTo > e.fromDate AND :requestDateFrom < e.toDate)')
     ->setParameter('requestDateFrom', $booking->getFromDate()->format('Y-m-d'))
     ->setParameter('requestDateTo', $booking->getToDate()->format('Y-m-d'))
     ->setParameter('asset', $booking->getAsset())
     ->getQuery()
     ->execute();

So, as using SQLite in-memory is a badass trick to quickly run databased involved tests, beware of the quirks that can come up when using a different database typology.