DEV Community

loading...
Cover image for Using SQLite in test, and why you should be carefull.

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

parijke profile image Paul Rijke ・2 min read

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();
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide