Enable Json1 Extension for sqlite3

Mmh, this seems to be a hard thing to do!

I did found the following link: https://stackoverflow.com/questions/39319280/python-sqlite-json1-load-extension#answer-54194333

This worked for me, but it's not really an easy task to do!

Unfortunately, this is not trivial. You can't use loadExtension() for Laravel's PDO connection.

For local development, you can use Homestead. It comes with the JSON1 extension enabled.

@BOBBYBOUWMANN - Thanks for your reply.

My Problem is, I don't know the requirements to enable json1 for sqlite3

I installed sqlite3 with homebrew but since homebrew 2 is out they don't provide the --with-json1 option anymore. And I can't figure out how to install/load it. Or is it already installed with my sqlite installation? When I run PRAGMA compile_options; in sqlite cli there is ENABLE_JSON1 under the output. Tbe stackoverflow link says "You can check which compile options have been used by connecting to a SQLite database and running the following query." Does that mean my php sqlite has already json1 enabled? Or is it only enabled for sqlite3 and needs to be loaded for PHP again?

But my laravel test has the output: "RuntimeException: This database engine does not support JSON contains operations."

As @staudenmeir said, there is no way to use the loadExtension method. So is there only the way to enable json1 through a sqlite SELECT load query?

@bobbybouwmann have you enabled it through an additional SELECT query?

Thanks for all your answers! :)

Just wondering because everybody uses sqlite3 with in-memory database for testing.

But when it comes to JSON queries in my app, my tests doesn't work anymore :/ Should I go then for mysql for my tests? But this increases the test speed drastically.

You can only enable the JSON1 extension by including it when compiling SQLite.

You should always run tests on the database from your production environment. Otherwise, passing tests don't really ensure that your application is working correctly. MySQL and SQLite differ in many areas.

@STAUDENMEIR - I used Homebrew to install sqlite on my mac OS. The formula for sqlite includes:

def install
    ...
    ENV.append "CPPFLAGS", "-DSQLITE_ENABLE_JSON1=1"

https://github.com/Homebrew/homebrew-core/blob/master/Formula/sqlite.rb

So I can assume that json1 extension was included, when sqlite was installed for my machine? (especially because PRAGMA compile_options; outputs: ENABLE_JSON1)

sqlite has had the --with-json1 option but since homebrew 2, there is no more options support.

You should always run tests on the database from your production environment. Otherwise, passing tests don't really ensure that your application is working correctly. MySQL and SQLite differ in many areas..

-- @staudenmeir

We are planning to use mysql in our CI integration tests and before we push our code to the branch. But for quick assertions it's more comfortable to use in memory sqlite because the migrations take to long for mysql.

Furthermore our team uses Laravel valet (mac OS) or laradock (linux). So homestead is currently no option for us. Is there a way to find out how homestead enables it?

Judging from the code, it doesn't look like Homestead is actively enabling the JSON1 extension: https://github.com/laravel/settler/blob/master/scripts/provision.sh#L268

@STAUDENMEIR - Thanks for your reply. Ok then I'll try out to compile it by myself and enable it with an sql query.

I'll post an answer, when I have found out how it works. Thanks all :)

@chrissfr Any luck with recompiling sqlite with json1? I could not find any reference on how to enable JSON1 either.

No sry, haven't tried yet. For sqlite I skip the json tests and run them afterwards with a mysql connection. But I would like to use the json1 extension. But I could not find any reference how to do this either :/