User Tools

Site Tools


development:ohdsi_code_style_for_sql

OHDSI code style for SQL

The OHDSI code style for SQL is heavily inspired by the Poor Man's T-SQL Formatter, which is available as a NotePad++ plug-in. The only difference with the default settings is that in OHDSI, commas are trailing. You can automatically format your SQL correctly by using the Poor Man's T-SQL Formatter Online Tool (but don't forget to set Trailing Commas).

Case

Because several database platforms are case-insensitive and tend to convert table and field names to either uppercase (e.g. Oracle) or lowercase (e.g. PostgreSQL), we use snake_case. All names should be in lowercase. Reserved words should be in upper case.

Good

SELECT COUNT(*) AS person_count FROM person

Bad

SELECT COUNT(*) AS personCount FROM person

SELECT COUNT(*) AS Person_Count FROM person

SELECT COUNT(*) AS PERSON_COUNT FROM person

select count(*) as person_count from person

Commas

Commas should be trailing.

Good

SELECT COUNT(*) AS person_count,
  condition_concept_id,
  condition_type_concept_id
FROM condition_era
GROUP BY condition_concept_id,
  condition_type_concept_id

Bad

SELECT COUNT(*) AS person_count
  ,condition_concept_id
  ,condition_type_concept_id
FROM condition_era
GROUP BY condition_concept_id
  ,condition_type_concept_id

Indentation and new lines

Indentation is done using tabs. Field definitions are followed by a new line.

Good

SELECT COUNT(*) AS person_count,
  condition_type_concept_id
FROM (
  SELECT * 
  FROM condition_era
  WHERE condition_concept_id = 123
  ) tmp
GROUP BY condition_type_concept_id;

Bad

SELECT COUNT(*) AS person_count, condition_type_concept_id
FROM (SELECT * FROM condition_era WHERE condition_concept_id = 123) tmp
GROUP BY condition_type_concept_id;
development/ohdsi_code_style_for_sql.txt · Last modified: 2018/02/19 09:10 by schuemie