Cursor Rules for PostgreSQL
---
description: This rule explains PostgreSQL database design patterns and advanced features usage.
globs: **/*.sql
alwaysApply: false
---
# PostgresSQL rules
## General
- Use lowercase for SQL reserved words to maintain consistency and readability.
- Employ consistent, descriptive identifiers for tables, columns, and other database objects.
- Use white space and indentation to enhance the readability of your code.
- Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`).
- Include comments for complex logic, using '/* ... */' for block comments and '--' for line comments.
## Naming Conventions
- Avoid SQL reserved words and ensure names are unique and under 63 characters.
- Use snake_case for tables and columns.
- Prefer plurals for table names
- Prefer singular names for columns.
## Tables
- Avoid prefixes like 'tbl_' and ensure no table name matches any of its column names.
- Always add an `id` column of type `identity generated always` unless otherwise specified.
- Create all tables in the `public` schema unless otherwise specified.
- Always add the schema to SQL queries for clarity.
- Always add a comment to describe what the table does. The comment can be up to 1024 characters.
## Columns
- Use singular names and avoid generic names like 'id'.
- 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
- Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception.
#### Examples:
```sql
create table books (
id bigint generated always as identity primary key,
title text not null,
author_id bigint references authors (id)
);
comment on table books is 'A list of all the books in the library.';
```
## Queries
- When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability
- Add spaces for readability.
Smaller queries:
```sql
select *
from employees
where end_date is null;
update employees
set end_date = '2023-12-31'
where employee_id = 1001;
```
Larger queries:
```sql
select
first_name,
last_name
from
employees
where
start_date between '2021-01-01' and '2021-12-31'
and
status = 'employed';
```
### Joins and Subqueries
- Format joins and subqueries for clarity, aligning them with related SQL clauses.
- Prefer full table names when referencing tables. This helps for readability.
```sql
select
employees.employee_name,
departments.department_name
from
employees
join
departments on employees.department_id = departments.department_id
where
employees.start_date > '2022-01-01';
```
## Aliases
- Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity.
```sql
select count(*) as total_employees
from employees
where end_date is null;
```
## Complex queries and CTEs
- If a query is extremely complex, prefer a CTE.
- Make sure the CTE is clear and linear. Prefer readability over performance.
- Add comments to each block.
```sql
with department_employees as (
-- Get all employees and their departments
select
employees.department_id,
employees.first_name,
employees.last_name,
departments.department_name
from
employees
join
departments on employees.department_id = departments.department_id
),
employee_counts as (
-- Count how many employees in each department
select
department_name,
count(*) as num_employees
from
department_employees
group by
department_name
)
select
department_name,
num_employees
from
employee_counts
order by
department_name;
```Related Snippets
This rule provides comprehensive guidelines for PostgreSQL database design and SQL coding practices. It's designed to help maintain consistency, readability, and best practices when working with PostgreSQL databases in your projects.
What this rule does
The PostgreSQL rule serves as a style guide and best practices reference for database development with PostgreSQL. It covers essential aspects of database design including:
- General formatting principles
- Naming conventions for database objects
- Table and column design guidelines
- Query formatting for improved readability
- Techniques for handling complex queries
This rule helps enforce consistent database design patterns across your project, making your database code more maintainable and understandable by all team members.
Key PostgreSQL guidelines
General formatting
The rule emphasizes consistent formatting with lowercase SQL reserved words, descriptive identifiers, and proper use of whitespace. For example, it recommends storing dates in ISO 8601 format and including comments for complex logic.
Naming conventions
Specific naming patterns are recommended:
- Using snake_case for tables and columns
- Plural names for tables (e.g.,
books) - Singular names for columns (e.g.,
title) - Avoiding SQL reserved words as identifiers
- Keeping names under 63 characters
Table design
For table creation, the rule recommends:
- Always adding an
idcolumn as an identity - Creating tables in the
publicschema by default - Including schema references in SQL queries
- Adding descriptive comments to explain table purpose
Query formatting
The rule provides clear guidance on query formatting based on query complexity:
- Smaller queries can be more compact
- Larger queries should use newlines and consistent indentation
- Joins should be clearly formatted with full table names
- Descriptive aliases should always use the
askeyword
Complex query handling
For complex queries, the rule recommends:
- Using Common Table Expressions (CTEs) for readability
- Adding comments to explain each logical block
- Focusing on clarity over performance optimizations
Using PostgreSQL in Cursor
This rule is stored as postgresql.mdc in your project's .cursor/rules directory. It's configured as an "Auto Attached" rule that activates automatically when you work with SQL files, as indicated by its glob pattern (**/*.sql).
When you open or create a SQL file in Cursor, this rule will automatically provide context to the AI, influencing how it responds to your queries about PostgreSQL database design and offering guidance when generating SQL code.
You can also manually invoke this rule in any context by typing @postgresql in the chat or Cmd-K interface, which is useful when discussing database design even outside of SQL files.
Usage tips
When to apply this rule
This rule is particularly valuable in these scenarios:
- When designing a new database schema
- When reviewing existing database code for consistency
- When writing complex queries that need to be maintainable
- When onboarding new team members to your database practices
Examples of rule application
For instance, when asking the AI to help you create a new table, with this rule activated, it will follow the conventions automatically:
create table users (
id bigint generated always as identity primary key,
username text not null,
email text not null,
created_at timestamp with time zone default now()
);
comment on table users is 'Stores user account information for application authentication.';
When requesting help with complex queries, the AI will format them according to the rule's guidelines, using CTEs for clarity and proper indentation:
with active_users as (
-- Find users who logged in within the last 30 days
select
user_id,
username,
last_login_date
from
users
where
last_login_date > current_date - interval '30 days'
),
user_activity as (
-- Count actions per user
select
user_id,
count(*) as action_count
from
user_actions
where
action_date > current_date - interval '30 days'
group by
user_id
)
select
active_users.username,
user_activity.action_count
from
active_users
join
user_activity on active_users.user_id = user_activity.user_id
order by
user_activity.action_count desc;
By consistently applying these guidelines, your database code will remain readable, maintainable, and follow PostgreSQL best practices.