If you need a quick and easy way to create a CMS (content management system) or a database backend for an app, then Restdb.io will amaze! Here are some features:
- super developer-friendly with crystal clear documentation
- affordable and even has a free plan
- example code for popular programming languages, including client side JavaScript and server side NodeJS, which makes it quick to get up and running
- webhooks whenever a record is created, updated or deleted
- codehooks to run custom code before and after a record is created, updated or deleted, e.g. for data normalizing and validation. codehooks also support scheduled tasks like cron jobs.
- auto-generate test data to seed a table
- ability to import data from a CSV file
- native and custom field validation
- non-technical users can log in and add/edit/delete records using intuitive forms and controls without seeing any developer features
- ability to upload and host images and files
Below is a tutorial on how to create a simple CRUD (Create, Read, Update, Delete) app using Restdb.io with a PHP backend.
1. Log in to Restdb.io account and create a table (collection)
After you log in, you’ll need to be in Developer Mode to create a table. When creating fields in the table, Restdb.io offers numerous field property options including native and custom field validation.
2. Add or import data
To populate the table, you can
- Add data field by field, row by row
- Import data from a CSV file
- Auto-generate data by clicking on the Test Data tab
Using the Test Data option, I chose to generate 5 rows which gave me this.
3. Get sample CRUD code
Click on [Table] > API Docs and choose a language to see sample code for
- getting records (using HTTP GET method)
- creating a record (using HTTP POST method)
- update a record (using HTTP PUT method)
- delete a record using (HTTP DELETE method)
- View the complete REST API
- View examples on how to query the database
- View examples on how to create, update and delete data
- View examples of NoSql data aggregation
PHP
The sample PHP CRUD code provided by RestDB.io requires the use of the HttpRequest PEAR PECEL HTTP_Request extension. To avoid having to install this dependency, you can just use cURL which is included with PHP.
NodeJS
Below is a screenshot using NodeJS using the Request library.
JavaScript using jQuery AJAX
You can also perform CRUD actions using client-side JavaScript but you need to set up CORS (cross-origin resource sharing). The example below shows JavaScript using jQuery AJAX.
For client-side JavaScript, you need to generate a CORS API key. The screenshot below shows a CORS API key (redacted) that allows connections from any origin server but only allows the GET method to read data.
In the screenshot above, the API key would only work for the “people” table / collection. To have one key work for all tables / collections, use “/**” instead.
4. PHP: Create a file to test reading from the database
I created a test file called read-curl.php which uses cURL to connect to the remote database.
After uploading it to my web host, opening the file in a browser shows me the data in JSON format, as expected, which you can see live at http://zabuun.com/restdb.io/read-curl.php
You can also test the API by installing Postman (free), entering a query in the form of a REST URL, and entering your API key as an HTTP header.
4. JavaScript (jQuery + AJAX): Create a file to test reading from the database
I created a test file called read.html with the following code. This code uses the CORS key for cross-origin resource sharing.
After uploading it to my web host, opening the file in a browser shows me the data in JSON format, as expected, which you can see live at http://zabuun.com/restdb.io/read.html
If you have JSON data as in the example above, you can use Handlebars.JS to format the output in HTML.
5. Query data
When reading data from RestDB , you will often want to filter data based on conditions rather than retrieve an entire collection (all records). RestDB supports many MongoDB-like query syntax. Read the docs. Here’s an example of RestDB queries for equivalent relational DB SQL queries.
| SQL | RestDB |
|---|---|
| SELECT * FROM users | https://<db-name>.restdb.io/rest/users?q={} |
| SELECT id, user_id, status FROM users | /rest/users?q={}&h={"$fields": {"user_id": 1, "status": 1} } |
| SELECT * FROM users WHERE status = “A” | /rest/users?q={ "status": "A" } |
| SELECT * FROM users WHERE status != “A” | /rest/users?q={"status":{"$not":"A"}} |
| SELECT * FROM users WHERE status = “A” AND age = 50 | /rest/users?q={ "status": "A", "age": 50 } |
| SELECT * FROM users WHERE status = “A” OR age = 50 | /rest/users?q={ "$or": [ { "status": "A" } ,{ "age": 50 } ] } |
| SELECT * FROM users WHERE age > 25 | /rest/users?q={ "age": { "$gt": 25 } } |
| SELECT * FROM users WHERE age < 25 | /rest/users?q={ "age": { "$lt": 25 } } |
| SELECT * FROM users WHERE age > 25 AND age <= 50 | /rest/users?q={ "age": { "$gt": 25, "$lte": 50 } } |
| SELECT * FROM users WHERE user_id like “%bc%” | /rest/users?q={ "user_id": {"$regex" :"bc"}} |
| SELECT * FROM users WHERE user_id like “bc%” | /rest/users?q={ "user_id": {"$regex" :"^bc"}} |
| SELECT * FROM users WHERE status = “A” ORDER BY user_id ASC | /rest/users?q={ "status": "A" }&sort=user_id&dir=1 |
| SELECT * FROM users WHERE status = “A” ORDER BY user_id DESC | /rest/users?q={ "status": "A" }&sort=user_id&dir=-1 |
| SELECT COUNT(*) FROM users | /rest/users?q={}&h={"$aggregate":["COUNT:"]} |
| SELECT COUNT(*) FROM users WHERE age > 30 | /rest/users?q={"age":{"$gt": 30}}&h={"$aggregate":["COUNT:"]} |
| SELECT * FROM users LIMIT 1 | /rest/users?q={}&max=1 |
| SELECT * FROM users LIMIT 5 SKIP 10 | /rest/users?q={}&max=5&skip=10 |
You can also aggregate data, e.g. min, max, avg, count, groupby, etc, using aggregation and grouping functions. Read the docs.
If you already have an entire JSON dataset and you want to query against it, you can use JSONata. JSONata is a lightweight query and transformation language for JSON data.
6. Auto-generate a web form
If you want to create a web form for public use, you can have restdb.io auto-generate one for you, complete with JavaScript validation and code to publish the data to your restdb.io database. See an example at
http://zabuun.com/restdb.io/form.html
7. Filesystem
Below is a screenshot of my remote filesystem on a shared GoDaddy server. The error.log file is auto-generated.
8. Create users with editor access
If you work with other developers or editors, you can give them role-based access to your restdb.io account to manage data. An editor, for example, will not see the developer features and can simply add records using a user-friendly web form like shown below.
