PHP-CRUD-API based on mevdschee/php-crud-api with ENV support for the variables
718
Single file PHP script that adds a REST API to a MySQL/MariaDB, PostgreSQL, SQL Server or SQLite database.
NB: This is the TreeQL reference implementation in PHP.
Related 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.
This is a single file application! Upload "api.php" somewhere and enjoy!
For local development you may run PHP's built-in web server:
php -S localhost:8080
Test the script by opening the following URL:
http://localhost:8080/api.php/records/posts/1
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 "api.include.php" is provided. This file contains everything
from "api.php" except the configuration from "src/index.php" and can be used by PHP's "include".
Edit the following lines in the bottom of the file "api.php":
$config = new Config([
'username' => 'xxx',
'password' => 'xxx',
'database' => 'xxx',
]);
These are all the configuration options and their default value between brackets:
mysql, pgsql, sqlsrv or sqlite (mysql)localhost)cors)records,geojson,openapi,status){"info":{"title":"PHP-CRUD-API","version":"1.0.0"}})TempFile, Redis, Memcache, Memcached or NoCache (TempFile)10)false)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.
These limitation and constrains apply:
The following features are supported:
You can install all dependencies of this project using the following command:
php install.php
You can compile all files into a single "api.php" file using:
php build.php
NB: The install script will patch the dependencies in the vendor directory for PHP 7.0 compatibility.
You can access the non-compiled code at the URL:
http://localhost:8080/src/records/posts/1
The non-compiled code resides in the "src" and "vendor" directories. The "vendor" directory contains the dependencies.
You can update all dependencies of this project using the following command:
php update.php
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 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.
The example posts table has only a a few fields:
posts
=======
id
title
content
created
The CRUD + List operations below act on this table.
If you want to create a record the request can be written in URL format as:
POST /records/posts
You have to send a body containing:
{
"title": "Black is the new red",
"content": "This is the second post.",
"created": "2018-03-06T21:34:01Z"
}
And it will return the value of the primary key of the newly created record:
2
To read a record from this table the request can be written in URL format as:
GET /records/posts/1
Where "1" is the value of the primary key of the record that you want to read. It will return:
{
"id": 1
"title": "Hello world!",
"content": "Welcome to the first post.",
"created": "2018-03-05T20:12:56Z"
}
On read operations you may apply joins.
To update a record in this table the request can be written in URL format as:
PUT /records/posts/1
Where "1" is the value of the primary key of the record that you want to update. Send as a body:
{
"title": "Adjusted title!"
}
This adjusts the title of the post. And the return value is the number of rows that are set:
1
If you want to delete a record from this table the request can be written in URL format as:
DELETE /records/posts/1
And it will return the number of deleted rows:
1
To list records from this table the request can be written in URL format as:
GET /records/posts
It will return:
{
"records":[
{
"id": 1,
"title": "Hello world!",
"content": "Welcome to the first post.",
"created": "2018-03-05T20:12:56Z"
}
]
}
On list operations you may apply filters and joins.
Filters 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:
GET /records/categories?filter=name,eq,Internet
GET /records/categories?filter=name,sw,Inter
GET /records/categories?filter=id,le,1
GET /records/categories?filter=id,ngt,1
GET /records/categories?filter=id,bt,0,1
GET /records/categories?filter=id,in,0,1
Output:
{
"records":[
{
"id": 1
"name": "Internet"
}
]
}
In the next section we dive deeper into how you can apply multiple filters on a single list call.
Filters can be a by applied by repeating the "filter" parameter in the URL. For example the following URL:
GET /records/categories?filter=id,gt,1&filter=id,lt,3
will request all categories "where id > 1 and id < 3". If you wanted "where id = 2 or id = 4" you should write:
GET /records/categories?filter1=id,eq,2&filter2=id,eq,4
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.
By 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:
GET /records/categories/1?include=name
GET /records/categories/1?include=categories.name
GET /records/categories/1?exclude=categories.id
Output:
{
"name": "Internet"
}
NB: Columns that are used to include related entities are automatically added and cannot be left out of the output.
With the "order" parameter you can sort. By default the sort is in ascending order, but by specifying "desc" this can be reversed:
GET /records/categories?order=name,desc
GET /records/categories?order=id,desc&order=name
Output:
{
"records":[
{
"id": 3
"name": "Web development"
},
{
"id": 1
"name": "Internet"
}
]
}
NB: You may sort on multiple fields by using multiple "order" parameters. You can not order on "joined" columns.
The "size" parameter limits the number of returned records. This can be used for top N lists together with the "order" parameter (use descending order).
GET /records/categories?order=id,desc&size=1
Output:
{
"records":[
{
"id": 3
"name": "Web development"
}
]
}
NB: If you also want to know to the total number of records you may want to use the "page" parameter.
The "page" parameter holds the requested page. The default page size is 20, but can be adjusted (e.g. to 50).
GET /records/categories?order=id&page=1
GET /records/categories?order=id&page=1,50
Output:
{
"records":[
{
"id": 1
"name": "Internet"
},
{
"id": 3
"name": "Web development"
}
],
"results": 2
}
NB: Since pages that are not ordered cannot be paginated, pages will be ordered by primary key.
Let's say that you have a posts table that has comments (made by users) and the posts can have tags.
posts comments users post_tags tags
======= ======== ======= ========= =======
id id id id id
title post_id username post_id name
content user_id phone tag_id
created message
When you want to list posts with their comments users and tags you can ask for two "tree" paths:
posts -> comments -> users
posts -> post_tags -> tags
These paths have the same root and this request can be written in URL format as:
GET /records/posts?join=comments,users&join=tags
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:
{
"records":[
{
"id": 1,
"title": "Hello world!",
"content": "Welcome to the first post.",
"created": "2018-03-05T20:12:56Z",
"comments": [
{
id: 1,
post_id: 1,
user_id: {
id: 1,
username: "mevdschee",
phone: null,
},
message: "Hi!"
},
{
id: 2,
post_id: 1,
user_id: {
id: 1,
username: "mevdschee",
phone: null,
},
message: "Hi again!"
}
],
"tags": []
},
{
"id": 2,
"title": "Black is the new red",
"content": "This is the second post.",
"created": "2018-03-06T21:34:01Z",
"comments": [],
"tags": [
{
id: 1,
message: "Funny"
},
{
id: 2,
message: "Informational"
}
]
}
]
}
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.
When 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:
GET /records/posts/1,2
The result may be:
[
{
"id": 1,
"title": "Hello world!",
"content": "Welcome to the first post.",
"created": "2018-03-05T20:12:56Z"
},
{
"id": 2,
"title": "Black is the new red",
"content": "This is the second post.",
"created": "2018-03-06T21:34:01Z"
}
]
Similarly when you want to do a batch update the request in URL format is written as:
PUT /records/posts/1,2
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:
[
{
"title": "Adjusted title for ID 1"
},
{
"title": "Adjusted title for ID 2"
}
]
This adjusts the titles of the posts. And the return values are the number of rows that are set:
[1,1]
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:
[
{
"code": 0,
"message": "Success"
},
{
"code": 1010,
"message": "Data integrity violation"
}
]
The response status code will always be 424 (failed dependency) in case of any failure of one of the batch operations.
For 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.
The GeoJSON support is a read-only view on the tables and records in GeoJSON format. These requests are supported:
method path - operation - description
----------------------------------------------------------------------------------------
GET /geojson/{table} - list - lists records as a GeoJSON FeatureCollection
GET /geojson/{table}/{id} - read - reads a record by primary key as a GeoJSON Feature
The "/geojson" endpoint uses the "/records" endpoint internally and inherits all functionality, such as joins and filters.
It also supports a "geometry" parameter to indicate the name of the geometry column in case the table has more than one.
For map views it supports the "bbox" parameter in which you can specify upper-left and lower-right coordinates (comma separated).
The following Geometry types are supported by the GeoJSON implementation:
The GeoJSON functionality is enabled by default, but can be disabled using the "controllers" configuration.
You 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:
Content type
Image
Digest
Size
141.9 MB
Last updated
over 5 years ago
docker pull steelburn/php-crud-api