Experimenting a simple SQL query abstraction.
After publishing Pilipinews to the public in the last month, I worked again on how to improve the news aggregation as well as how to minimize the usage of its resources. Given that the application is hosted on a five dollar ($5) virtual private server from Digital Ocean, this gave me a challenge on how to work on it and also gave me an opportunity to use widely known Object Relational Mappers (ORMs) like Doctrine ORM and Eloquent ORM from Laravel.
Initially, I started first with Doctrine ORM. For a time it was okay, but setting it up for its entities and repositories could take up time, so I decided to change it back to Eloquent ORM. Same case, it was okay, but I'm afraid that I could not swap it back to Doctrine because they have different patterns being used, ActiveRecord for Eloquent while DataMapper for Doctrine.
Since then, I realized that they are both ORMs, and I think it is a good idea to create an interface on top of them. However, it is a huge challenge for me to work on it because they were implemented on different design patterns and syntax. But I noticed that they are using a similar structure for their respective query builders and it got me thinking, why not create an interface for query builders? While doing some research, I also noticed that there were no standards or interfaces yet similar to the PHP Standards Recommendations (PSR) in creating SQL query builders. With that concept and a scratch to itch, I decided to create a library called Windstorm.
As of now, Windstorm is a chainable, expressive, and interoperable SQL query builder initially based on top of Doctrine's Database Abstraction Layer (DBAL). It should follow the standard for generating SQL queries like SELECT, INSERT INTO, DELETE, and UPDATE. So for a simple query like this:
SELECT * FROM users WHERE age > 10When being written through Windstorm, it should look as this:
$query->select('*')->from('users')->where('age')->isGreaterThan(10);In this syntax, a seasoned and veteran developer who writes SQL for breakfast can easily integrate it into a PHP language. It is also on top of Doctrine's DBAL, means the database driver can be changed without changing the application code. So if it is on MySQL now then wanted to migrate to a SQL Server in the future, it is simple as changing the configuration and it is ready to go.
After writing the proof-of-concept, I decided to use it for Pilipinews. And to my surprise, the resource usage in the server was dropped significantly. Which means I could use more of the remaining resources to other matters. Since it is only a mere working concept, there are still issues and improvements to be done. But I have high optimism that this library could help me with my projects in the future as well as refactoring to the current ones I'm handling.