sql-limiter

sql-limiter

Injects and enforces LIMIT, FIRST, and/or FETCH in your SQL statements.

Try it

const sqlLimiter = require("sql-limiter");

const enforcedSql = sqlLimiter.limit(
  `SELECT * FROM some_table limit 999;`,
  ["limit", "fetch"], // limit strategies to detect/enforce
  100 // max allowed limit
);
console.log(enforcedSql); // SELECT * FROM some_table limit 100;

It is database agnostic, aiming to support anything that kinda looks like SQL (ANSI or not).

It ignores non-SELECT queries. It understands CTE statements. It understands strings and comments. If you are limiting multiple statements it assumes your SQL statements are terminated by either ; or \g;

API

sqlLimiter.limit( sqlText, limitStrategies, limitNumber, offsetNumber )

• sqlText - SQL text to enforce limits on. Multiple statements allowed. Only SELECT statements are targeted.
• limitStrategies - Keyword or array of strategies used to restrict rows. Must be either limit, first, top, fetch for FETCH NEXT/FETCH FIRST.
• limitNumber - Number of rows to allow. If number in statement is lower, it is untouched. If higher it is lowered to limit. If missing it is added.
• offsetNumber - Number of rows to skip before beginning to return rows from the query. If number in statement is defined, it is untouched. If missing it is added. (optional)
• mode - Mode for enforcing limitNumber or offsetNumber. Must be either replace, insert or cap. The default is cap if not defined.
  • "replace": Replace existing value. If not existing, it will be inserted.
  • "insert": Insert if limit or offset are not existing.
  • "cap": Insert if not existing and if higher it is lowered to the defined value.

Returns sqlText with limits enforced.

When an existing limit is not found in a SELECT query, limitStrategies is used to define priority of strategy used. This is useful for databases that support both, such as Postgres.

Example:

// When limit is first
const enforcedSql = sqlLimiter.limit(
  `SELECT * FROM some_table;`,
  ["limit", "fetch"],
  100
);
console.log(enforcedSql); // SELECT * FROM some_table limit 100;

// When fetch is first
const enforcedSql = sqlLimiter.limit(
  `SELECT * FROM some_table;`,
  ["fetch", "limit"],
  100
);
console.log(enforcedSql); // SELECT * FROM some_table fetch first 100 rows only;

// When offset is defined
const enforcedSql = sqlLimiter.limit(
  `SELECT * FROM some_table;`,
  "limit",
  100,
  10
);
console.log(enforcedSql); // SELECT * FROM some_table limit 100 offset 10;

sqlLimiter.getStatements( sqlText )

• sqlText - SQL text to parse and split into individual statements.

Returns array of statement strings. Used by sql-limiter internally but exposed for your convenience.

sqlLimiter.getStatementClasses( sqlText )

• sqlText - SQL text to parse and split into individual statements.

Returns array of statement classes. Exposed for your convenience.

sqlLimiter.removeTerminator( sqlStatement )

• sqlStatement - Single SQL statement text to remove terminator from.

Returns sqlStatement string with terminator removed. Used by sql-limiter internally but exposed for your convenience

sqlLimiter.getStatementType( sqlStatement )

• sqlStatement - Single SQL statement text to get type keyword from.

Returns sqlStatement type keyword in lower case. This will be the first keyword of the SQL query, sans with and as for CTE queries.

console.log(sqlLimiter.getStatementType("SELECT * FROM ...")); // "select"
console.log(
  sqlLimiter.getStatementType("WITH foo AS (SELECT ...) INSERT INTO ... ")
); // "insert"
console.log(sqlLimiter.getStatementType("-- just a comment")); // undefined

Why

sql-limiter was initially created to enforce SQL limits in SQLPad.

In SQLPad, users run arbitrary SQL against a database, and the application needs to be protected from SELECT * FROM really_big_table queries.

Some database drivers support the ability to stream SQL results, and then terminate the query midstream. Most drivers/databases don't support this however, and for those that do it can be kind of tricky. It also isn't as efficient as telling the database the results are to be limited, as that may impact the query execution plan depending on the database.

How

The SQL statement is tokenized using the excellent moo package.

The resulting tokens are traversed to attempt to detect and enforce the limit.

This library is not a full fledged SQL parser. You may run into some edge cases depending on your target database. If you run into anything, please open an issue.

FETCH FIRST/NEXT n ROWS ONLY

FETCH FIRST was added into the SQL standard in SQL:2008. Markus Winand has a great guide written about this.

sql-limiter will look for FETCH FIRST <number> and FETCH NEXT <number> to detect the use of FETCH use. If neither are found, FETCH FIRST <number> ROWS ONLY will be added to the query.

The fetch strategy assumes that the target database honors the FETCH FIRST syntax alone, not requiring preceding ORDER BY or OFFSET clauses.

SQL Server Users Beware

While sql-limiter has a top strategy and fetch strategy, it most likely will not meet your needs if you are trying to limit SQL intended for SQL Server.

SQL Server's TOP is great for single SELECT queries, but becomes problematic for queries unioned together. TOP only applies to the SELECT clause it is used in, as opposed to acting on the entire unioned result set. For example:

-- For SQL Server, this query would return 1,000,005 rows
SELECT TOP 5 * FROM million_row_table
UNION ALL
SELECT * FROM million_row_table

-- In Postgres, this query returns 5 rows
SELECT * FROM million_row_table
UNION ALL
SELECT * FROM million_row_table
LIMIT 5

To achieve the same effect as LIMIT using TOP in SQL Server, you must wrap the query, and put the TOP in the wrapping query instead. This can be difficult without a proper SQL parser.

Instead of TOP, you could use FETCH FIRST in SQL Server 2012 and later, but is very strict! It requires preceding ORDER BY and OFFSET clauses to function, which also prove to be difficult to support and detect without a proper SQL parser (especially since variables and other things are supported in these clauses).

Contributing

Discover a query that isn't understood as it should be? Know of other terminators? Open an issue and let me know.

Pull requests for bugs and maintenance always welcome. Please open an issue before opening a PR for new functionality.

Resources for limiting SQL queries

• Use the Index Luke - Querying Top N Rows
• Postgres SELECT Synopsis
• IBM DB2 Fetch First
• Actian/Ingres Fetch First

License

MIT