Stl - Sql function tagged template literal library
Stl
is a JS library that simplifies the creation of parameterized
SQL statements. It shares the interface and a significant portion
of logic from porsager's postgres.js
for constructing both
safe and unsafe SQL. The key distinction with Stl
is that it doesn't
handle database connectivity. This gives developers the freedom to
combine it with their preferred database library.
This library exists so that we can build MVP and prototypes with little
to no friction when working with databases. It is biased towards
sqlite
being the storage layer of choice.
Quick start
stl
Install $ npm install @m5nv/stl
Use it
For stl
to work effectively, it requires integration with a database
library of your choice. Essentially, stl
is compatible with any
database library that supports parameterized SQL statements. In the
code snippet below, simply swap out <your-favorite-database-library>
with the actual database library you're using.
import stl from '@m5nv/stl'
import db from '<your-favorite-database-library>'
const sql = stl({debug: false});
const name = "Mur", age = 60;
const query = sql`
select
name,
age
from users
where
name like ${name + "%"}
and age > ${age}
`;
const result = await db.all(query.string, query.parameters);
Development and testing
- run
npm i
to install dev dependencies -
cd test
&&npm link @m5nv/stl
to link tosrc
in development -
npm run test
||npm run test:coverage
to test
Notes
identifiers and keywords in SQL:
- 'keyword' A keyword in single quotes is a
string literal
. - "keyword" A keyword in double-quotes is an
identifier
. - identifier is a string that names an object/entity.
Overview of interpolation syntax
stl can interpolate the following use cases:
Interpolation syntax | Usage | Example |
---|---|---|
${ sql`` } |
for keywords or sql fragments | sql`SELECT * FROM users ${sql`order by age desc` }` |
${ sql(string) } |
for identifiers | sql`SELECT * FROM ${sql('table_name')` |
${ sql([] or {}, ...) } |
for helpers | sql`INSERT INTO users ${sql({ name: 'Peter'})}` |
${ 'somevalue' } |
for literal values | sql`SELECT * FROM users WHERE age = ${42}` |
Queries
Consult porsager's queries section as a reference. Please file a bug report if you find any discrepancy with the interpolation.
porsager's
postgres library
Deviation from Error
reporting from stl
layer
Remove origin of Rationale:
- the original implementation's optimization leads to faulty origin
- probably best implemented at the application layer
- Read more here
cause
of Error
instead of in code
Error codes are specfied in Rationale:
- reuse platform provided ways and means...
Remove code for transforming data
Rationale:
- new code is unlikely to use this feature (?)
- better handled at the application layer
References
Database security and SQL Injection
- SQL Injection Attack: What it is and how to prevent it
- Wikipedia: SQL Injection
- OWASP: SQL Injection Prevention
- Database Security: User Management
The no ORM camp
SQLite
- JSON improvements
- Virtual columns to speed up json data query
- Retrieving related rows in a single query
- Passing arrays as parameters using json trick
- SQLite CLI