Tuesday, 6 September, 2016 UTC
Summary
Node.js is an excellent platform for creating RESTful APIs that store and retrieve data using Oracle Database. node-oracledb is the driver maintained by Oracle that allows Node.js developers to connect to and utilize the database. As data crosses the boundaries between JavaScript and Oracle Database, the driver automatically converts it to native data types on either end. This post explores how that conversion process works for datetime values.
Please Note: This post is part of a series on working with dates in JavaScript, JSON, and Oracle Database. See that post for more details and links to other options.
Here’s an overview of what’s covered in this post:
- Intro to datetime handling with node-oracledb
- A basic “in bind” example
- Explicitly setting the session time zone
- Persisting and fetching datetime values
- Changing the default behavior when fetching datetime values
- Putting it all together: An example with Express and node-oracledb
Intro to datetime handling with node-oracledb
SQL execution and bind variables are the most common methods of moving data between Node.js and Oracle Database. When it comes to datetime values, the behavior of node-oracledb is the same regardless of the method used. The section in the documentation on result type mapping describes that behavior:
Internally, TIMESTAMP and DATE columns are fetched as TIMESTAMP WITH LOCAL TIME ZONE using OCIDateTime. When binding a JavaScript Date value in an INSERT statement, the date is also inserted as TIMESTAMP WITH LOCAL TIME ZONE using OCIDateTime.
While not a lengthy explanation, that’s still a lot to take in!
A basic “in bind” example
Bind variables can be thought of as placeholders for values in SQL and PL/SQL code. Just before execution of the code, values can be transferred in (bound in) from the JavaScript context to the code being executed. At the end of execution, values can be transferred back out (bound out) to the JavaScript context. It’s also possible to declare bind variables that work in both directions.
When values are bound in either direction, their data types are converted from one context to another. The following example demonstrates binding a Date in JavaScript into a TIMESTAMP WITH LOCAL TIME ZONE in SQL:
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"select to_char(:in_tswltz, 'dd-mon-yyyy hh24:mi:ss.ff tzr') as tswltz, " +
" sessiontimezone stz, " +
" dump(:in_tswltz) dump " +
"from dual",
{
in_tswltz: myDate
},
{
outFormat: oracledb.OBJECT
},
function(err, result) {
if (err) {throw err;}
console.log('JavaScript date: ', myDate);
console.log('result.rows[0].TSWLTZ:', result.rows[0].TSWLTZ);
console.log('result.rows[0].STZ: ', result.rows[0].STZ);
console.log('result.rows[0].DUMP: ', result.rows[0].DUMP);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
Here’s an overview of what’s going on in the script:
- Line 3: A variable named myDate is declared and initialized to the datetime value 01-jan-2016 00:00:00.123456 America/New_York. As this is a JavaScript date, the time zone will be picked up from my OS.
- Line 5: A one-off (non-pooled) connection to the database is obtained from the base class.
- Line 14: The connection’s execute method is used to execute a SQL statement.
- Lines 15-18: The SQL statement selects the following:
- The value of the bind variable – allows us to see the value in the SQL context. TO_CHAR is used to prevent any conversion of the datetime value going back to JavaScript.
- The session time zone – provides context and so we can see how the driver behaves as the value is changed.
- A dump of the bind variable – selected so we can verify the data type of the variable.
- Line 16: A single bind variable is specified as an “in” bind (the default) which maps the value of myDate from JavaScript into the bind variable in_tswltz in the SQL context. The binding process is done just prior to the execution of the statement.
- Lines 24-27: console.log() is used to show the value of myDate, as well as the values brought back from the execution of the SQL statement.
This is what I get when I run the script on my machine:
$ node date-bind-in-example.js JavaScript date: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) result.rows[0].TSWLTZ: 01-jan-2016 01:00:00.123000000 -04:00 result.rows[0].STZ: -04:00 result.rows[0].DUMP: Typ=231 Len=11: 120,116,1,1,6,1,1,7,84,212,192
Notice that the time zone offset of the date in JavaScript (-05:00) and the time zone offset of the datetime value in Oracle Database (-04:00) were off by an hour. Seem strange? Actually, they are the same datetime value, just seen through different lenses!
The session time zone is the clue to why they look different. My session time zone was implicitly set to a fixed time zone offset of -04:00, which was derived from my OS. When the value of the bind variable was accessed, it reflected the time according to the session time zone.
Finally, the output from the DUMP column confirms that the data type being bound in is TIMESTAMP WITH LOCAL TIME ZONE. This can be verified by looking up the type (231) the table of Oracle Built-in Data Types.
Remember: With TIMESTAMP WITH LOCAL TIME ZONE, the datetime value is converted to the database time zone and subsequent access reflects the session time zone.
Explicitly setting the session time zone
Folks in the US typically see datetime values through the lens of a time zone region that recognizes daylight savings time rather than a fixed offset. To set the session time zone when using node-oracledb, you can use either the environment variable ORA_SDTZ or an ALTER SESSION statement.
Here’s an example that sets ORA_SDTZ before rerunning the script:
$ export ORA_SDTZ='America/New_York' $ node date-bind-in-example.js JavaScript date: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) result.rows[0].TSWLTZ: 01-jan-2016 00:00:00.123000000 America/New_York result.rows[0].STZ: America/New_York result.rows[0].DUMP: Typ=231 Len=11: 120,116,1,1,6,1,1,7,84,212,192
Notice that the value of the TIMESTAMP WITH LOCAL TIME ZONE bind variable now matches the JavaScript date.
If you are unable to use environment variables, or if you want to change the time zone more dynamically, ALTER SESSION may come in handy. Running this script will give me the same output as above:
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"alter session set time_zone='America/New_York'",
function() {
connection.execute(
"select to_char(:in_tswltz, 'dd-mon-yyyy hh24:mi:ss.ff tzr') as tswltz, " +
" sessiontimezone stz, " +
" dump(:in_tswltz) dump " +
"from dual",
{
in_tswltz: myDate
},
{
outFormat: oracledb.OBJECT
},
function(err, result) {
if (err) {throw err;}
console.log('JavaScript date: ', myDate);
console.log('result.rows[0].TSWLTZ:', result.rows[0].TSWLTZ);
console.log('result.rows[0].STZ: ', result.rows[0].STZ);
console.log('result.rows[0].DUMP: ', result.rows[0].DUMP);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
}
);
Keep in mind that using connection.execute() to run an ALTER SESSION statement does incur the cost of a round trip to the database.
The following variation uses ALTER SESSION in the execution of a PL/SQL block. When coding in PL/SQL, we need to use EXECUTE IMMEDIATE to execute Data Definition Language (DDL):
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"begin " +
" execute immediate 'alter session set time_zone=''America/New_York'''; " +
// do work after execute immediate
"end;",
function(err, result) {
if (err) {throw err;}
// continue processing
}
);
}
);
The advantage of using PL/SQL is that we get the flexibility of the ALTER SESSION statement without the additional round trip.
One last option to consider is the use of a logon trigger to set the session time zone. This might be a good option if you can’t use environment variables and you don’t need the flexibility of using ALTER SESSION at the execution level:
create or replace trigger hr_logon_trigger after logon on hr.schema begin execute immediate 'alter session set time_zone=''America/New_York'''; end;
Persisting and fetching datetime values
Now that we’ve covered the general behavior of the driver and how you can set the session time zone, let’s explore what happens as we persist datetime values and then later fetch them back out.
We’ll start by creating a table to store the todo object that was created in the first part of this series. We’ll use one column for each of the different datetime data types to store the due property:
create table todos( id number generated always as identity, name varchar2(100), due_d date, due_ts timestamp, due_tswtz timestamp with time zone, due_tswltz timestamp with local time zone );
Here’s a script that inserts a todo object into the new table:
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"insert into todos ( " +
" name, " +
" due_d, " +
" due_ts, " +
" due_tswtz, " +
" due_tswltz " +
") values ( " +
" :name, " +
" :in_tswltz, " +
" :in_tswltz, " +
" :in_tswltz, " +
" :in_tswltz " +
")",
{
name: "Get milk",
in_tswltz: myDate
},
{
autoCommit: true
},
function(err, result) {
if (err) {throw err;}
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
I’ll run the script after setting the environment variable ORA_SDTZ as follows:
$ export ORA_SDTZ='America/New_York' $ node insert-todo.js
The following script can be used to fetch the value back out. Notice that I needed to cast the TIMESTAMP WITH TIME ZONE value as the driver doesn’t currently support fetching that data type.
var oracledb = require('oracledb');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
var dbConfig = require('./dbconfig.js');
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"select name, " +
" due_d, " +
" due_ts, " +
" cast (due_tswtz as timestamp with local time zone) due_tswtz, " +
" due_tswltz " +
"from todos",
{}, // no binds
{
outFormat: oracledb.OBJECT
},
function(err, result) {
if (err) {throw err;}
console.log(result.rows[0]);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
This is what I get when I run this script with the same session time zone as when the data was inserted:
$ export ORA_SDTZ='America/New_York'
$ node select-todo.js
{ NAME: 'Get milk',
DUE_D: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),
DUE_TS: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),
DUE_TSWTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),
DUE_TSWLTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) }
Everything looks perfect! But what would happen if a user fetched the data out with a different session time zone?
$ export ORA_SDTZ='America/Los_Angeles'
$ node select-todo.js
{ NAME: 'Get milk',
DUE_D: Fri Jan 01 2016 03:00:00 GMT-0500 (EST),
DUE_TS: Fri Jan 01 2016 03:00:00 GMT-0500 (EST),
DUE_TSWTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST),
DUE_TSWLTZ: Fri Jan 01 2016 00:00:00 GMT-0500 (EST) }
The date values for the DATE and TIMESTAMP columns are now off by a few hours. So what went wrong?
Again, the driver binds in and out using TIMESTAMP WITH LOCAL TIME ZONE. When the data was inserted, the value of the bind variable (in_tswltz) was mapped to the four due_* columns. The value reflected the then current session time zone, which was set to ‘America/New_York’. When the datetime value was converted for storage in the DATE and TIMESTAMP columns, the related time zone information was lost.
When the row was fetched back out, I explicitly converted the TIMESTAMP WITH TIME ZONE COLUMN to TIMESTAMP WITH LOCAL TIME ZONE. This can be done accurately regardless of the session time zone because the actual time zone information needed is included with the data type. However, the DATE and TIMESTAMP columns were implicitly converted to TIMESTAMP WITH LOCAL TIME ZONE, which is done using the session time zone.
Remember: Explicitly setting the session time zone to the correct setting when using node-oracledb is very important for consistent behavior, especially when working with DATE and TIMESTAMP data types!
Changing the default behavior when fetching datetime values
If you want to change the behavior of fetching datetime values, there are three main options: manual conversion, oracledb.fetchAsString, and fetchInfo. Keep in mind, however, that if you want to use the same ISO 8601 date format that JSON.stringify() uses by default, you may need to convert datetime values to UTC/GMT.
Here’s a logon trigger which sets the NLS_TIMESTAMP_TZ_FORMAT parameter to the ISO 8601 format used by JSON.stringify(). This trigger was enabled while running the examples that follow:
create or replace trigger hr_logon_trigger after logon on hr.schema begin execute immediate 'alter session set NLS_TIMESTAMP_TZ_FORMAT=''yyyy-mm-dd"T"HH24:MI:SS.FF3"Z"'''; end;
This example uses TO_CHAR to change the default datetime behavior:
var oracledb = require('oracledb');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
var dbConfig = require('./dbconfig.js');
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"select name, " +
" to_char(from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC') due_d, " +
" to_char(from_tz(due_ts, 'America/New_York') at time zone 'UTC') due_ts, " +
" to_char(due_tswtz at time zone 'UTC') due_tswtz, " +
" to_char(due_tswltz at time zone 'UTC') due_tswltz " +
"from todos",
{}, // no binds
{
outFormat: oracledb.OBJECT
},
function(err, result) {
if (err) {throw err;}
console.log(result.rows[0]);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
In to_char.js, datetime values are manually converted to strings before they reach the driver.
This is what I get when I run the script:
$ node to_char.js
{ NAME: 'Get milk',
DUE_D: '2016-01-01T05:00:00.000Z',
DUE_TS: '2016-01-01T05:00:00.123Z',
DUE_TSWTZ: '2016-01-01T05:00:00.123Z',
DUE_TSWLTZ: '2016-01-01T05:00:00.123Z' }
As you can see, the dates have been properly converted to UTC and are formatted as ISO 8601 strings.
If you’d rather not use TO_CHAR, you can use the oracledb.fetchAsString property of the base class. This is a global setting that will affect all bindings.
var oracledb = require('oracledb');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
var dbConfig = require('./dbconfig.js');
oracledb.fetchAsString = [oracledb.DATE];
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"select name, " +
" from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC' due_d, " +
" from_tz(due_ts, 'America/New_York') at time zone 'UTC' due_ts, " +
" due_tswtz at time zone 'UTC' due_tswtz, " +
" due_tswltz at time zone 'UTC' due_tswltz " +
"from todos",
{}, // no binds
{
outFormat: oracledb.OBJECT
},
function(err, result) {
if (err) {throw err;}
console.log(result.rows[0]);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
Executing fetchAsString.js will produce the same output as to_char.js.
In addition to oracledb.fetchAsString, there’s a fetchInfo property of the options object that can be used at the execution level. This property can also be used to override the oracledb.fetchAsString settings by specifying another type or oracledb.DEFAULT.
var oracledb = require('oracledb');
var myDate = new Date(2016, 00, 01, 00, 00, 00, 123);
var dbConfig = require('./dbconfig.js');
oracledb.getConnection(
dbConfig,
function(err, connection) {
if (err) {throw err;}
connection.execute(
"select name, " +
" from_tz(cast (due_d as timestamp), 'America/New_York') at time zone 'UTC' due_d, " +
" from_tz(due_ts, 'America/New_York') at time zone 'UTC' due_ts, " +
" due_tswtz at time zone 'UTC' due_tswtz, " +
" due_tswltz at time zone 'UTC' due_tswltz " +
"from todos",
{}, // no binds
{
outFormat: oracledb.OBJECT,
fetchInfo: {
DUE_D: {type: oracledb.STRING},
DUE_TS: {type: oracledb.STRING},
DUE_TSWTZ: {type: oracledb.STRING},
DUE_TSWLTZ: {type: oracledb.STRING}
}
},
function(err, result) {
if (err) {throw err;}
console.log(result.rows[0]);
connection.close(function(err) {
if (err) {throw err;}
});
}
);
}
);
Putting it all together: An example with Express and node-oracledb
The following example uses Express to create a web server and node-oracledb to interact with Oracle Database. Hopefully, it will provide a better picture of how datetime values move through RESTful APIs. Keep in mind that this is not a complete RESTful API. I’ve only implemented handlers for POST & GET and there’s no authentication, authorization, validations, proper error handling, logging, or instrumentation.
I did, however, create a connection pool because no RESTful API should be using one-off connections!
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var express = require('express');
var bodyParser = require('body-parser');
var app = express();
var dateTimeRegExp = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d{3})?Z$/;
var reviver = function(key, value) {
if (typeof value === 'string' && dateTimeRegExp.test(value)) {
return new Date(value);
} else {
return value;
}
};
// The bodyParser middleware accepts a reviver function that it passes along
// to JSON.parse when the body is parsed.
app.use(bodyParser.json({
reviver: reviver
}));
app.post('/api/todos', function(req, res, next) {
var todo = req.body;
// This is normally where work, such as validation and manipulation, is done.
// Becuase of the reviver above, at this point the JSON body has been parsed
// into native JavaScript types and todo.due is a proper date.
// The todo is now just like it was in the browser, only here, the due date
// will be reflected in the time zone of the Node.js server's time zone
// (derived from the OS).
var insertStmt = '' +
'insert into todos ( ' +
' name, ' +
' due_d, ' +
' due_ts, ' +
' due_tswtz, ' +
' due_tswltz ' +
') values ( ' +
' :name, ' +
' :due, ' +
' :due, ' +
' :due, ' +
' :due ' +
') ' +
'returning id into :id';
oracledb.getConnection(function(getConnErr, conn) {
if (getConnErr) {next(getConnErr); return;}
conn.execute(
insertStmt,
{
name: todo.name,
due: todo.due,
id: {
dir: oracledb.BIND_OUT,
type: oracledb.NUMBER
}
},
{
autoCommit: true
},
function(executeErr, result) {
if (executeErr) {next(executeErr); return;}
todo.id = result.outBinds.id[0];
conn.release(function(releaseErr){
if (releaseErr) {next(releaseErr); return;}
res.status(201).json(todo);
});
}
);
});
});
app.get('/api/todo/:id', function(req, res, next) {
var todoId = Number(req.params.id);
var selectStmt = '' +
'select id, ' +
' name, ' +
' due_d, ' +
' due_ts, ' +
' cast (due_tswtz as timestamp with local time zone) due_tswtz, ' +
' due_tswltz ' +
'from todos ' +
'where id = :id';
oracledb.getConnection(function(getConnErr, conn) {
if (getConnErr) {next(getConnErr); return;}
conn.execute(
selectStmt,
{
id: todoId
},
{
outFormat: oracledb.OBJECT
},
function(executeErr, result) {
var todo = {};
if (executeErr) {next(executeErr); return;}
todo.id = result.rows[0].ID;
todo.name = result.rows[0].NAME;
// For the due property, it's possible to use DUE_D, DUE_TS, DUE_TSWTZ,
// or DUE_TSWLTZ as they should all be the same except for the fact that
// only the TIMESTAMP values have subsecond timings.
todo.due = result.rows[0].DUE_TSWLTZ;
conn.release(function(releaseErr){
if (releaseErr) {next(releaseErr); return;}
// The json method below will call JSON.stringify on the todo before
// sending it back down to the client.
res.status(200).json(todo);
});
}
);
});
});
oracledb.createPool(
dbConfig,
function(err) {
if (err) {throw err;}
// Connection pool to db created successfully, open web server
app.listen(3000, function() {
console.log('App started on localhost:3000');
});
}
);
I typically run my Node.js servers on GMT/UTC (you’ll have to trust that I’ve set my operating system’s time zone accordingly before running Node.js). I also like to set my session time zone to GMT/UTC whenever possible.
Here’s how I would start the web server:
$ export ORA_SDTZ='UTC' $ node demo-server.js App started on localhost:3000
I can simulate a browser POSTing a todo to the API by opening another terminal and testing via cURL:
$ curl -X "POST" "http://localhost:3000/api/todos" \
> -H "Content-Type: application/json" \
> -d "{\"name\":\"Get milk\",\"due\":\"2016-01-01T05:00:00.123Z\"}"
{"name":"Get milk","due":"2016-01-01T05:00:00.123Z","id":23}
The following is an explanation of what happens in the script during a POST:
- Express accepts the incoming POST request and applies the JSON bodyParser middleware the app was configured to use (line 17). The JSON bodyParser was configured to use a reviver function (line 18) which will check for and properly parse incoming dates into JavaScript dates.
- Express then invokes the handler for the POST request (lines 21-77) and passes along the now parsed todo via req.body.
- node-oracledb is used to execute an insert statement on the database (lines 51-75). This can be thought of as a three-step process:
- Just before execution of the statement, the in bind variables (name and due) are bound into the SQL context. Because name is a String in JavaScript it is bound in as a VARCHAR2 and because due is a Date it is bound in as a TIMESTAMP WITH LOCAL TIME ZONE.
- The statement is executed.
- After execution, the value of the out bind (id) is transferred back to the JavaScript context as a Number because that’s how it was configured (line 58).
- The execute callback is then invoked (lines 64-74). In the callback, the id of the newly inserted record is attached as a property on the todo in JavaScript. Then res.json() is used to serialize and send the todo back to the client.
If you look back at the cURL command, you’ll see the insert was successful and the new todo’s id was 23.
I can simulate a browser GETing the todo back out as follows.
$ curl -X "GET" "http://localhost:3000/api/todo/23"
{"id":23,"name":"Get milk","due":"2016-01-01T05:00:00.123Z"}
This is what happens in the script during a GET request:
- Express accepts the incoming GET request and invokes the handler (lines 79-125). The id from the URL is passed in via req.params.id (line 80) and that value is bound into the SQL query (line 97).
- The query is executed.
- The results from the SQL context are mapped to JavaScript types and used to construct the todo object to be returned to the client (lines 107-113).
- Finally, res.json() is used to serialize and send the todo back to the client.
I hope you now have a better understanding of how node-oracledb works with datetime values.