This module provides access to an Oracle database connection pool via the oracledb module. It decorates the Fastify instance with an oracle property that is a connection pool instance.
When the Fastify server is shutdown, this plugin invokes the .close() method on the connection pool.
npm i fastify-oracle --save Add it to you project with register and you are done! This plugin will add the oracle namespace in your Fastify instance, with the following properties:
getConnection: the function to get a connection from the pool pool: the pool instance query: a utility to perform a query _without_ a transaction transact: a utility to perform multiple queries _with_ a transaction The plugin provides the basic functionality for creating a connection and executing statements such as
const fastify = require('fastify')() fastify.register(require('fastify-oracle'), { pool: { user: 'foo', password: 'bar', connectString: 'oracle.example.com:1521/foobar' } }) fastify.get('/db_data', async function (req, reply) { let connection try { connection = await this.oracle.getConnection() const { rows } = await connection.execute('SELECT 1 AS FOO FROM DUAL') return rows } finally { if (connection) await connection.close() } }) fastify.listen(3000, (err) => { if (err) { fastify.log.error(err) // Manually close since Fastify did not boot correctly. fastify.close(err => { process.exit(1) }) } // Initiate Fastify's shutdown procedure so that the plugin will // automatically close the connection pool. process.on('SIGTERM', fastify.close.bind(fastify)) })The query feature can be used for convenience to perform a query without a transaction
const fastify = require('fastify') fastify.register(require('fastify-oracle'), { pool: { user: 'travis', password: 'travis', connectString: 'localhost/xe' } }) fastify.post('/user/:username', (req, reply) => { // will return a promise, fastify will send the result automatically return fastify.oracle.query('SELECT * FROM USERS WHERE NAME = :name', { name: 'james' }) }) /* or with a callback fastify.oracle.query('SELECT * FROM USERS', function onResult (err, result) { reply.send(err || result) }) */See node-oracledb documentation for all available usage options.
The transact feature can be used for convenience to perform multiple queries with a transaction
const fastify = require('fastify') fastify.register(require('fastify-oracle'), { pool: { user: 'travis', password: 'travis', connectString: 'localhost/xe' } }) fastify.post('/user/:username', (req, reply) => { // will return a promise, fastify will send the result automatically return fastify.oracle.transact(async conn => { // will resolve to commit, or rollback with an error return conn.execute(`INSERT INTO USERS (NAME) VALUES('JIMMY')`) }) }) /* or with a callback fastify.oracle.transact(conn => { return conn.execute('SELECT * FROM DUAL') }, function onResult (err, result) { reply.send(err || result) } }) */ /* or with a commit callback fastify.oracle.transact((conn, commit) => { conn.execute('SELECT * FROM DUAL', (err, res) => { commit(err, res) }); }) */fastify-oracle requires an options object with at least one of the following properties:
pool: anoracledbpool configuration objectpoolAlias: the name of a pool alias that has already been configured. This takes precedence over thepooloption.client: an instance of anoracledbconnection pool. This takes precedence over thepoolandpoolAliasoptions.
Other options are as follows
name: (optional) can be used in order to connect to multiple oracledb instances. The first registered instance can be accessed viafastify.oracleorfastify.oracle.<dbname>. Note that once you register a named instance, you will not be able to register an unnamed instance.outFormat: (optional) sets theoutFormatof oracledb. Should be'ARRAY'or'OBJECT'. Default:'ARRAY'fetchAsString: (optional) the column data of specified types are returned as a string instead of the default representation. Should be an array of valid data types. Valid values are['DATE', 'NUMBER', 'BUFFER', 'CLOB']. Default[].
const fastify = require('fastify')() fastify .register(require('fastify-oracle'), { pool: { user: 'foo', password: 'bar', connectString: 'oracle.example.com:1521/ora1' }, name: 'ora1' }) .register(require('fastify-oracle'), { pool: { user: 'foo', password: 'bar', connectString: 'oracle.example.com:1521/ora2' }, name: 'ora2' }) fastify.get('/db_1_data', async function (req, reply) { let conn try { conn = await this.oracle.ora1.getConnection() const result = await conn.execute('select 1 as foo from dual') return result.rows } finally { if (conn) { conn.close().catch((err) => {}) } } }) fastify.get('/db_2_data', async function (req, reply) { let conn try { conn = await this.oracle.ora2.getConnection() const result = await conn.execute('select 1 as foo from dual') return result.rows } finally { if (conn) { conn.close().catch((err) => {}) } } })The oracledb instance is also available via fastify.oracle.db for accessing constants and other functionality:
fastify.get('/db_data', async function (req, reply) { let conn try { conn = await this.oracle.ora1.getConnection() const result = await conn.execute('select 1 as foo from dual', { }, { outFormat: this.oracle.db.OBJECT }) return result.rows } finally { if (conn) { conn.close().catch((err) => {}) } } })If needed pool instance can be accessed via fastify.oracle[.dbname].pool
Thanks to
- James Sumners, who is the original author of this plugin, for his work and transferring his repository to me.
- Vincit for his Travis Oracle work.