Cursor rule

1---
2description: This rule explains PostgreSQL database design patterns and advanced features usage.
3globs: 
4alwaysApply: false
5---
6# PostgresSQL rules
7 
8## General
9 
10- Use lowercase for SQL reserved words to maintain consistency and readability.
11- Employ consistent, descriptive identifiers for tables, columns, and other database objects.
12- Use white space and indentation to enhance the readability of your code.
13- Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`).
14- Include comments for complex logic, using '/* ... */' for block comments and '--' for line comments.
15 
16## Naming Conventions
17 
18- Avoid SQL reserved words and ensure names are unique and under 63 characters.
19- Use snake_case for tables and columns.
20- Prefer plurals for table names
21- Prefer singular names for columns.
22 
23## Tables
24 
25- Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names.
26- Always add an `id` column of type `identity generated always` unless otherwise specified.
27- Create all tables in the `public` schema unless otherwise specified.
28- Always add the schema to SQL queries for clarity.
29- Always add a comment to describe what the table does. The comment can be up to 1024 characters.
30 
31## Columns
32 
33- Use singular names and avoid generic names like 'id'.
34- For references to foreign tables, use the singular of the table name with the `_id` suffix. For example `user_id` to reference the `users` table
35- Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception.
36 
37#### Examples:
38 
39```sql
40create table books (
41  id bigint generated always as identity primary key,
42  title text not null,
43  author_id bigint references authors (id)
44);
45comment on table books is 'A list of all the books in the library.';
46```
47 
48 
49## Queries
50 
51- When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability
52- Add spaces for readability.
53 
54Smaller queries:
55 
56 
57```sql
58select *
59from employees
60where end_date is null;
61 
62update employees
63set end_date = '2023-12-31'
64where employee_id = 1001;
65```
66 
67Larger queries:
68 
69```sql
70select
71  first_name,
72  last_name
73from
74  employees
75where
76  start_date between '2021-01-01' and '2021-12-31'
77and
78  status = 'employed';
79```
80 
81 
82### Joins and Subqueries
83 
84- Format joins and subqueries for clarity, aligning them with related SQL clauses.
85- Prefer full table names when referencing tables. This helps for readability.
86 
87```sql
88select
89  employees.employee_name,
90  departments.department_name
91from
92  employees
93join
94  departments on employees.department_id = departments.department_id
95where
96  employees.start_date > '2022-01-01';
97```
98 
99## Aliases
100 
101- Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity.
102 
103```sql
104select count(*) as total_employees
105from employees
106where end_date is null;
107```
108 
109 
110## Complex queries and CTEs
111 
112- If a query is extremely complex, prefer a CTE.
113- Make sure the CTE is clear and linear. Prefer readability over performance.
114- Add comments to each block.
115 
116```sql
117with department_employees as (
118  -- Get all employees and their departments
119  select
120    employees.department_id,
121    employees.first_name,
122    employees.last_name,
123    departments.department_name
124  from
125    employees
126  join
127    departments on employees.department_id = departments.department_id
128),
129employee_counts as (
130  -- Count how many employees in each department
131  select
132    department_name,
133    count(*) as num_employees
134  from
135    department_employees
136  group by
137    department_name
138)
139select
140  department_name,
141  num_employees
142from
143  employee_counts
144order by
145  department_name;
146```