pg-query-formatter
A simple, lightweight, and flexible way to format and escape postgres queries using a printf-like syntax.
$ npm install pg-query-formatter --save
var Query = ;var sql = 'SELECT * FROM %I WHERE %I = %L' 'teachers' 'name' 'george'; sql;// "SELECT * FROM teachers WHERE name = 'george'"sql;// { text: 'SELECT * FROM teachers WHERE name = $1', values: [ 'george' ] }
Format Specifiers
%s
– String (unescaped)%I
– Identifier%L
– Literal (string or number)%Q
– Subquery%(fmt)
: Object%(%I = %L)
– Assignment lists{id: 5, name: 'george'}
%(%s %s)
– Column definitions{id: 'integer', name: 'text'}
Constructors
new Query()
new Query(string fmt, values...)
new Query.List(string separator)
Methods
append(string fmt, values...)
toString()
toParam()
Examples
// 1. Selectvar sql = 'SELECT * FROM %I WHERE name = %L' 'teachers' 'George';console;//>> SELECT * FROM teachers WHERE name = 'George' // 2. Array Expansionvar sql = 'SELECT * FROM teachers WHERE name IN (%L)' 'George' 'Jorge' 'Georgio';console;//>> SELECT * FROM teachers WHERE name IN ('George', 'Jorge', 'Georgio') // 3. Complex Selectvar teachers = 'SELECT * FROM %I WHERE name = %L' 'teachers' 'George';var students = 'SELECT * FROM %I WHERE name = %L' 'students' 'George';var all = '%Q UNION ALL %Q' teachers students;console;//>> SELECT * FROM teachers WHERE name = 'George' UNION ALL SELECT * FROM students WHERE name = 'George' // 4. Query Listsvar where = ' AND '; // make a list of queries separated by ' AND 'wherewherevar sql = 'SELECT %I FROM teachers WHERE %Q' 'id' 'name' where;console;//>> SELECT id, name FROM teachers WHERE age > 20 AND age < 30 // 5. Updatevar sql = 'UPDATE people SET %(%I = %L) WHERE id = %L' name: 'George' age: 25 pet_id: null 4; console;//>> UPDATE people SET name = 'George', age = 25, pet_id = NULL WHERE id = 4
License
Copyright © 2014 Brandon Reavis & Contributors
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at: http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.