eslint-plugin-sql


SQL linting rules for ESLint.

> In its current form, the plugin has been designed and tested to work with Postgres codebase.

* eslint-plugin-sql
* Installation
* Configuration
* Settings
* placeholderRule
* Rules
* format
* no-unsafe-query

Installation

1. Install ESLint.
1. Install eslint-plugin-sql plugin.

``sh
npm install eslint --save-dev
npm install eslint-plugin-sql --save-dev
`

Configuration

1. Add plugins section and specify eslint-plugin-sql as a plugin.
1. Enable rules.

`json
{
"plugins": [
"sql"
],
"rules": {
"sql/format": [
2,
{
"ignoreExpressions": false,
"ignoreInline": true,
"ignoreTagless": true
}
],
"sql/no-unsafe-query": [
2,
{
"allowLiteral": false
}
]
}
}

`

Settings

$3

A regex used to ignore placeholders or other fragments of the query that'd make it invalid SQL query, e.g.

If you are using ? placeholders in your queries, you must ignore \? pattern as otherwise the string is not going to be recognized as a valid SQL query.

This configuration is relevant for sql/no-unsafe-query to match queries containing placeholders as well as for sql/format when used with {ignoreTagless: false} configuration.

Rules

$3

_The --fix option on the command line automatically fixes problems reported by this rule._

Matches queries in template literals. Warns when query formatting does not match the configured format (see Options).

This rule is used to format the queries using sql-formatter.



#### Options

The first option is an object with the following configuration.

|configuration|format|default|description|
|---|---|---|---|
|ignoreExpressions|boolean|false|Does not format template literals that contain expressions.|
|ignoreInline|boolean|true|Does not format queries that are written on a single line.|
|ignoreStartWithNewLine|boolean|true|Does not remove \n at the beginning of queries.|
|ignoreTagless|boolean|true|Does not format queries that are written without using sql tag.|
|retainBaseIndent|boolean|true|Uses the first line of the query as the base indent.|
|sqlTag|string|sql|Template tag name for SQL.|

The second option is an object with the sql-formatter configuration.

|configuration|default|description|
|---|---|---|
|useTabs|false|Use tabs for indentation.|
|tabSize|2|Number of spaces per indentation.|
|language|sql|Language of the query.|
|keywordCase|preserve|Determines the case of keywords (preserve, upper, lower).|
|dataTypeCase|preserve|Determines the case of data types (preserve, upper, lower).|
|denseOperators|false|Decides whitespace around operators.|
|identifierCase|preserve|Determines the case of identifiers (preserve, upper, lower).|
|functionCase|preserve|Determines the case of functions (preserve, upper, lower).|
|paramTypes|dialect default|Configures parameter placeholders. See paramTypes documentation.|



#### Using Custom Parameter Placeholders

Different SQL dialects support different placeholder styles. By default, sql-formatter uses dialect-specific placeholders (e.g., $1, $2 for PostgreSQL, ? for MySQL). If you're using a different placeholder style like :name, you need to configure paramTypes.

For example, if you're using :name style placeholders with PostgreSQL:

`js
{
"rules": {
"sql/format": [
2,
{
"sqlTag": "sql"
},
{
"language": "postgresql",
"paramTypes": {
"named": [":"]
}
}
]
}
}
`

The paramTypes object supports:

|property|type|description|
|---|---|---|
|positional|boolean|Enable ? positional placeholders.|
|numbered|('$' \| ':' \| '?')[]|Prefixes for numbered placeholders (e.g., $1, :1).|
|named|('$' \| ':' \| '@')[]|Prefixes for named placeholders (e.g., :name, @name).|
|quoted|('$' \| ':' \| '@')[]|Prefixes for quoted placeholders (e.g., :"name").|
|custom|{ regex: string }[]|Custom placeholder patterns using regex.|

The following patterns are considered problems:

`js
sql.fragment
SELECT
m1.ID
FROM
message m1
WHERE
m1.ID = ${message.id}

// Options: [{},{"identifierCase":"lower"}]
// Message: undefined
// Fixed code:
// sql.fragment
// SELECT
// m1.id
// FROM
// message m1
// WHERE
// m1.id = ${message.id}
//

sql.fragment
SELECT id::NUMERIC

// Options: [{},{"dataTypeCase":"lower","language":"postgresql"}]
// Message: undefined
// Fixed code:
// sql.fragment
// SELECT
// id::numeric
//

sql.fragment
SELECT
COUNT(*)
FROM
message
WHERE
id = ${message.id}

// Options: [{},{"keywordCase":"lower"}]
// Message: undefined
// Fixed code:
// sql.fragment
// select
// COUNT(*)
// from
// message
// where
// id = ${message.id}
//

sql.fragment
select
COUNT(*)
from
message
where
id = ${message.id}

// Options: [{},{"keywordCase":"upper"}]
// Message: undefined
// Fixed code:
// sql.fragment
// SELECT
// COUNT(*)
// FROM
// message
// WHERE
// id = ${message.id}
//

sql.fragment
${null}
UPDATE message
SET
messages = ${sql.jsonb(messages as unknown as SerializableValue[])}
WHERE id = ${message.id}
;
// Options: [{},{"tabWidth":4}]
// Message: undefined
// Fixed code:
// sql.fragment
// ${null}
// UPDATE message
// SET
// messages = ${sql.jsonb(messages as unknown as SerializableValue[])}
// WHERE
// id = ${message.id}
// ;

await pool.query(sql.typeAlias('void')
UPDATE message
SET
messages = ${sql.jsonb(messages as unknown as SerializableValue[])}
WHERE id = ${message.id}
);
// Options: [{},{"tabWidth":4}]
// Message: undefined
// Fixed code:
// await pool.query(sql.typeAlias('void')
// UPDATE message
// SET
// messages = ${sql.jsonb(messages as unknown as SerializableValue[])}
// WHERE
// id = ${message.id}
// );

sql
SELECT
1

// Options: [{},{"tabWidth":4}]
// Message: undefined
// Fixed code:
// sql
// SELECT
// 1
//

sql.type({ id: z.number() })
SELECT
1

// Options: [{},{"tabWidth":4}]
// Message: undefined
// Fixed code:
// sql.type({ id: z.number() })
// SELECT
// 1
//

sql.typeAlias('void')
SELECT
1

// Options: [{},{"tabWidth":4}]
// Message: undefined
// Fixed code:
// sql.typeAlias('void')
// SELECT
// 1
//

SELECT 1
// Options: [{"ignoreInline":false,"ignoreTagless":false},{}]
// Message: undefined
// Fixed code:
//
// SELECT
// 1
//

SELECT 2
// Options: [{"ignoreInline":false,"ignoreTagless":false},{"tabWidth":2}]
// Message: undefined
// Fixed code:
//
// SELECT
// 2
//

sql.unsafeSELECT 3
// Options: [{"ignoreInline":false},{}]
// Message: undefined
// Fixed code:
// sql.unsafe
// SELECT
// 3
//

sql.type()SELECT 3
// Options: [{"ignoreInline":false},{}]
// Message: undefined
// Fixed code:
// sql.type()
// SELECT
// 3
//

SELECT ${'foo'} FROM ${'bar'}
// Options: [{"ignoreInline":false,"ignoreTagless":false},{}]
// Message: undefined
// Fixed code:
//
// SELECT
// ${'foo'}
// FROM
// ${'bar'}
//

const code = sql
SELECT
foo
FROM
bar

// Options: [{},{}]
// Message: undefined
// Fixed code:
// const code = sql
// SELECT
// foo
// FROM
// bar
//

SQLSELECT 1
// Options: [{"ignoreInline":false,"sqlTag":"SQL"},{}]
// Message: undefined
// Fixed code:
// SQL
// SELECT
// 1
//

sql
SELECT hi !

// Options: [{},{}]
// Message: undefined

sql
SELECT * FROM users WHERE name = :name AND id = :id

// Options: [{},{"language":"mysql","paramTypes":{"named":[":"]}}]
// Message: undefined
// Fixed code:
// sql
// SELECT
// *
// FROM
// users
// WHERE
// name = :name
// AND id = :id
//

SQL
SELECT * FROM users WHERE user_id = :user_id

// Options: [{"sqlTag":"SQL"},{"keywordCase":"upper","language":"postgresql","paramTypes":{"named":[":"]}}]
// Message: undefined
// Fixed code:
// SQL
// SELECT
// *
// FROM
// users
// WHERE
// user_id = :user_id
//
`

The following patterns are not considered problems:

`js


A

B

$3

sqlSELECT 1
// Options: [{"ignoreInline":true},{}]

SELECT 2
// Options: [{"ignoreTagless":true},{}]

const code = sql
SELECT
${'foo'}
FROM
${'bar'}

// Options: [{"ignoreExpressions":true,"ignoreInline":false,"ignoreTagless":false},{}]

const code = sql
SELECT
${'foo'}
FROM
${'bar'}

// Options: [{},{}]

const code = sql
DROP TABLE foo

// Options: [{},{}]

const code = sql
DROP TABLE foo;

DROP TABLE foo;

// Options: [{},{}]
`

$3

Disallows use of SQL inside of template literals without the sql tag.

The sql tag can be anything, e.g.

* https://github.com/seegno/sql-tag
* https://github.com/gajus/mightyql#tagged-template-literals



#### Options

The first option is an object with the following configuration.

|configuration|format|default|description|
|---|---|---|---|
|allowLiteral|boolean|false|Controls whether sql tag is required for template literals containing literal queries, i.e. template literals without expressions.|
|sqlTag|string|sql|Template tag name for SQL.|

The following patterns are considered problems:

`js
SELECT 1
// Message: undefined

SELECT ${'foo'}
// Message: undefined

fooSELECT ${'bar'}
// Message: undefined

SELECT ?
// Message: undefined

fooSELECT ${'bar'}
// Options: [{"sqlTag":"SQL"}]
// Message: undefined
`

The following patterns are not considered problems:

`js
sql.unsafeSELECT 3

SELECT 1
// Options: [{"allowLiteral":true}]

sqlSELECT 1

sqlSELECT ${'foo'}

SQLSELECT ${'bar'}
// Options: [{"sqlTag":"SQL"}]
``