Laravel tests fails under SQLite if uses MySQL function in query

Question

In my Laravel project I use some MySQL based SQL functions in my queries, like this:

Post::whereRaw('publish_at < NOW()');

SELECT * FROM posts WHERE publish_at < NOW();

Or this:

Post::whereRaw('TIMESTAMP(publish_date, publish_time) < NOW()');

SELECT * FROM posts WHERE TIMESTAMP(publish_date, publish_time) < NOW();

These queries works fine with MySQL db, but if I try to test them on SQLite in-memory database, they fail because of:

no such function: NOW (SQL: ...)

I want to keep the benefits of MySQL functions, but I don't want to drop these tests, nor create a different functions to use it in tests.

Maybe is there any less known Eloquent functions to solve these issues?

Any idea how can I make it work on both databases?

Answer 1

CURRENT_TIMESTAMP is a special syntax that conforms to standard SQL. It's like a constant (notice the lack of () that you would expect if calling a function).

It is supported by SQLite:

SQLite version 3.32.3 2020-06-18 14:16:19
sqlite> select current_timestamp;
2021-10-17 17:16:02

And by MySQL:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.7.34-37-log Homebrew

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2021-10-17 17:16:08 |
+---------------------+

---

Re your comment:

There are bound to be some functions that are not portable between different SQL implementations. "Portable SQL" is a myth. You always have a variety of syntax features or builtin functions in each SQL implementation that are not supported in other brands.

The best I can suggest — which is what I did when I worked on the Zend::Db component for Zend Framework — is to use the Adapter Pattern. Create a subclass for each of your intended databases you want to support, and implement each function differently in each subclass.

Eloquent allows you to develop more meaningful "builder" methods to make your model-based queries more idiomatic. https://dev.to/rocksheep/cleaner-models-with-laravel-eloquent-builders-12h4

You would develop separate implementations of these build methods for each brand of database.