JSON in SQLite using PHP

Before you begin

In this tutorial, we’ll learn how to store a JSON in a SQLite table with JSON data type column using PHP. A GNU Linux/Mac OS machine with cURL, PHP 7.x and SQLite version > 3.3 will be required to follow this tutorial. Finally, make sure you have the PHP package manager composer installed to follow this post:

# Composer install
$ curl -sS https://getcomposer.org/installer | \
  php -- --install-dir=/usr/local/bin --filename=composer
$ composer version -V
> Composer version 1.9.1 2019-11-01 17:20:17

Retrieve data from an API using PHP cURL

Retrieves a list of countries from the World Bank API using cURL PHP library (First 100 countries).

$ php -a

php > $ch = curl_init();
php > curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
php > curl_setopt($ch, CURLOPT_URL,"http://api.worldbank.org/countries?format=json&per_page=100");
php > $result=curl_exec($ch);
php > $countries = json_decode($result)[1];
php > curl_close($ch);
php > var_dump($countries);
array(100) {
  [0]=>
  object(stdClass)#501 (10) {
    ["id"]=>
    string(3) "ABW"
    ["iso2Code"]=>
    string(2) "AW"
    ["name"]=>
    string(5) "Aruba"
    ["region"]=>
    object(stdClass)#500 (2) {
      ["id"]=>
      string(3) "LCN"
      ["value"]=>
      string(26) "Latin America & Caribbean "
    }
    ["adminregion"]=>
    object(stdClass)#499 (2) {
      ["id"]=>
      string(0) ""
      ["value"]=>
      string(0) ""
    }
    ["incomeLevel"]=>
    object(stdClass)#498 (2) {
      ["id"]=>
      string(3) "HIC"
      ["value"]=>
      string(11) "High income"
    }
    ["lendingType"]=>
    object(stdClass)#492 (2) {
      ["id"]=>
      string(3) "LNX"
      ["value"]=>
      string(14) "Not classified"
    }
    ["capitalCity"]=>
    string(10) "Oranjestad"
    ["longitude"]=>
    string(8) "-70.0167"
    ["latitude"]=>
    string(7) "12.5167"
  }
...

Create a SQLite table using PHP

Using PHP and it’s extension for SQLite we are creating a connection to the test.db database. Make sure you have the SQLite extension enabled in your php.ini extension=sqlite3 (without a semicolon at the beginning of the line). You can also use phpinfo(); inside of a PHP file or run php -m | grep sqlite to ensure the sqlite3 module is enabled. If sqlite doesn’t appear in the list you might need to install the OS package php-pdo as well.

php > var_dump(SQLite3::version());
array(2) {
  ["versionString"]=>
  string(6) "3.28.0"
  ["versionNumber"]=>
  int(3028000)
}

The following PHP lines we’ll allow us to create a SQL table countries with a SQLite-JSON based field called data:

$ php -a

php > $db = new SQLite3('test.db');
php > $db->exec("CREATE TABLE IF NOT EXISTS countries (id varchar(3), data json)");

Insert values into a SQLite table with a JSON column using PHP

Loops array countries and inserts them one by one using PHP foreach statement:

$ php -a

php > $db = new SQLite3('test.db');
php >
php > foreach ($countries as $country) {
php {  $stm = $db->prepare("insert into countries values (?, ?)");
php {  $stm->bindValue(1, $country->id, SQLITE3_TEXT);
php {  $stm->bindValue(2, json_encode($country), SQLITE3_TEXT);
php {  $res = $stm->execute();
php { }
php >

Finally, we can check if the database has values on it by querying the table countries. In the following example we are using PHP to query the first result and pretty-printing the JSON value:

$ composer require camspiers/json-pretty
$ php -a

php > require_once __DIR__ . '/vendor/autoload.php';
php > $jsonPretty = new Camspiers\JsonPretty\JsonPretty;
php > $db = new SQLite3('test.db');
php > echo $jsonPretty->prettify($db->querySingle('SELECT data from countries'));
{
	"id":  "ABW",
	 "iso2Code":  "AW",
	 "name":  "Aruba",
	 "region":  {
		"id":  "LCN",
		 "value":  "Latin America & Caribbean "
	},
	 "adminregion":  {
		"id":  "",
		 "value":  ""
	},
	 "incomeLevel":  {
		"id":  "HIC",
		 "value":  "High income"
	},
	 "lendingType":  {
		"id":  "LNX",
		 "value":  "Not classified"
	},
	 "capitalCity":  "Oranjestad",
	 "longitude":  "-70.0167",
	 "latitude":  "12.5167"
}

Query JSON values in SQLite

Finally, we can query the countries table. For example, getting a list of ISO country codes by accessing the attribute iso2Code on the JSON type data field:

$ apt-get install -y sqlite3 # or equivalent in your OS, if sqlite3 needs to be installed
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57
$ sqlite3 test.db
sqlite> select json_extract(data, '$.iso2Code') from countries;
AW
AF
A9
AO
AL
AD
L5
1A
AE
AR
...

Selecting country names in SQL with values between ‘Eb’ and ‘Et’ inside of the JSON data field:

$ sqlite3 test.db
sqlite> select json_extract(data, '$.name') from countries
where json_extract(data, '$.name') between 'Eb' and 'Et'
order by json_extract(data, '$.name') asc;
Ecuador
Egypt, Arab Rep.
Eritrea
Estonia

Or fetching randomly one country name:

$ sqlite3 test.db
sqlite> select json_extract(data, '$.name') from countries order by random() limit 1;
Andorra

Although we’ve used PHP in this post, there are lot of other programming languages that can be used to easily query JSON in SQLite like Pyhton.

Finally, you should definitely take a look at these books to fuel your SQLite knowledge: