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 = TableName: 'users' columns: ColumnName: 'id' ColumnName: 'username' ColumnName: 'password' ;const bank = 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 > 1offset 0 rowsfetch next 50 rows only
- Non-record set paging
- pg dialect
select * from some-table where x > 1limit 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
; ; // from here forward sqlQuery will use postgres options
the following are the options set by setPostgres/setSqlServer
const postgresOptions = sqlStartChar: '"' sqlEndChar: '"' namedValues: true namedValueMarker: '$' markerType: 'number' dialect: 'pg' recordSetPaging: false;const sqlServerOptions = sqlStartChar: '[' sqlEndChar: ']' escapeLevel: 'table-alias' 'column-alias' namedValues: true namedValueMarker: ':' markerType: 'name' dialect: 'MS' recordSetPaging: false;
const query = ;
Get your SQL
const sql = query;
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 = ;
generates:
SELECT SUM(bank_account.balance) as balance_sumFROM bank_account as bank_accountGROUP 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 = ;
Update/Insert
const insert = bank;const update = bank;
- 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
SqlTable
classes from database (supports Sqlite and postgres)
Generate 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