PlaySQL comes with 2 drivers, Hsqldb and PostgreSQL. While you can connect to other databases in theory, I’ve chosen those two because they perfectly represent a minimum valuable product for my plugin.

Why HSQLDB?

Hsqldb is a database-in-a-file engine. That means, it is a classic SQL database which stores all data in a local text file, therefore it is really easy to manipulate. It is probably one of the best tools in this field, along with sqlite. The downside is it is not scalable and not recommended for production. It is just a good alternative until you switch to PostgreSQL.

Why PostgreSQL?

Full disclosure: I like PostgreSQL.

If you’re not already using PostgreSQL to store your data, you should seriously consider the idea. I think Craig Kerstiens summarizes it pretty well in his blog post “Why Postgres“.

Heroku has chosen it, and it’s recognized across the world as an industrial-cast database. Here are two excellent quotes:

Why Postgres? “…because it was operationally more reliable than MySQL.”

“As the years wore on, we discovered that basically Postgres was this diamond in the rough.”

It has also proven to scale with Instagram and Disqus, and is generally considered as the first-choice engine.

I started my career using Oracle SQL and I got used to its features, like the subqueries and the left joins. I also got used to the structure of the databases, with roles, schemas, tablespaces, etc. The closest database management system in the open source world is PostgreSQL, which approach is very similar. Example:

SELECT *

FROM (SELECT * FROM PERSON WHERE TYPE=’STUDENT’) STUDENT

LEFT JOIN HOUSES H ON STUDENT.HOUSE_ID = H.ID

 

The PostgreSQL language is powerful. It supports LEFT JOINs and subqueries; It also has support for recursive ones, just like Oracle SQL, which comes so handy when browsing parent-child relationships.

The PostgreSQL data support is comprehensive. PostgreSQL has a ridiculously huge quantity of datatypes, it even supports JSON, and you can literally query Twitter from PostgreSQL.

PostgreSQL has programming hooks. It supports triggers and custom functions, just like Oracle. Many languages are supported: Python, Ruby, R and JavaScript (V8). And why not deploying javascript applications directly to the database?

PostgreSQL is transaction-safe. Imagine that: It has transactional DDL – You can change the schema within a transaction and roll it back! My currently preferred meme is even if you want to run schemaless like in NoSQL, PostgreSQL can be your best choice, using hstore and PLV8.

Lastly, PostgreSQL is enterprise-ready. It supports schemas, roles and an advanced security scheme which allows you to partition your data securely.

PostgreSQL is certainly a database of choice in 2012, therefore I dedicated my resources to building features rather than support other databases. You’ll be happy to know that PlaySQL allows you to extend the support to other databases using a plugin point, if need be.