SQL Linter Reference

Coginiti includes a powerful built-in SQL linter that performs real-time interactive checks on catalog assets and SQL/CoginitiScript code. The linter helps enforce best practices, maintain code consistency, and detect potential issues while offering suggestions for improvement.

Overview

The SQL linter provides:

• Real-time analysis of SQL and CoginitiScript code
• Interactive feedback with suggestions and fixes
• Configurable rules for different coding standards
• Code actions for automatic fixes where possible
• Multi-platform support across database engines

Configuration

The linter can be configured using either configuration files or in-file comments to customize rule behavior according to your team's coding standards.

Configuration Files

The linter uses TOML configuration files for project-wide rule settings.

File Discovery

The linter searches for configuration files in the following order:

1. Current directory: Looks for linter.toml in the directory of the file being analyzed
2. Parent directories: Searches up the directory tree until finding linter.toml or reaching the root
3. Fallback: If no configuration file is found, the linter is disabled

Configuration Format

Configuration files use the TOML format with the following structure:

[linter.sql.<group>.<rule_name>]
enabled = true/false
# Additional rule-specific properties

Example linter.toml:

[linter.sql.aliasing]
prevent_self_alias.enabled = true
prevent_duplicates.enabled = true
prevent_unused.enabled = true

[linter.sql.structure]
order_select_targets_by_complexity.enabled = true
distinct_no_parens.enabled = true
cte_use_instead_of_subquery.enabled = false

Rule Properties

Each rule includes at minimum:

• enabled: Boolean controlling whether the rule is active

Complex rules may include additional properties:

• explicit_as: For aliasing rules, controls AS keyword requirements
• threshold_values: For performance-related rules
• exception_patterns: For rule-specific exceptions

In-File Configuration

Rules can be configured directly within SQL files using special comment syntax.

Syntax

-- linter.sql.<group>.<rule_name>.enabled = true/false
-- linter.sql.<group>.<rule_name>.<property> = value

Scope

In-file configurations:

• Apply from the line where defined until the end of the file
• Can be updated multiple times within the same file
• Override configuration file settings for that specific file

Example

-- Enable rule to prevent self aliasing
-- linter.sql.aliasing.prevent_self_alias.enabled = true
--
-- Prevent usage of unused aliases
-- linter.sql.aliasing.prevent_unused.enabled = true

SELECT
    transaction_date AS transaction_date -- This will trigger prevent_self_alias
FROM
    sales_header AS sh; -- This will trigger prevent_unused if 'sh' is not used

Best Practices

• Prefer configuration files for project-wide standards
• Use in-file configuration sparingly for file-specific requirements
• Document reasons when overriding project-wide rules
• Keep configurations minimal to avoid clutter

Linter Rules

Aliasing Group

Rules in the aliasing group check various aspects of how aliases for SQL identifiers should be specified.

linter.sql.aliasing.type

Name: Alias Style Enforcement

Description: Ensures SQL identifier aliases follow a consistent style preference.

Code Action: ✅ Available

Additional Properties:

• explicit_as: Whether the AS keyword is required (default: true)

Anti-pattern (when explicit_as = true):

SELECT
  customer_name name,
  order_date date
FROM
  orders o;

Best Practice:

SELECT
  customer_name AS name,
  order_date AS date
FROM
  orders AS o;

linter.sql.aliasing.prevent_self_alias

Name: Prevent Self-Aliasing

Description: Identifies redundant aliases where identifiers are aliased to themselves.

Code Action: ✅ Available

Anti-pattern:

SELECT
  transaction_datetime AS transaction_datetime,
  product_id AS product_id,
  quantity AS quantity,
  price AS price
FROM
  sales_header;

Best Practice:

SELECT
  transaction_datetime,
  product_id,
  quantity,
  price
FROM
  sales_header;

linter.sql.aliasing.prevent_duplicates

Name: Prevent Duplicate Aliases

Description: Ensures all aliases within a clause are unique to avoid confusion and potential errors.

Code Action: ❌ Not Available

Anti-pattern:

SELECT
  customer_name AS name,
  product_name AS name,
  order_total AS total
FROM
  orders;

Best Practice:

SELECT
  customer_name AS customer_name,
  product_name AS product_name,
  order_total AS total
FROM
  orders;

linter.sql.aliasing.prevent_unused

Name: Prevent Unused Aliases

Description: Identifies table or column aliases that are defined but never referenced.

Code Action: ✅ Available

Anti-pattern:

SELECT
  customer_id,
  order_date
FROM
  orders AS o; -- 'o' alias is never used

Best Practice:

-- Option 1: Use the alias
SELECT
  o.customer_id,
  o.order_date
FROM
  orders AS o;

-- Option 2: Remove the alias
SELECT
  customer_id,
  order_date
FROM
  orders;

linter.sql.aliasing.require_alias_for_expression

Name: Require Aliases for Expressions

Description: Enforces that column expressions (calculations, functions, etc.) have explicit aliases.

Code Action: ❌ Not Available

Anti-pattern:

SELECT
  SUM(order_total),
  COUNT(*),
  AVG(item_price)
FROM
  orders;

Best Practice:

SELECT
  SUM(order_total) AS total_revenue,
  COUNT(*) AS order_count,
  AVG(item_price) AS avg_price
FROM
  orders;

Structure Group

Rules in the structure group check various aspects of how SQL queries should be organized and structured.

linter.sql.structure.order_select_targets_by_complexity

Name: Order SELECT Targets by Complexity

Description: Recommends ordering SELECT list items from simple to complex: wildcards first, simple columns next, calculations and aggregates last.

Code Action: ✅ Available

Anti-pattern:

SELECT
  customer_name,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num,
  order_date,
  SUM(order_total) OVER (PARTITION BY customer_id) AS customer_total,
  product_name
FROM
  orders;

Best Practice:

SELECT
  customer_name,
  order_date,
  product_name,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num,
  SUM(order_total) OVER (PARTITION BY customer_id) AS customer_total
FROM
  orders;

linter.sql.structure.distinct_no_parens

Name: DISTINCT Without Parentheses

Description: Prevents the incorrect usage of DISTINCT with parentheses, which can be misleading.

Code Action: ✅ Available

Anti-pattern:

SELECT
  DISTINCT(customer_id),
  order_date
FROM
  orders;

Best Practice:

SELECT DISTINCT
  customer_id,
  order_date
FROM
  orders;

linter.sql.structure.join_table_order

Name: Join Table Ordering

Description: Recommends listing tables in JOIN conditions in the order they appear in the FROM clause.

Code Action: ❌ Not Available

Anti-pattern:

SELECT
  sh.transaction_datetime,
  sd.product_id,
  sd.quantity,
  sd.price
FROM
  sales_header AS sh
INNER JOIN sales_detail AS sd
  ON sales_header.sales_header_key = sales_detail.sales_header_key;

Best Practice:

SELECT
  sh.transaction_datetime,
  sd.product_id,
  sd.quantity,
  sd.price
FROM
  sales_header AS sh
INNER JOIN sales_detail AS sd
  ON sh.sales_header_key = sd.sales_header_key;

linter.sql.structure.cte_use_instead_of_subquery

Name: Use CTEs Instead of Subqueries

Description: Recommends using Common Table Expressions (CTEs) instead of subqueries in JOIN and FROM clauses for better readability.

Code Action: ❌ Not Available

Anti-pattern:

SELECT
  o.customer_id,
  o.order_date,
  summary.total_orders,
  summary.avg_order_value
FROM
  orders AS o
INNER JOIN (
    SELECT
      customer_id,
      COUNT(*) AS total_orders,
      AVG(order_total) AS avg_order_value
    FROM
      orders
    GROUP BY
      customer_id
  ) AS summary
  ON summary.customer_id = o.customer_id;

Best Practice:

WITH customer_summary AS (
  SELECT
    customer_id,
    COUNT(*) AS total_orders,
    AVG(order_total) AS avg_order_value
  FROM
    orders
  GROUP BY
    customer_id
)
SELECT
  o.customer_id,
  o.order_date,
  cs.total_orders,
  cs.avg_order_value
FROM
  orders AS o
INNER JOIN customer_summary AS cs
  ON cs.customer_id = o.customer_id;

linter.sql.structure.cte_prevent_unused

Name: Prevent Unused CTEs

Description: Identifies Common Table Expressions that are defined but never referenced in the main query.

Code Action: ❌ Not Available

Anti-pattern:

WITH customer_orders AS (
  SELECT
    customer_id,
    COUNT(*) AS order_count
  FROM
    orders
  GROUP BY
    customer_id
),
product_sales AS (
  SELECT
    product_id,
    SUM(quantity) AS total_sold
  FROM
    order_items
  GROUP BY
    product_id
)
SELECT
  customer_id,
  order_count
FROM
  customer_orders; -- product_sales CTE is unused

Best Practice:

WITH customer_orders AS (
  SELECT
    customer_id,
    COUNT(*) AS order_count
  FROM
    orders
  GROUP BY
    customer_id
)
SELECT
  customer_id,
  order_count
FROM
  customer_orders;

linter.sql.structure.join_no_using

Name: Prevent USING in JOIN Clauses

Description: Discourages the use of the USING keyword in JOIN clauses in favor of explicit ON conditions for clarity.

Code Action: ❌ Not Available

Anti-pattern:

SELECT
  o.order_id,
  o.order_date,
  c.customer_name,
  c.email
FROM
  orders AS o
INNER JOIN customers AS c USING(customer_id);

Best Practice:

SELECT
  o.order_id,
  o.order_date,
  c.customer_name,
  c.email
FROM
  orders AS o
INNER JOIN customers AS c
  ON c.customer_id = o.customer_id;

Platform Support

The SQL linter is currently supported on the following database platforms:

Platform	Status	Notes
PostgreSQL	✅ Full Support	All rules supported
Amazon Redshift	✅ Full Support	All rules supported
Snowflake	✅ Full Support	All rules supported
Google BigQuery	✅ Full Support	All rules supported

Platform-Specific Considerations

PostgreSQL

• Full rule compatibility
• Supports all advanced SQL features
• Case-sensitive identifier handling

Amazon Redshift

• Compatible with PostgreSQL rules
• Additional considerations for distribution keys
• Column compression awareness

Snowflake

• Account for case-insensitive identifiers by default
• Warehouse-specific optimization hints
• Support for variant data types

Google BigQuery

• Standard SQL dialect support
• Partitioning and clustering considerations
• UDF and JavaScript function support

IDE Integration

Real-Time Feedback

The linter provides immediate feedback while writing SQL:

• Error highlighting: Red underlines for violations
• Warning indicators: Yellow underlines for suggestions
• Quick fixes: Light bulb icons for available code actions
• Hover tooltips: Detailed explanations of issues

Code Actions

Available code actions include:

• Auto-fix violations: Automatic correction of rule violations
• Apply suggestions: Implementation of best practice recommendations
• Ignore violations: Add suppression comments for specific instances
• Configure rules: Quick access to rule configuration

Performance

The linter is designed for minimal performance impact:

• Incremental analysis: Only re-analyzes changed code
• Background processing: Non-blocking operation
• Caching: Efficient rule evaluation caching
• Configurable limits: Adjustable analysis depth

Best Practices

Configuration Management

1. Use version control: Store linter.toml files in your repository
2. Team consensus: Establish agreed-upon coding standards
3. Gradual adoption: Enable rules incrementally for existing projects
4. Document exceptions: Clearly explain any rule overrides

Rule Selection

1. Start conservative: Begin with essential rules like preventing duplicates
2. Prioritize safety: Enable rules that prevent functional issues first
3. Consider context: Different rules may apply to different project types
4. Regular review: Periodically assess rule effectiveness

Code Quality

1. Address violations promptly: Don't let linter warnings accumulate
2. Understand the why: Learn the reasoning behind each rule
3. Consistent application: Apply rules uniformly across the codebase
4. Continuous improvement: Refine rules based on team feedback

Performance Optimization

1. Selective enabling: Only enable rules relevant to your use case
2. File-level overrides: Use in-file configuration sparingly
3. Regular maintenance: Remove unused configuration settings
4. Monitor impact: Watch for any performance degradation

Troubleshooting

Common Issues

Linter Not Running

Symptoms: No linter feedback appearing in the editor

Solutions:

1. Check for linter.toml file in the project directory
2. Verify file syntax and rule configuration
3. Ensure the file being edited is on a supported platform
4. Restart the Coginiti application

False Positives

Symptoms: Linter flags valid SQL as problematic

Solutions:

1. Review rule documentation for edge cases
2. Use in-file configuration to disable specific rules
3. Update rule configuration in linter.toml
4. Report issues to support if rule behavior seems incorrect

Performance Issues

Symptoms: Slow editor response or high CPU usage

Solutions:

1. Reduce the number of enabled rules
2. Check for very large SQL files that may need optimization
3. Clear any cached configurations
4. Consider breaking large queries into smaller components

Configuration Not Applied

Symptoms: Rule changes in configuration files don't take effect

Solutions:

1. Verify TOML syntax is correct
2. Check file path and name (must be exactly linter.toml)
3. Restart Coginiti to reload configuration
4. Ensure configuration file is in the correct directory

Getting Help

For additional support with the SQL linter:

• Support Portal: support.coginiti.co
• Community: Ask questions on Stack Overflow
• Documentation: Check the latest rule updates and examples

Examples

Complete Configuration Example

# linter.toml - Comprehensive configuration example

[linter.sql.aliasing]
# Enforce explicit AS keyword
type.enabled = true
type.explicit_as = true

# Prevent self-aliasing (redundant aliases)
prevent_self_alias.enabled = true

# Ensure unique aliases within clauses  
prevent_duplicates.enabled = true

# Flag unused table/column aliases
prevent_unused.enabled = true

# Require aliases for calculated expressions
require_alias_for_expression.enabled = true

[linter.sql.structure]
# Order SELECT items by complexity
order_select_targets_by_complexity.enabled = true

# Prevent DISTINCT() with parentheses
distinct_no_parens.enabled = true

# Prefer explicit JOIN table ordering
join_table_order.enabled = true

# Recommend CTEs over subqueries
cte_use_instead_of_subquery.enabled = false  # Too strict for some teams

# Remove unused CTEs
cte_prevent_unused.enabled = true

# Prefer explicit ON over USING in joins
join_no_using.enabled = true

In-File Configuration Example

-- Project requires explicit AS keywords
-- linter.sql.aliasing.type.explicit_as = true

-- Disable self-alias check for this file (legacy compatibility)
-- linter.sql.aliasing.prevent_self_alias.enabled = false

WITH sales_summary AS (
  SELECT
    customer_id AS customer_id,  -- Normally flagged, but disabled above
    SUM(order_total) AS total_sales,
    COUNT(*) AS order_count
  FROM
    orders
  WHERE
    order_date >= '2024-01-01'
  GROUP BY
    customer_id
)
SELECT
  ss.customer_id,
  ss.total_sales,
  ss.order_count,
  c.customer_name AS name  -- Explicit AS required by configuration
FROM
  sales_summary AS ss
INNER JOIN customers AS c
  ON c.customer_id = ss.customer_id
ORDER BY
  ss.total_sales DESC;

The SQL linter is a powerful tool for maintaining code quality and consistency across your Coginiti projects. By configuring rules appropriately and following best practices, teams can significantly improve their SQL code quality and reduce common errors.