Single file PHP script that adds a REST API to a MySQL/MariaDB, PostgreSQL, SQL Server or SQLite database. Howto: Upload " NB: This is the TreeQL reference implementation in PHP. This is a single
file application! Upload " For local development you may run PHP's built-in web server: Test the script by opening the following URL: Don't forget to modify the configuration at the bottom of the file. Alternatively you can integrate this project into the web framework of your choice, see:
In these integrations Composer is used to load this project as a dependency. For people that don't use composer, the file " ConfigurationEdit the following lines in the bottom of the file "
These are all the configuration options and their default value between brackets:
All configuration options are also available as environment variables. Write the config option with capitals, a "PHP_CRUD_API_" prefix and underscores for word breakes, so for instance:
The environment variables take precedence over the PHP configuration. LimitationsThese limitation and constrains apply:
FeaturesThe following features are supported:
Related projects and portsRelated projects:
There are also ports of this script in:
There are also proof-of-concept ports of this script that only support basic REST CRUD functionality in: PHP, Java, Go, C# .net core, Node.js and Python. CompilationYou can install all dependencies of this project using the following command: You can compile all files into a single " NB: The install script will patch the dependencies in the vendor directory for PHP 7.0 compatibility. DevelopmentYou can access the non-compiled code at the URL:
The non-compiled code resides in the " Updating dependenciesYou can update all dependencies of this project using the following command: This script will install and run Composer to update the dependencies. NB: The update script will patch the dependencies in the vendor directory for PHP 7.0 compatibility. TreeQL, a pragmatic GraphQLTreeQL allows you to create a "tree" of JSON objects based on your SQL database structure (relations) and your query. It is loosely based on the REST standard and also inspired by json:api. CRUD + ListThe example posts table has only a a few fields:
The CRUD + List operations below act on this table. CreateIf you want to create a record the request can be written in URL format as: You have to send a body containing:
And it will return the value of the primary key of the newly created record: ReadTo read a record from this table the request can be written in URL format as: Where "1" is the value of the primary key of the record that you want to read. It will return:
On read operations you may apply joins. UpdateTo update a record in this table the request can be written in URL format as: Where "1" is the value of the primary key of the record that you want to update. Send as a body:
This adjusts the title of the post. And the return value is the number of rows that are set: DeleteIf you want to delete a record from this table the request can be written in URL format as: And it will return the number of deleted rows: ListTo list records from this table the request can be written in URL format as: It will return:
On list operations you may apply filters and joins. FiltersFilters provide search functionality, on list calls, using the "filter" parameter. You need to specify the column name, a comma, the match type, another commma and the value you want to filter on. These are supported match types:
You can negate all filters by prepending a "n" character, so that "eq" becomes "neq". Examples of filter usage are:
Output:
In the next section we dive deeper into how you can apply multiple filters on a single list call. Multiple filtersFilters can be a by applied by repeating the "filter" parameter in the URL. For example the following URL:
will request all categories "where id > 1 and id < 3". If you wanted "where id = 2 or id = 4" you should write:
As you see we added a number to the "filter" parameter to indicate that "OR" instead of "AND" should be applied. Note that you can also repeat "filter1" and create an "AND" within an "OR". Since you can also go one level deeper by adding a letter (a-f) you can create almost any reasonably complex condition tree. NB: You can only filter on the requested table (not on it's included tables) and filters are only applied on list calls. Column selectionBy default all columns are selected. With the "include" parameter you can select specific columns. You may use a dot to separate the table name from the column name. Multiple columns should be comma separated. An asterisk ("*") may be used as a wildcard to indicate "all columns". Similar to "include" you may use the "exclude" parameter to remove certain columns:
Output: NB: Columns that are used to include related entities are automatically added and cannot be left out of the output. OrderingWith the "order" parameter you can sort. By default the sort is in ascending order, but by specifying "desc" this can be reversed:
Output:
NB: You may sort on multiple fields by using multiple "order" parameters. You can not order on "joined" columns. Limit sizeThe "size" parameter limits the number of returned records. This can be used for top N lists together with the "order" parameter (use descending order).
Output:
NB: If you also want to know to the total number of records you may want to use the "page" parameter. PaginationThe "page" parameter holds the requested page. The default page size is 20, but can be adjusted (e.g. to 50).
Output:
The element "results" holds to total number of records in the table, which would be returned if no pagination would be used. NB: Since pages that are not ordered cannot be paginated, pages will be ordered by primary key. JoinsLet's say that you have a posts table that has comments (made by users) and the posts can have tags.
When you want to list posts with their comments users and tags you can ask for two "tree" paths:
These paths have the same root and this request can be written in URL format as:
Here you are allowed to leave out the intermediate table that binds posts to tags. In this example you see all three table relation types (hasMany, belongsTo and hasAndBelongsToMany) in effect:
This may lead to the following JSON data:
You see that the "belongsTo" relationships are detected and the foreign key value is replaced by the referenced object. In case of "hasMany" and "hasAndBelongsToMany" the table name is used a new property on the object. Batch operationsWhen you want to create, read, update or delete you may specify multiple primary key values in the URL. You also need to send an array instead of an object in the request body for create and update. To read a record from this table the request can be written in URL format as: The result may be:
Similarly when you want to do a batch update the request in URL format is written as: Where "1" and "2" are the values of the primary keys of the records that you want to update. The body should contain the same number of objects as there are primary keys in the URL:
This adjusts the titles of the posts. And the return values are the number of rows that are set: Which means that there were two update operations and each of them had set one row. Batch operations use database transactions, so they either all succeed or all fail (successful ones get roled back). If they fail the body will contain the list of error documents. In the following response the first operation succeeded and the second operation of the batch failed due to an integrity violation:
The response status code will always be 424 (failed dependency) in case of any failure of one of the batch operations. Spatial supportFor spatial support there is an extra set of filters that can be applied on geometry columns and that starting with an "s":
These filters are based on OGC standards and so is the WKT specification in which the geometry columns are represented. Note that the SRID that is assumed when converting from WKT to geometry is specified by the config variable GeoJSONThe GeoJSON support is a read-only view on the tables and records in GeoJSON format. These requests are supported:
The "
The GeoJSON functionality is enabled by default, but can be disabled using the "controllers" configuration. Mapping names for legacy systemsTo support creating an API for (a part of) a legacy system (such as Wordpress) you may want to map the table and column names as can not improve them without changing the software, while the names may need some improvement for consistency. The config allows you to rename tables and columns with a comma separated list of mappings that are split with an equal sign, like this:
This specific example will expose the " NB: Since these two mappings overlap the first (less specific) mapping may be omitted. MiddlewareYou can enable the following middleware using the "middlewares" config parameter:
The "middlewares" config parameter is a comma separated list of enabled middlewares. You can tune the middleware behavior using middleware specific configuration parameters:
If you don't specify these parameters in the configuration, then the default values (between brackets) are used. In the sections below you find more information on the built-in middleware. AuthenticationCurrently there are five types of authentication supported. They all store the authenticated user in the
Below you find more information on each of the authentication types. API key authenticationAPI key authentication works by sending an API key in a request header. The header name defaults to "X-API-Key" and can be configured using the 'apiKeyAuth.header' configuration parameter. Valid API keys must be configured using the 'apiKeyAuth.keys' configuration parameter (comma seperated list).
The authenticated API key will be stored in the Note that the API key authentication does not require or use session cookies. API key database authenticationAPI key database authentication works by sending an API key in a request header "X-API-Key" (the name is configurable). Valid API keys are read from the database from the column "api_key" of the "users" table (both names are configurable).
The authenticated user (with all it's properties) will be stored in the Note that the API key database authentication does not require or use session cookies. Database authenticationThe database authentication middleware defines five new routes:
A user can be logged in by sending it's username and password to the login endpoint (in JSON format). The authenticated user (with all it's properties) will be stored in the It is IMPORTANT to restrict access to the users table using the 'authorization' middleware, otherwise all users can freely add, modify or delete any account! The minimal configuration is shown below:
Note that this middleware uses session cookies and stores the logged in state on the server. Login using views with joined table For login operations, it is possible to use a view as the usersTable. Such view can return a filtered result from the users table, e.g., where active = true or it may also return a result multiple tables thru a table join. At a minimum, the view should include the username and password. However, views with joined tables are not insertable (see issue 907 ). As a workaround, use the property loginTable to set a different reference table for login. The usersTable will still be set to the normal, insertable users table. Basic authenticationThe Basic type supports a file (by default '.htpasswd') that holds the users and their (hashed) passwords separated by a colon (':'). When the passwords are entered in plain text they will be automatically hashed. The authenticated username will be stored in the
This example sends the string "username1:password1". JWT authenticationThe JWT type requires another (SSO/Identity) server to sign a token that contains claims. Both servers share a secret so that they can either sign or verify that the signature is valid. Claims
are stored in the
This example sends the signed claims:
NB: The JWT implementation only supports the RSA and HMAC based algorithms. Configure and test JWT authentication with Auth0First you need to create an account on Auth0. Once logged in, you have to create an application (its type does not matter). Collect the Then you have to configure the To test your integration, you can copy the auth0/vanilla.html file. Be sure to fill these three variables:
⚠️ If you don't fill the audience parameter, it will not work because you won't get a valid JWT. You can also change the More info Configure and test JWT authentication with FirebaseFirst you need to create a Firebase project on the Firebase console. Add a web application to this project and grab the code snippet for later use. Then you have to configure the a. Log a user in to your Firebase-based app, get an authentication token for that user b. Go to https://jwt.io/ and
paste the token in the decoding field c. Read the decoded header information from the token, it will give you the correct Here is an example of what it should look like in the configuration:
Notes:
To test your integration, you can copy the firebase/vanilla.html file and the firebase/vanilla-success.html file, used as a "success" page and to display the API result. Replace, in both files, the Firebase configuration ( You can also change
the More info Authorizing operationsThe Authorization model acts on "operations". The most important ones are listed here:
The " For endpoints that start with " Authorizing tables, columns and recordsBy default all tables, columns and paths are accessible. If you want to restrict access to some tables you may add the 'authorization' middleware and define a 'authorization.tableHandler' function that returns 'false' for these tables.
The above example will restrict access to the table 'license_keys' for all operations.
The above example will restrict access to the 'password' field of the 'users' table for all operations.
The above example will disallow access to user records where the username is 'admin'. This construct adds a filter to every executed query.
The above example will disabled the NB: You need to handle the creation of invalid records with a validation (or sanitation) handler. SQL GRANT authorizationYou can alternatively use database permissons (SQL GRANT statements) to define the authorization model. In this case you should not use the "authorization" middleware, but you do need to use the "reconnect" middleware. The handlers of the "reconnect" middleware allow you to specify the correct username and password, like this:
This will make the API connect to the database specifying "mevdschee" as the username and "secret123" as the password. The OpenAPI specification is less specific on allowed and disallowed operations when you are using database permissions, as the permissions are not read in the reflection step. NB: You may want to retrieve the username and password from the session (the "$_SESSION" variable). Sanitizing inputBy default all input is accepted and sent to the database. If you want to strip (certain) HTML tags before storing you may add the 'sanitation' middleware and define a 'sanitation.handler' function that returns the adjusted value.
The above example will strip all HTML tags from strings in the input. Type sanitationIf you enable the 'sanitation' middleware, then you (automatically) also enable type sanitation. When this is enabled you may:
You may use the config settings "
Here we enable the type sanitation for date and timestamp fields in the posts and comments tables. Validating inputBy default all input is accepted and sent to the database. If you want to validate the input in a custom way, you may add the 'validation' middleware and define a 'validation.handler' function that returns a boolean indicating whether or not the value is valid.
When you edit a comment with id 4 using: And you send as a body: Then the server will return a '422' HTTP status code and nice error message:
You can parse this output to make form fields show up with a red border and their appropriate error message. Type validationsIf you enable the 'validation' middleware, then you (automatically) also enable type validation. This includes the following error messages:
You may use the config settings "
Here we enable the type validation for date and timestamp fields in the posts and comments tables. NB: Types that are enabled will be checked for null values when the column is non-nullable. Multi-tenancy supportTwo forms of multi-tenancy are supported:
Below is an explanation of the corresponding middlewares. Multi-tenancy middlewareYou may use the "multiTenancy" middleware when you have a single multi-tenant database. If your tenants are identified by the "customer_id" column, then you can use the following handler:
This construct adds a filter requiring "customer_id" to be "12" to every operation (except for "create"). It also sets the column "customer_id" on "create" to "12" and removes the column from any other write operation. NB: You may want to retrieve the customer id from the session (the "$_SESSION" variable). Reconnect middlewareYou may use the "reconnect" middleware when you have a separate database for each tenant. If the tenant has it's own database named "customer_12", then you can use the following handler:
This will make the API reconnect to the database specifying "customer_12" as the database name. If you don't want to use the same credentials, then you should also implement the "usernameHandler" and "passwordHandler". NB: You may want to retrieve the database name from the session (the "$_SESSION" variable). Prevent database scrapingYou may use the "joinLimits" and "pageLimits" middleware to prevent database scraping. The "joinLimits" middleware limits the table depth, number of tables and number of records returned in a join operation. If you want to allow 5 direct direct joins with a maximum of 25 records each, you can specify:
The "pageLimits" middleware limits the page number and the number records returned from a list operation. If you want to allow no more than 10 pages with a maximum of 25 records each, you can specify:
NB: The maximum number of records is also applied when there is no page number specified in the request. Search all text fieldsYou may use the "textSearch" middleware to simplify (wildcard) text searches when listing records. It allows you to specify a "search" parameter using:
It will return all records from "posts" that contain "Hello" in one of their text (typed) fields:
The example searches the fields "title" or "content" for the substring "Hello". Customization handlersYou may use the "customization" middleware to modify request and response and implement any other functionality.
The above example will add a header "X-Time-Taken" with the number of seconds the API call has taken. JSON encoding optionsYou can change the way the JSON is encoded by setting the configuration parameter "jsonOptions".
The above example will set JSON options to 128+256+64 = 448, as per the list of options below:
Source: PHP's JSON constants documentation JSON middlewareYou may use the "json" middleware to read/write JSON strings as JSON objects and arrays. JSON strings are automatically detected when the "json" middleware is enabled. You may limit the scanning of by specifying specific table and/or field names:
This will change the output of: Without "json" middleware the output will be:
With "json" middleware the output will be:
This also applies when creating or modifying JSON string fields (also when using batch operations). Note that JSON string fields cannot be partially updated and that this middleware is disabled by default. You can enable the "json" middleware using the "middlewares" configuration setting. XML middlewareYou may use the "xml" middleware to translate input and output from JSON to XML. This request: Outputs (when "pretty printed"):
While (note the "format" query parameter):
Outputs:
This functionality is disabled by default and must be enabled using the "middlewares" configuration setting. File uploadsFile uploads are supported through the FileReader API, check out the example. OpenAPI specificationOn the "/openapi" end-point the OpenAPI 3.0 (formerly called "Swagger") specification is served. It is a machine readable instant documentation of your API. To learn more, check out these links:
CacheThere are 4 cache engines that can be configured by the "cacheType" config parameter:
You can install the dependencies for the last three engines by running:
The default engine has no dependencies and will use temporary files in the system "temp" path. You may use the "cachePath" config parameter to specify the file system path for the temporary files or in case that you use a non-default "cacheType" the hostname (optionally with port) of the cache server. TypesThese are the supported types with their length, category, JSON type and format:
Note that geometry is a non-jdbc type and thus has limited support. Data types in JavaScriptJavascript and Javascript object notation (JSON) are not very well suited for reading database records. Decimal, date/time, binary and geometry types must be represented as strings in JSON (binary is base64 encoded, geometries are in WKT format). Below are two more serious issues described. 64 bit integersJavaScript does not support 64 bit integers. All numbers are stored as 64 bit floating point values. The mantissa of a 64 bit floating point number is only 53 bit and that is why all integer numbers bigger than 53 bit may cause problems in JavaScript. Inf and NaN floatsThe valid floating point values 'Infinite' (calculated with '1/0') and 'Not a Number' (calculated with '0/0') cannot be expressed in JSON, as they are not supported by the JSON specification. When these values are stored in a database then you cannot read them as this script outputs database records as JSON. ErrorsThe following errors may be reported:
The following JSON structure is used:
NB: Any non-error response will have status: 200 OK StatusTo connect to your monitoring there is a 'ping' endpoint: And this should return status 200 and as data: These can be used to measure the time (in microseconds) to connect and read data from the database and the cache. Custom controllerYou can add your own custom REST API endpoints by writing your own custom controller class. The class must provide a constructor that accepts five parameters. With these parameters you can register your own endpoint to the existing router. This endpoint may use the database and/or the reflection class of the database. Here is an example of a custom controller class:
And then you may register your custom controller class in the config object like this:
The TestsI am testing mainly on Ubuntu and I have the following test setups:
This covers not all environments (yet), so please notify me of failing tests and report your environment. I will try to cover most relevant setups in the "docker" folder of the project. RunningTo run the functional tests locally you may run the following commands:
This runs the functional tests from the "tests" directory. It uses the database dumps (fixtures) and database configuration (config) from the corresponding subdirectories. Nginx config example
Docker testsInstall docker using the following commands and then logout and login for the changes to take effect:
To run the docker tests run "build_all.sh" and "run_all.sh" from the docker directory. The output should be:
The above test run (including starting up the databases) takes less than 5 minutes on my slow laptop.
As you can see the "run.sh" script gives you access to a prompt in the chosen docker environment. In this environment the local files are mounted. This allows for easy debugging on different environments. You may type "exit" when you are done. Docker imageThere is a https://hub.docker.com/r/mevdschee/php-crud-api It will be automatically build on every release. The "latest" tag points to the last release. The docker image accepts the environment variable parameters from the configuration. Docker composeThis repository also contains a
This will setup a database (MySQL) and a webserver (Apache) and runs the application using the blog example data used in the tests. Test the script (running in the container) by opening the following URL:
Enjoy! |