fluent-sql
v3.0.5
Published
Fluent SQL lib
Downloads
21
Maintainers
Readme
README
This library basically takes your fluent SQL and generates SQL strings and replacement
What is this repository for?
- Quick summary I needed a javascript version of the same library I wrote for java (mainly because I just liked the flow)
- Examples
Create your table
const users = new SqlTable({
TableName: 'users',
columns: [{ ColumnName: 'id' }, { ColumnName: 'username' }, { ColumnName: 'password' }],
});
const bank = new SqlTable({
TableName: 'bank_account',
columns: [{ ColumnName: 'id' }, { ColumnName: 'user_id' }, { ColumnName: 'account_no' }, { ColumnName: 'balance' }],
});
NOTE: column names will be changed to camelCase from snake_case
Create your query. SqlQuery takes an options object.
SqlQuery object to copy options from OR
an object of options
- sqlStartChar - character used to escape names
- default is '['
- sqlEndChar - character used to end escaped names
- default is ']'
- escapeLevel - array of zero or more ('table-alias', 'column-alias')
- default is ['table-alias', 'column-alias']
- namedValues - boolean, if false will use ? for the values and just return an array of values
- default true
- namedValueMarker - character, will use this with named values in the generated SQL (example: where foo = (:value0))
- default is ':'
- markerType - 'number' or 'name' if number will generate :1, ..., :n number is 1 based
- default is 'name'
- dialect - 'pg' = postgreSQL, 'MS' = SQLServer subtle changes to the generated SQL (TOP vs. LIMIT for
example)
- default is 'MS'
- recordSetPaging - true/false
- default is false
- sqlStartChar - character used to escape names
Non-record set paging
- MS dialect
select * from some-table where x > 1
offset 0 rows
fetch next 50 rows only
- Non-record set paging
- pg dialect
select * from some-table where x > 1
limit 50 offset 0
- Record Set Paging
SELECT * FROM (
SELECT *, row_number() OVER (ORDER BY name ASC) as Paging_RowNumber FROM (
select * from some-table where x > 1
) base_query
) as detail_query WHERE Paging_RowNumber BETWEEN 0 AND 50
import { setPostgres, setSqlServer } from 'fluent-sql';
setPostgres(); // from here forward sqlQuery will use postgres options
the following are the options set by setPostgres/setSqlServer
export const postgresOptions = {
sqlStartChar: '"',
sqlEndChar: '"',
namedValues: true,
namedValueMarker: '$',
markerType: 'number',
dialect: 'pg',
recordSetPaging: false,
};
export const sqlServerOptions = {
sqlStartChar: '[',
sqlEndChar: ']',
escapeLevel: ['table-alias', 'column-alias'],
namedValues: true,
namedValueMarker: ':',
markerType: 'name',
dialect: 'MS',
recordSetPaging: false,
};
const query = new SqlQuery()
.select(users.id, users.username, users.password)
.from(users)
.where(users.username.eq('jsmith'));
Get your SQL
const sql = query.genSql(decryptFunction, maskingFunction);
Sql looks like the following (MS Dialect)
{
fetchSql:
'SELECT\n[users].id as [id],\n[users].username as [username],\n[users].password as [password]\nFROM\nusers as [users]\nWHERE [users].username = (:username0)',
countSql: undefined,
hasEncrypted: false,
values: {
username0: 'jsmith'
}
}
Sql looks like the following (Postgres)
{
fetchSql:
'SELECT\n"users".id as "id",\n"users".username as "username",\n"users".password as "password"\nFROM\nusers as "users"\nWHERE "users".username = ($1)',
countSql: undefined,
hasEncrypted: false,
values: [ 'jsmith' ]
}
Decrypt & Masking functions are just a function that takes 2 parameters, SqlColumn and boolean on weather or not to use a fully qualified column name (ie. table.col), you can do anything in these and return null or a SQL literal to insert for that column in the generated SQL. Both functions can be NULL
The sql returned is an object
- fetchSql - the actual sql statement to fetch the data
- countSql - a count(*) with the same where statement
- hasEncrypted - boolean to say if the encrypted function ever returned something other than null
- values - object of the values you used in the query
Aggregate example
const query = new SqlQuery().select(bank.balance.sum().by(bank.userId)).from(bank);
generates:
SELECT SUM(bank_account.balance) as balance_sum
FROM bank_account as bank_account
GROUP BY bank_account.user_id
Limits & paging
- top, limit, take, & pageSize = all set the record count returned the last called wins
- offet & skip = how many records to skip
- page = cannot be used with offset or skip MUST have a top, limit, take, or pageSize
const query = new SqlQuery()
.select(users.id)
.page(5)
.pageSize(10);
Update/Insert
const insert = bank.insert({ id: 1, userId: 1, accountNo: 1, balance: 1000.0 });
const update = bank.update({ id: 1, balance: 1000.0 });
- insert/update structure
- sql - sql for INSERT/UPDATE
- values - object of the values used in the sql
Look through the tests for more examples, the tests should have every possible option exercised
How do I get set up?
npm install fluent-sql
Generate SqlTable
classes from database (supports Sqlite and postgres)
npm i -D simple-db-migrate
(I used my command line parsing from this module)npm i -D sqlite3
ornpm i -D pg
if you are not using one of these already- exec
fluent-sql-gen
- command line options
- --verbose, -v
toggle on
- --dialect, -d
one of [pg, sqlite]
- --database, -db
string
- --user, -u
string
- --password, -p
string
- --host, -h
string
- --port
number
- --verbose, -v
- defaults are
- --dialect=sqlite -db db.sqlite
Other npm packages
https://www.npmjs.com/package/simple-db-migrate
- dead simple database migrations
change history
did a terrible job up till now on this
3.0.0
- added TypeScript, entire source is now ts
2.6.0
- added 'fluent-sql-gen' to create
SqlTable
classes from database
- added 'fluent-sql-gen' to create
2.5.0
- Completely changed the generated SQL for paging.
- Added recordSetPaging option to get old behavior
TODO:
- add outer join
- add generator for tables/columns