plugin-sql

@putout/plugin-sql

SQL (Structured Query Language) is a descriptive computer language designed for updating, retrieving, and calculating data in table-based databases.

(c) MDN

🐊Putout plugin adds ability improve SQL. Not Bundled.

Install

npm i putout @putout/plugin-sql -D

Add .putout.json with:

{
    "plugins": ["sql"]
}

Rules

• ✅ apply-count;
• ✅ postgres;
• ✅ convert-sqlite-to-postgres;
• ✅ convert-postgres-to-sqlite;

Config

{
    "rules": {
        "sql/apply-count": "on",
        "sql/postgres": "on",
        "sql/convert-sqlite-to-postgres": "off",
        "sql/convert-postgres-to-sqlite": "off"
    }
}

apply-count

The COUNT() function returns the number of rows that matches a specified criterion.

(c) w3cshools.com

Check out in 🐊Putout Editor.

❌ Example of incorrect code

SELECT COUNT(1) FROM orders

✅ Example of correct code

SELECT COUNT(*) FROM orders

convert-sqlite-to-postgres

Rules

• ✅ apply-key-exists;
• ✅ apply-jsonb-extract-path-text;
• ✅ convert-last-insert-rowid-to-returning-id;
• ✅ convert-auto-increment-to-identity;
• ✅ convert-generate-series-to-with-recursive;

Config

{
    "rules": {
        "sql/convert-sqlite-to-postgres/apply-key-exists": "off",
        "sql/convert-sqlite-to-postgres/apply-jsonb-extract-path-text": "off",
        "sql/convert-sqlite-to-postgres/convert-last-insert-rowid-to-returnning-id": "off",
        "sql/convert-sqlite-to-postgres/convert-auto-increment-to-identitiy": "off",
        "sql/convert-sqlite-to-postgres/convert-generate-series-to-with-recursive": "off"
    }
}

apply-key-exists

? - does the string exist as a top-level key within the JSON value?

postgresql.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT *
FROM users
WHERE json_type(data, '$.email') IS NOT NULL;

✅ Example of correct code

SELECT *
FROM users
WHERE data ? 'email';

apply-jsonb-extract-path-text

The jsonb_extract_path_text() function allows you to extract a JSON subobject as text from a JSONB value at a specified path.

neon.com

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT json_extract(data, '$.user.name')
FROM users;

✅ Example of correct code

SELECT jsonb_extract_path_text(data, 'user', 'name')
FROM users;

convert-last-insert-rowid-to-returning-id

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function

sqlite.org

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, DELETE, and MERGE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

postgres.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

INSERT INTO users (name)
VALUES ('Alice');
SELECT last_insert_rowid();

✅ Example of correct code

INSERT INTO users (name)
VALUES ('Alice')
RETURNING id;

convert-auto-increment-to-identity

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

sqlite.org

An identity column is a special column that is generated automatically from an implicit sequence. It can be used to generate key values.

postgres.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

✅ Example of correct code

CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

convert-generate-series-to-with-recursive

The generate_series() generates a series of values from start to stop, with a step size of step

postgresql.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT * FROM generate_series(1, 10, 2);

✅ Example of correct code

WITH RECURSIVE numbers(value) AS (
    SELECT 1
    UNION ALL
    SELECT value + 2
    FROM numbers
    WHERE value < 10
)
SELECT value FROM numbers;

convert-postgres-to-sqlite

Rules

• ✅ apply-json-extract;
• ✅ apply-json-type;
• ✅ apply-auto-increment;
• ✅ convert-with-to-sequential;
• ✅ convert-lastval-to-last-insert-rowid;

Config

{
    "rules": {
        "sql/convert-postgrest-to-sqlite/apply-json-type": "off",
        "sql/convert-postgrest-to-sqlite/apply-json-extract": "off",
        "sql/convert-postgrest-to-sqlite/apply-auto-increment": "off",
        "sql/convert-postgrest-to-sqlite/convert-with-to-sequential": "off",
        "sql/convert-postgrest-to-sqlite/convert-lastval-to-last-insert-rowid": "off"
    }
}

apply-json-type

The json_type(X) function returns the "type" of the outermost element of X

sqlite.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT *
FROM users
WHERE data ? 'email';

✅ Example of correct code

SELECT *
FROM users
WHERE json_type(data, '$.email') IS NOT NULL;

apply-json-extract

The json_extract(X,P1,P2,...) extracts and returns one or more values from the well-formed JSON at X.

sqlite.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT jsonb_extract_path_text(data, 'user', 'name')
FROM users;

✅ Example of correct code

SELECT json_extract(data, '$.user.name')
FROM users;

apply-auto-increment

With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

sqlite.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

✅ Example of correct code

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

convert-with-to-sequential

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.

postgres.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

INSERT INTO users (name)
VALUES ('Alice');
SELECT last_insert_rowid();

✅ Example of correct code

INSERT INTO users (name)
VALUES ('Alice')
RETURNING id;

convert-lastval-to-last-insert-rowid

nextval returns the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

postgres.org

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function.

(c) sqlite.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

SELECT lastval();

✅ Example of correct code

SELECT last_insert_rowid();

postgres

Rules

• ✅ apply-generate-series;
• ✅ convert-sequence-to-serial;
• ✅ convert-serial-to-identity;

Config

{
    "rules": {
        "sql/postgres/apply-generate-series": "on",
        "sql/postgres/convert-sequence-to-serial": "on",
        "sql/postgres/convert-serial-to-identity": "on"
    }
}

apply-generate-series

The generate_series() generates a series of values from start to stop, with a step size of step

postgresql.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

WITH RECURSIVE numbers(value) AS (
    SELECT 1
    UNION ALL
    SELECT value + 2
    FROM numbers
    WHERE value < 10
)
SELECT value FROM numbers;

✅ Example of correct code

SELECT * FROM generate_series(1, 10, 2);

convert-sequence-to-serial

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)

postgres.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id INTEGER DEFAULT nextval('users_id_seq') PRIMARY KEY,
    name TEXT
);

✅ Example of correct code

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

convert-serial-to-identity

An identity column is a special column that is generated automatically from an implicit sequence. It can be used to generate key values.

postgres.org

Checkout in 🐊Putout Editor.

❌ Example of incorrect code

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

✅ Example of correct code

CREATE TABLE users (
    id GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

License

MIT