PHP-CRUD-API now supports PostgreSQL 9

For the pasts months I have been building PHP-CRUD-API (formerly MySQL-CRUD-API). It is a single PHP file that provides an instant powerful and consistent REST API for a MySQL, PostgreSQL or MS SQL Server database. The application uses reflection to “detect” the table structure and then provide an API in only a few hundred lines of PHP code. It is quite comparable to the REST functionality of the experimental HTTP plugin in MySQL 5.7.

Production performance < 10 ms

I recently finished the test suites and started using it in production. I’ve added some simple .htaccess based firewalling to ensure that only trusted applications can talk to it. Most REST API calls are handled well under 10 ms, so the performance impact on the consuming web application is acceptable. It manages to keep page loads under 100 ms even when doing several REST API calls.

PostgreSQL support and more

Recently PostgreSQL was added as a supported database. With the addition of this third database backend I also changed the name from MySQL-CRUD-API to PHP-CRUD-API. Other features that were recently added are support for “CORS pre-flight requests” (mainly for AngularJS) and “JSON batch insert”. Feature requests are very welcome on the php-crud-api Github page.

Contributions / Future

If you feel like contributing, then maybe these topics inspire you:

  1. Set up Travis automated tests
  2. Add an API documentation generator
  3. Create a plugin system for authentication, authorization and accounting
  4. Port to NodeJS, Java or C#

If you like the project, please give it a star on Github!


10 tools for PHP developers in Ubuntu 14.04

To convert your Ubuntu 14.04 into a powerhouse for software development for PHP, you need to install some developer tools from the rich set of available software packages. I have made a top 10 of my favorite developer tools.

1) Install Eclipse 3.8 as a complete IDE

Altough everybody I know prefers the JetBrains IDE’s, I am still a sucker for free (as in speech) software. I think Eclipse and the various distributions of Eclipse you can download are just as good, when properly configured.

sudo apt-get install eclipse

2) Install Geany 1.23 as a lightweight IDE

I also often find myself using a lightweight IDE. After using way too much “gedit” I have recently (re)discovered “Geany” and I feel it is perfect. With the following commands you install it with a good set of plugins.

sudo apt-get install geany geany-plugins

You can find some Geany color schemes on Github that are worth installing as well.

tar -xvf geany-themes-1.22.2.tar.bz2
mkdir -p ~/.config/geany/colorschemes/
cp geany-themes-1.22.2/colorschemes/* ~/.config/geany/colorschemes/
rm -Rf geany-themes-1.22.2 geany-themes-1.22.2.tar.bz2

3) Install MariaDB 5.5

MariaDB is the replacement for MySQL now that Oracle has acquired it. This is comparable to the replacement of OpenOffice with LibreOffice after Oracle got their hands on it.

sudo apt-get install mariadb-server

4) Install PostgreSQL 9.3

PostgreSQL may be the most powerful database around.

With PostgreSQL 9.2, query results can be returned as JSON data types. Combined with the new PL/V8 Javascript and PL/Coffee database programming extensions, and the optional HStore key-value store, users can now utilize PostgreSQL like a “NoSQL” document database, while retaining PostgreSQL’s reliability, flexibility and performance. (source)

I feel you should always consider using it before choosing any “NoSQL” or “documents-based” database.

sudo apt-get install postgresql pgadmin3

Do not forget to set the root (postgres) password.

sudo -u postgres psql postgres
ALTER USER postgres WITH PASSWORD '<password>';

5) Install Apache 2.4 + PHP 5.5 + modules

Install Apache and the default method (mod_php) to serve the new PHP 5.5.

sudo apt-get install apache2 libapache2-mod-php5

Install PHP 5 support for MariaDB and PostgreSQL.

sudo apt-get install php5-mysql php5-pgsql

Some PHP packages for Symfony development.

sudo apt-get install php5-intl php5-sqlite

PHP packages for cURL and XDebug.

sudo apt-get install php5-curl php5-xdebug

Optional PHP packages for creating images (GD), doing geolocalization on IP address (GeoIP), encryption and Redis/Memcache caches.

sudo apt-get install php5-gd php5-geoip php5-mcrypt php5-redis php5-memcache php5-memcached

Now enable mod_rewrite and restart Apache to make sure everything is loaded.

sudo a2enmod rewrite
sudo service apache2 restart

6) Install Adminer 4

PhpMyAdmin is a very popular web-based database management tool. Adminer is a drop in replacement that beats it on almost all points. You can install the latest version of Adminer using:

sudo mkdir /usr/share/adminer
sudo wget -O /usr/share/adminer/latest.php ""
sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php
echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf-available/adminer.conf
sudo a2enconf adminer
sudo service apache2 reload

Now go here to run it: http://localhost/adminer.php

In the future you can easily update Adminer using:

sudo wget -O /usr/share/adminer/latest.php ""

7) Install Git

Install the Git source code versioning system:

sudo apt-get install git

Check out this great Git cheat sheet.

8) Install some compilers and build tools

With the following commands you get most common compilers and build tools.

sudo apt-get install gcc build-essential libc6-dev autoconf automake

9) Install the Microsoft fonts

There are always people that rely on the standard Microsoft fonts (like Arial). Although this is bad practice and HTML5 allows you to embed fonts, still it may solve some problems to just install these:

sudo apt-get install ttf-mscorefonts-installer

10) Upgrade your Office experience

A developer also needs to read or write some documentation or specifications. Libreoffice Writer is IMHO better than the real thing (Microsoft Word).

sudo apt-get purge gnumeric abiword
sudo apt-get install libreoffice


Did we miss some really important package? Let us know in the comments.


Features of PostgreSQL

Database systems can cost lots of money, this is fairly known. Products like Microsoft SQL Server and Oracle Standard Edition are also billed per CPU (even per core) and may also require client licenses. The costs and issues of licensing may drive people to free (not only as in beer) software. When free database systems are discussed, most people immediately think about MySQL (also owned by Oracle). But there is another, maybe even better, player in the open source market that is less known. Its name is “PostgreSQL”.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. — source:

This database system is free and very powerful. It supports almost all of the features that the paid (and free) counterparts have. Some of the interesting features are: GIS support, hot standby for high availablity and index-only scans (great for big data). Still not convinced? Check out the impressive Feature Matrix of PostgreSQL yourself. It has excellent support on Linux systems (also OSX and Windows) and integrates well with PHP and the frameworks like Symfony2.

Download & try PostgreSQL (for OSX or Windows) or follow installation instructions (for Ubuntu Linux)

Note: If you want a tool like PHPMyAdmin for PostgreSQL you might consider Adminer.