PHP Testing JSON Contains with SQLite

Question

My Web App uses Laravel & MySQL but for my tests I am using SQLite in memory.

This is my code that I use in my Controller:

$opportunities = DB::table('opportunities')
        ->whereRaw('JSON_CONTAINS(businesses, \'"' . $business . '"\')')
        ->get();

When testing this throws an exception because of how SQLite doesn't have a JSON_CONTAINS function. How can I get around this so that my tests pass and I don't have to make any massive changes to the structure? Does SQLite have a function for this or something along these lines?

Thanks

Don't use MySQL's JSON feature. Stick to standard SQL features that are in both MySQL and SQLite. — Barmar
– Barmar, Commented May 25, 2018 at 20:20
But it really makes little sense to use a different DBMS for testing than production. If your testing succeeds, you don't know that it will work in production. — Barmar
– Barmar, Commented May 25, 2018 at 20:21

MultiHunter · Accepted Answer · 2020-05-18 10:33:41Z

9

A bit more explanation for the accepted answer:

/**
 * Call this method in your test. 
 * @PHP 7.4+
 */
private function setupSqlite(): void
{
    DB::connection()->getPdo()->sqliteCreateFunction('JSON_CONTAINS', function ($json, $val, $path = null) {
        $array = json_decode($json, true, 512, JSON_THROW_ON_ERROR);
        // trim double quotes from around the value to match MySQL behaviour
        $val = trim($val, '"');
        // this will work for a single dimension JSON value, if more dimensions
        // something more sophisticated will be required
        // that is left as an exercise for the reader
        if ($path) {
            return $array[$path] == $val;
        }

        return in_array($val, $array, true);
    });

Then you can use this query, that works for both mysql/mariadb and sqlite:

$result = Product::whereRaw('JSON_CONTAINS(tags, \'"' . $tag . '"\')')->get();

In above example I was searching json column with simple array:

$column = ["example", "test", "simple"];

answered May 18, 2020 at 10:33

MultiHunter

1332 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

That's a useful addition to the question.

Watercayman Over a year ago

Good answer. Note, this doesn't seem to work with the built-in Laravel ->whereJsonContains('audience', NewsItem::PUBLIC), rather it seems to only be happy with the raw statement as @MultiHunter notes in the query example above.

Nick · Accepted Answer · 2018-05-26 13:58:38Z

6

You could emulate JSON_CONTAINS during testing using sqlite_create_function e.g.

function json_contains($json, $val) {
  $array = json_decode($json, true);
  // trim double quotes from around the value to match MySQL behaviour
  $val = trim($val, '"');
  // this will work for a single dimension JSON value, if more dimensions
  // something more sophisticated will be required
  // that is left as an exercise for the reader
  return in_array($val, $array);
}

sqlite_create_function(<your db handle>, 'JSON_CONTAINS', 'json_contains');

You may also want to emulate the optional third parameter of JSON_CONTAINS e.g.

function json_contains($json, $val, $path = null) {
  $array = json_decode($json, true);
  // trim double quotes from around the value to match MySQL behaviour
  $val = trim($val, '"');
  // this will work for a single dimension JSON value, if more dimensions
  // something more sophisticated will be required
  // that is left as an exercise for the reader
  if ($path)
    return $array[$path] == $val;
  else
    return in_array($val, $array);
}

edited May 26, 2018 at 13:58

answered May 26, 2018 at 2:01

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

Shiv Over a year ago

Brilliant!! You might want to add $val = trim($val, '"'); to the function so there is better compatibility because MySQL strips them automatically, PHP doesn't.

Nick Over a year ago

@Shiv good point, I will edit the answer. Also just noticed a typo in the second version if you use that, I had = instead of == in the $array[$path] comparison.

Cobolt · Accepted Answer · 2018-05-25 20:36:58Z

1

I gave up on running my test suite on SQLite because there are just too many differences between the implementations. The first issue was it's lack of support for enums, but when even simple things like ALTER statements were causing problems I changed to MySQL for testing too.

What I do now is specify the name of my testing db in phpunit.xml.

<phpunit>
    <php>
        <env name="DB_DATABASE" value="mydb_testing"/>
    </php>
</phpunit>

The down side of this is being required to create two databases to develop the app, but I need to be sure that the migrations will work on production, which uses MySQL and testing on SQLite doesn't prove that.

answered May 25, 2018 at 20:36

Cobolt

9532 gold badges11 silver badges25 bronze badges

Collectives™ on Stack Overflow

PHP Testing JSON Contains with SQLite

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related