- Notifications
You must be signed in to change notification settings - Fork2.2k
Recipes
Specify PostgreSQL version that database you are using is compatible with protocol-wise usingversion option, e. g.:
constknex=require('knex')({client:'pg',version:'7.2',connection:{host:'127.0.0.1',user:'your_database_user',password:'your_database_password',database:'myapp_test'}});
Note that value ofversion option should be not the version of the database that you are using, but version of PostgreSQL that most closely matches functionality of the database that you are using. If not provided by database vendor, try using '7.2' as a baseline and keep increasing (within the range of existing PostgreSQL versions) until it starts (or stops) working.
There are also known incompatibilities with migrations for databases that do not support select for update. Seehttps://github.com/tgriesser/knex/issues/2002 for a workaround.
{encrypt: true} should be included in options branch of connection configuration:
knex({client :'mssql',connection:{database:'mydatabase',server:'myserver.database.windows.net',user:'myuser',password:'mypass',port:1433,connectionTimeout:30000,options:{encrypt:true}}});
See all of node-mssql's connection options
exports.up=(knex)=>{returnknex.schema.createTable('foo',(table)=>{table.increments('id');table.specificType('fulltext','tsvector');table.index('fulltext',null,'gin');););
After you build the SQLCipher source and the npm SQLite3 package, and encrypt your DB (look elsewhere for these things), then anytime you open your database, you need to provide your encryption key using the SQL statement:
PRAGMA KEY='secret'
This PRAGMA is more completely documented in the SQLCipher site. When working with Knex this is best done when opening the DB, via the following:
constmyDBConfig={client:"sqlite3",connection:{filename:"myEncryptedSQLiteDbFile.db"},pool:{afterCreate:function(conn,done){conn.run("PRAGMA KEY = 'secret'");done();}}};constknex=require('knex')(myDBConfig);
Of course embedding the key value in your code is a poor security practice. Instead, retrieve the 'secret' from elsewhere.
The key Knex thing to note here is the "afterCreate" function. This is documented in the knexjs.org site, but is not in the Table of Contents at this time, so do a browser find when on the site to get to it. It allows auto-updating DB settings when creating any new pool connections (of which there will only ever be one per file for Knex-SQLite).
If you don't use the "afterCreate" configuration, then you will need to run a knex.raw statement with each and every SQL you execute, something like as follows:
returnknex.raw("PRAGMA KEY = 'secret'").then(()=>knex('some_table').select().on('query-error',function(ex,obj){console.log("KNEX select from some_table ERR ex:",ex,"obj:",obj);})});
In case you would like to use Knex.js changelog functionality to ensure your environments are only seeded once, but don't want to mix seed files with migration files, you can specify multiple directories as a source for your migrations:
awaitknex.migrate.latest({directory:['src/services/orders/database/migrations','src/services/orders/database/seeds'],sortDirsSeparately:true,tableName:'orders_migrations',schemaName:'orders',})
awaitknex.transaction(trx=>{asyncfunctionstuff(){trx.rollback(newError('Foo'));};stuff().then(()=>{// do something});});
Or alternatively:
try{awaitknex.transaction(trx=>{asyncfunctionstuff(){trx.rollback(newError('always explicit rollback this time'));}stuff();});// transaction was committed}catch(err){// transaction was rolled back}
(note that promise forknex.transaction resolves after transaction is rolled back or committed)
In order to generate query along the lines of
SELECT"firstName","lastName","status"FROM"userInfo"WHERE"status"='active'AND ("firstName" ILIKE'%Ali%'OR"lastName" ILIKE'%Ali%');
you need to use following approach:
queryBuilder.where('status',status.uuid).andWhere((qB)=>qB.where('firstName','ilike',`%${q}%`).orWhere('lastName','ilike',`%${q}%`))
How to call and retrieve output from an oracle stored procedure
constoracle=require('oracledb');constbindVars={input_var1:6,input_var2:7,output_var:{dir:oracle.BIND_OUT},output_message:{dir:oracle.BIND_OUT}};constsp='BEGIN MULTIPLY_STORED_PROCEDURE(:input_var1, :input_var2, :output_var, :output_message); END;';constresults=awaitknex.raw(sp,bindVars);console.log(results[0]);// 42console.log(results[1]);// 6 * 7 is the answer to life