joereynolds / joereynolds/sql-lint

List of checks to incorporate

Open
#89 0 comments 0 reactions 0 assignees View on GitHub
check
Dominant language
TypeScript
Stars
459
Forks
44
PR merge metrics
No merged PRs in 30d

Description

This is a dumping ground of errors that I've encountered in SQL that we should add checks for.

### (ALL) - trailing whitespace

### (ALL) - Unquoted string

`INSERT INTO person (name) VALUES (Tim);`

Tim should be 'Tim'

### (UPDATE) - Update without a WHERE

Same as the DELETE without a WHERE. Move the `delete/missingWhere` check into the `generic` directory and have it apply to the update method as well.

### (ALL) - Wrong ordering of statement

Sometimes you put the `WHERE`, `LIMIT` or `ORDER BY` in the wrong order. We need a check to guard against this.

i.e.

```
SELECT * FROM api_response ORDER BY `id` DESC WHERE request_type = 'store';
```

Should be
```
SELECT * FROM api_response WHERE request_type = 'store' ORDER BY `id` DESC;
```

### (ALL) - Invalid data type

You can't assign a string to an integer for example

i.e. (assuming age is an `integer` type)
```
INSERT INTO person(age) VALUES ("test");
```

It should complain that we're trying to insert a non-numeric value in its place.

### (ALL) - lower cased reserved word

Things like `SELECT` and `UPDATE` should be uppercased and we should be shouted at for not following this.

### Incorrect foreign key data type

If you're making foreign keys in a `CREATE TABLE` statement, the data types of the foreign keys (as well as the length of the data type) must match. Write a check to guard against this.

### (DECLARE) - Permitted only within BEGIN...END block

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

### (DECLARE) - Declarations must follow a certain order

Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.