PHPackages                             aaron-lin/json-schema-sql-builder - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. aaron-lin/json-schema-sql-builder

ActiveLibrary[Database & ORM](/categories/database)

aaron-lin/json-schema-sql-builder
=================================

A PHP library to build SQL query from JSON Schema

v1.0.2(1y ago)423proprietaryPHPPHP >=7.4

Since Jul 10Pushed 1y ago1 watchersCompare

[ Source](https://github.com/Aaron2963/json_schema_sql_builder)[ Packagist](https://packagist.org/packages/aaron-lin/json-schema-sql-builder)[ RSS](/packages/aaron-lin-json-schema-sql-builder/feed)WikiDiscussions main Synced 1mo ago

READMEChangelog (3)DependenciesVersions (4)Used By (0)

JSON Schema SQL Builder
=======================

[](#json-schema-sql-builder)

This is a simple tool to generate SQL DDL statements from a JSON Schema.

Install
-------

[](#install)

```
$ composer require aaron-lin/json-schema-sql-builder
```

Usage
-----

[](#usage)

### Build SELECT Statement from JSON Schema

[](#build-select-statement-from-json-schema)

For example, the JSON schema is as following:

```
{
  "$schema": "http://json-schema.org/draft-06/schema#",
  "@table": "products",
  "@id": "id",
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "name": {
      "type": "string"
    },
    "_type_name": {
      "type": "string"
    },
    "_weight": {
      "type": "object",
      "properties": {
        "weight": {
          "type": "number"
        },
        "weight_unit": {
          "type": "string",
          "enum": [
            "g",
            "kg"
          ]
        }
      }
    },
    "_bids": {
      "type": "array",
      "@table": "bids",
      "@joinId": "product_id",
      "@id": "id",
      "@orderBy": "time DESC",
      "items": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "price": {
            "type": "number"
          },
          "time": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    }
  }
}
```

Use the following code to run the SQL SELECT query:

```
use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\SelectSQLBuilder;

$SchemaURI = 'path/to/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
  echo $e->getMessage();
  exit;
}
Storage::AddSelectExpression($SchemaURI . '#/properties/_type_name', '(SELECT name FROM product_types WHERE product_types.id = products.type_id LIMIT 1)');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new SelectSQLBuilder($SchemaURI, $DB);
$Builder->SetSelectExpressions()
  ->AddWhere("products.keywords like :keywords", ['keywords' => '%apple%'])
  ->AddOrderBy('products.price', 'DESC')
  ->SetLimit(10)
  ->SetOffset(0);
$Result = $Builder->Execute();
echo json_encode($Result, JSON_PRETTY_PRINT);
// [
//   {
//     "id": "1",
//     "name": "Apple",
//     "_type_name": "Fruit",
//     "_weight": {
//       "weight": "100.00",
//       "weight_unit": "g"
//     },
//     "_bids": [
//       {
//         "id": "7",
//         "price": "400",
//         "time": "2018-01-04 00:00:00"
//       },
//       {
//         "id": "5",
//         "price": "300",
//         "time": "2018-01-03 00:00:00"
//       },
//       {
//         "id": "3",
//         "price": "200",
//         "time": "2018-01-02 00:00:00"
//       },
//       {
//         "id": "1",
//         "price": "100",
//         "time": "2018-01-01 00:00:00"
//       }
//     ]
//   }
// ]
```

As you can see, after initializing JSON schema storage, you can add select expressions to indirect corresponding properties (direct properties are automatically added to select expressions as `table_name.property_key`). Then you can build the SQL SELECT statement with `SelectSQLBuilder::Build` method. For more information, please refer to [the test script](test/select-sql-builder.php), you can also find database schema and data in [the test directory](test/).

### Build INSERT/UPDATE Statement from JSON Schema

[](#build-insertupdate-statement-from-json-schema)

For example, the JSON schema is as following:

```
{
  "$schema": "http://json-schema.org/draft-06/schema#",
  "@table": "products",
  "@id": "id",
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "name": {
      "type": "string"
    },
    "_type_name": {
      "type": "string",
      "readonly": true
    },
    "_weight": {
      "type": "object",
      "properties": {
        "weight": {
          "type": "number"
        },
        "weight_unit": {
          "type": "string",
          "enum": [
            "g",
            "kg"
          ]
        }
      }
    },
    "_bids": {
      "type": "array",
      "@table": "bids",
      "@joinId": "product_id",
      "@id": "id",
      "@orderBy": "time DESC",
      "items": {
        "type": "object",
        "properties": {
          "id": {
            "type": "string"
          },
          "price": {
            "type": "number"
          },
          "time": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    }
  }
}
```

And the data is as following:

```
{
  "id": "1",
  "name": "Apple",
  "_type_name": "Fruit",
  "_weight": {
    "weight": "100.00",
    "weight_unit": "g"
  },
  "_bids": [
    {
      "id": "7",
      "price": "400",
      "time": "2018-01-04 00:00:00"
    },
    {
      "id": "5",
      "price": "300",
      "time": "2018-01-03 00:00:00"
    },
    {
      "id": "3",
      "price": "200",
      "time": "2018-01-02 00:00:00"
    },
    {
      "id": "1",
      "price": "100",
      "time": "2018-01-01 00:00:00"
    }
  ]
}
```

Use the following code to run the SQL INSERT/UPDATE query:

```
use Lin\JsonSchemaSqlBuilder\Storage;
use Lin\JsonSchemaSqlBuilder\UpsertSQLBuilder;

$SchemaURI = __DIR__ . '/schema.json#';
$DSN = 'mysql:host=db;dbname=test;charset=utf8mb4';
$DB = new \PDO($DSN, 'test', 'test');

try {
  Storage::SetSchemaFromURI($SchemaURI);
} catch (\Exception $e) {
    echo $e->getMessage();
  exit;
}

Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight', 'products.weight');
Storage::AddSelectExpression($SchemaURI . '#/properties/_weight/properties/weight_unit', 'products.weight_unit');
$Builder = new UpsertSQLBuilder($SchemaURI, $DB, $Data);
$Builder->SetAssignmentList();
$ResultCount = $Builder->Execute();
echo $ResultCount;
// for every new rows added, the return value is 1,
// for every existing rows updated, the return value is 2.
// data contains 1 row in products, 4 rows in bids,
// therefore, if data are all new rows, the return value is 5,
// or data are all existing rows, the return value is 10
```

For every new rows added, the return value of `UpsertSQLBuilder::Execute` is `1`, for every existing rows updated, the return value is `2`. For more information, please refer to [the test script](test/upsert-sql-builder.php), you can also find database schema and data in [the test directory](test/).

###  Health Score

28

—

LowBetter than 54% of packages

Maintenance38

Infrequent updates — may be unmaintained

Popularity11

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity46

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~246 days

Total

3

Last Release

551d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/3b46e3827bb5ee9e9b0eb05da5f21afa64dabc488035ab65d4970ce00b148313?d=identicon)[neotgrr](/maintainers/neotgrr)

---

Top Contributors

[![Aaron2963](https://avatars.githubusercontent.com/u/33084610?v=4)](https://github.com/Aaron2963 "Aaron2963 (17 commits)")

### Embed Badge

![Health badge](/badges/aaron-lin-json-schema-sql-builder/health.svg)

```
[![Health](https://phpackages.com/badges/aaron-lin-json-schema-sql-builder/health.svg)](https://phpackages.com/packages/aaron-lin-json-schema-sql-builder)
```

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)