SQL coding style guide


All Outlast-developed SQL statements should adhere to the following style guide. Not all previously created SQL statements follow this guide, but should be updated when encountered.

Database and user names

By default, databases should have the same name as the project url name on Stash. So for Outlast Framework where the url is https://bitbucket.outlast.io/projects/OFW/repos/outlast-framework/browse the database name should be outlast-framework.

Each database should have a seperate user account that can read/write only to that database. The user name should be the same as the database name, the password should be a randomly generated alphanumeric string. If ther user name is too long (MySQL limits user names to 16 characters) simply cut off the user name at 16 characters, so for the above example outlast-framewor is the proper user name.

Reserved words and functions

All reserved words need to be ALL CAPS:

SELECT * FROM product WHERE id='1'

This goes for functions as well:

SELECT COUNT(*) FROM product

Indentation is with spaces

Unlike all of our other code, SQL should be set to indent with 2 spaces. No tabs here.

Single and multi-line statements

Single line statements are ok for really simple queries that are no longer than a few characters:

SELECT * FROM product WHERE id='1'

Anything involving JOINs or just a more complex WHERE clause should be split into multi-line statements. Here, each clause is on the top level, with two-space indentation:

SELECT
  id
FROM
  product
WHERE
  name LIKE '' AND
  something = 23
ORDER BY
  ordernum ASC

When JOINing, make sure to always specify table aliases (AS). Follow a similar logic with indentiation, but the LEFT JOIN should be indented as part of the FROM clause:

SELECT
  p.id
FROM
  product AS p
  LEFT JOIN user AS u ON p.uploader=u.id
  LEFT JOIN something AS s ON s.product=p.id
WHERE
  u.email LIKE 'user@example.com'
ORDER BY
  name ASC

Single and double quotes

To maximize compatibility, use single quotes for surrounding values:

SELECT * FROM product WHERE name='Test' /** Not "Test" **/

Comments

You can use both -- or /** **/ for comments, but the latter is prefered.

If the SQL is within PHP, then put all comments in the PHP and not in the SQL.

Outlast Web & Mobile Development (c) 2018 | Privacy Policy |