SQL style guide· 2 minute read
When you’re working in a team you need ways to easily share and denote good style and taste. This is true of your primary programming language with PEP8 for Python and PSRs 1 & 2 for PHP being well known. There is probably even a style guide for HTML and CSS set out at your company. So why should SQL miss out on the party?
I have written a style guide for SQL to promote a consistent code style ensuring legible and maintainable projects - sqlstyle.guide.
SELECT a.title, a.release_date FROM albums AS a WHERE a.title = 'Charcoal Lane' OR a.title = 'The New Danger';
There are so many variant SQL styles that projects and people use which can make code difficult to easily read. Looking over various questions on Stackoverflow (on of which was mine!) I noticed that there were elements of good style that were shared by most examples.
I figured it was time that SQL had a concise and easy to read style guide that could easily be adopted and/or modified for bespoke requirements.
It is trivial to apply this style to your projects now or going forward. In the case of PHP you could have some code like the following.
$year = filter_input(INPUT_GET, 'year', FILTER_SANITIZE_NUMBER_INT); $db = new PDO( 'mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password' ); $statement = $db->prepare(" SELECT r.last_name, (SELECT MAX(YEAR(championship_date)) FROM champions AS c WHERE c.last_name = r.last_name AND c.confirmed = 'Y') AS last_championship_year FROM riders AS r WHERE r.last_name IN (SELECT c.last_name FROM champions AS c WHERE YEAR(championship_date) > :year AND c.confirmed = 'Y'); "); $statement->bindParam(':year', $year, PDO::PARAM_INT) $statement->execute(); $rows = $statement->fetchAll(PDO::FETCH_ASSOC);
To produce the guide I settled upon using GitHub Pages, Jekyll and Markdown sources. This means it is very easy to make forks, open issues and pull requests as GitHub Pages will handle the hosting and site build process. It is released under the Creative Commons Attribution-ShareAlike 4.0 International License.
The style in this guide is explicitly designed to be compatible with Joe Celko’s book SQL Programming Style so that teams who have already read that book will find the guide easy to adopt.
If you like the guide please consider sharing it with your team and via twitter - thanks!
Image credit: gratisography.com