Oracle Database Driver (another SQL solution for Meteor)


#1

Recently we published another SQL package metstrike:meteor-oracle, which allows developers to write Meteor applications for Oracle the same way as if they were writing them for MongoDB. The only difference is that instead of Mongo.Collection they need to use Oracle.Collection. On the client side the Oracle.Collection behaves 100% the same as Mongo.Collection, on the server side the MongoDB operations are translated into SQL commands and sent to Oracle database through npm package node-oracledb.

Example 1: Create Oracle.Collection and use it the same way as Mongo.Collection

var coll = new Oracle.Collection("todos");

coll.insert({name: "Get milk", priority:1, userId: "Jane"});

var rows = coll.find({priority:1}).fetch();

console.log(rows);

The example above executes the following SQL commands in Oracle database:

INSERT INTO "todos" ("name", "priority", "userId", "_id") VALUES (:0, :1, :2, :3) 
Parameters: [ 'Get milk', 1, 'Jane', '46662dSw2KkzpNXqo' ]

SELECT * FROM (SELECT * FROM "todos") WHERE "priority" = 1

And will print the following rows result:

[ { _id: '46662dSw2KkzpNXqo', name: 'Get milk', priority: 1, userId: 'Jane' } ]

Example 2: Connect to a different Oracle account

Oracle.setDefaultOracleOptions(
    {user: "scott", 
    password: "tiger", 
    connectString : "host:/XE"
    });

Example 3: Turn on debug mode which prints all the executed SQL statements

Oracle.setDefaultOracleOptions({sqlDebug: true});

Example 4: Prevent automatic changes in db schema (turn off elastic data model, turn on strict mode)

Oracle.setDefaultOracleOptions({strict: true});

Prevents adding or changing any table or trigger.

Example 5: Prevent adding or changing any DB object, except oplog table and oplog triggers

Oracle.setDefaultOracleOptions({strictAllowOplogAndTriggers: true});

Releases

0.2.0 (2/1/2015)

  • Connectivity to external Oracle databases
  • Strict mode that prevents automatic changes in db schema (turns off elastic data model)
  • Automatic creation of OPLOG table and METEOR_PKG package
  • Removed dependency on DBMS_FLASHBACK

0.1.0 (12/15/2015)

Features:

  • Oracle.Collection class which behaves the same as Mongo.Collection
  • SQL Generator translating mongo style operations into SQL language
    • implemented in OracleCollection class
    • find(), findOne() operations are translated into SELECT … FROM …
      • most of the selector operators are supported
        • value operators: $not, $eq, $ne, $exists
        • element operators: $lt, $gt, $lte, $gte, $like, $mod, $in, $nin, $regex
        • logical operators: $and, $or, $nor
        • other operators: $where, $comment
      • sorter is supported
      • field selection is supported
      • skip and limit options are supported
      • sqlScn option has been added to support flashback queries
    • insert() operations are translated into INSERT INTO …
    • remove() operations are translated into DELETE FROM …
    • update() operations are translated into UPDATE … SET …
      • basic operators $inc, $set, $unset are supported
    • drop() operations are translated into DROP TABLE
    • ensureIndex() operations are translated into CREATE INDEX
    • drop() operations are translated into DROP TABLE
    • dropIndex() operations are translated into DROP INDEX
  • Elastic Data Model
    • the driver automatically creates and modifies data tables based on structure and size of data
    • the primary key constraint and related index is automaticaly created
    • the new columns are added automatically if needed
    • the size of the columns will be automatically augmented in new data would not fit
    • the type of columns gets automatically modified when possible
    • the NULL/NOT NULL constraint is automatically maintained
    • automatic conversion of boolean values to varchar2 (Oracle doesn’t support boolean in their SQL language)
  • Oplog Tailing
    • the driver creates oplog table in the meteor schema
    • automatic creation and maintenance of database triggers that populate the oplog table in real time

Restrictons:

  • column data types have to be consistent
  • nested data structures are not suported
  • the strict mode is not implemented yet
  • some operators in find() selector are not implemented yet
    • $maxDistance, $all, $near, $options, regexp inside $in or $nin, $size, $type
  • some operators in update() selector are not implemented yet
    • $setOnInsert, $push, $pushAll, $addToSet, $pop, $pull, $pullAll, $rename, $bit, $elemMatch

Approach

There are several other projects trying to integrate Meteor with SQL database and this is just another attempt to fit the concept of relational database into the architecture of Meteor. The current approach allows developers to write Meteor applications the same way as if they were writing them for MongoDB. The only difference is that instead of Mongo.Collection they need to use Oracle.Collection. All the remaining code stays the same and the application should work. No database tables or indexes have to be created by developer, the driver will create and maintain them all automatically.Similarly no database triggers to populate the oplog table have to be created manually, the driver will generate them automatically whenever a new collecton is created and it will regenerate them whenever a new column is added to the collection.

The simlicity and benefits of this approach can be demonstrated on existing sample Meteor applications like TODOS which can be very easily converted to work with Oracle. There is a caveat though, the developers need to be aware of current feature restrictions (and extensions) of this package. Not all features (e.g. the nested data structures) are currently supported and in the future some innovative features (e.g. the reactive $join operator) may be available in Oracle collections, while still not available in Mongo.

Oracle database provides a lot of rich and robust features, that are used by thousands of enterprises accros the world. A few examples in no particular order are: inner and outer joins (duh), views, bitmap indexes, library of packages, analytical and window queries, full text indexing, high availability, partitioning, materialized views, CLOBs/BLOBs, etc. The most useful features might be gradually exposed in this package allowing developers to incorporate them into their enterprise applications.

The current implementation is trying to reuse as much of existing Meteor code as possible so that it could be easily maintained in the future. The core objects (Oracle.Collection, OracleConnection) are inheriting most of the functionality from their mongo counterparts (Mongo.Collection, MongoConnection). In fact, the Oracle.Collection on the client side behaves 100% the same as Mongo.Collection. All the modifications are implemented on the server side only.

This package is dependent on node-oracledb, the Oracle Database driver for Node.js maintained by Oracle Corp. All the SQL queries and commands are being sent to this module, which handles all the communication with the Oracle database.

Future Improvements

  • use node-oracledb connection pool
  • use oracle resultsets to implement collection cursor
  • refresh table meta-data after any error occurred (maybe separate ORA errors should be considered)
  • refresh table meta-data after any DDL change is made by an external entity
  • support strict mode (driver will not make any DDL changes in the database)
  • support nested data structures
  • design reactive joins

Get the Idea

Let’s get back to the simple example we listed at the top:

var coll = new Oracle.Collection("todos");

coll.insert({name: "Get milk", priority:1, userId: "Jane"});

var rows = coll.find({priority:1}).fetch();

console.log(rows);

The meteor-oracle driver will not only generate the insert and select statements, it will also create database table (if it doesn’t exist yet), its primary key constraint, and the trigger that will populate the oplog table. The complete list of generated SQL statements looks as follows:

create table "todos" ("_id" varchar2(17) not null)

alter table "todos" add constraint "todos_PK" primary key ("_id")

INSERT:  todos { name: 'Get milk', priority: 1, userId: 'Jane', _id: '46662dSw2KkzpNXqo' } { safe: true }

alter table "todos" add ("name" varchar2(8) not null) []
alter table "todos" add ("priority" number(1) not null) []
alter table "todos" add ("userId" varchar2(4) not null) []

create or replace trigger "todos_trg"
after insert or update or delete
on "todos"
for each row
declare
	op varchar2(1);
	ns varchar2(200);
	o varchar2(4000);
	o2 varchar2(4000);
begin
	IF INSERTING THEN
		op := 'i';
		ns := 'meteor@localhost/XE'||'.'||'todos';
		o := '';
		o := o || '"_id": "'||replace(replace(:NEW."_id", chr(10), '\n'), '"', '\"')||'"';
		o := o || ', ';
		o := o || '"name": "'||replace(replace(:NEW."name", chr(10), '\n'), '"', '\"')||'"';
		o := o || ', ';
		o := o || '"priority": '||nvl(meteor_pkg.js_number(:NEW."priority"), 'null');
		o := o || ', ';
		o := o || '"userId": "'||replace(replace(:NEW."userId", chr(10), '\n'), '"', '\"')||'"';
		o := '{'||o||'}';
		o2 := null;
		insert into "oplog" ("id", "ts", "scn", "tr", "v", "op", "ns", "o", "o2")
		values ("oplog_seq".nextval, current_timestamp, dbms_flashback.get_system_change_number, dbms_transaction.local_transaction_id, 2, op, ns, o, o2);
	ELSIF UPDATING THEN
		op := 'u';
		ns := 'meteor@localhost/XE'||'.'||'todos';
		o := '';
		IF (:NEW."name" <> :OLD."name" OR (:NEW."name" IS NOT NULL AND :OLD."name" IS NULL) 
		        OR (:NEW."name" IS NULL AND :OLD."name" IS NOT NULL)) THEN 
		    IF o is not null THEN  := o || ', '; END IF; 
            o := o || '"name": "'||replace(replace(:NEW."name", chr(10), '\n'), '"', '\"')||'"'; 
        END IF;
		IF (:NEW."priority" <> :OLD."priority" OR (:NEW."priority" IS NOT NULL AND :OLD."priority" IS NULL) 
		        OR (:NEW."priority" IS NULL AND :OLD."priority" IS NOT NULL)) THEN 
		    IF o is not null THEN o := o || ', '; END IF; 
		    o := o || '"priority": '||nvl(meteor_pkg.js_number(:NEW."priority"), 'null'); 
		END IF;
		IF (:NEW."userId" <> :OLD."userId" OR (:NEW."userId" IS NOT NULL AND :OLD."userId" IS NULL) 
		        OR (:NEW."userId" IS NULL AND :OLD."userId" IS NOT NULL)) THEN 
		    IF o is not null THEN o := o || ', '; END IF; 
		    o := o || '"userId": "'||replace(replace(:NEW."userId", chr(10), '\n'), '"', '\"')||'"'; 
        END IF;
		IF o is not null THEN
			o := '{"$set": {'||o||'}}';
		o2 := '';
		o2 := o2 || '"_id": "'||replace(replace(:OLD."_id", chr(10), '\n'), '"', '\"')||'"';
		o2 := '{'||o2||'}';
			insert into "oplog" ("id", "ts", "scn", "tr", "v", "op", "ns", "o", "o2")
			values ("oplog_seq".nextval, current_timestamp, dbms_flashback.get_system_change_number, dbms_transaction.local_transaction_id, 2, op, ns, o, o2);
		END IF;
	ELSIF DELETING THEN
		op := 'd';
		ns := 'meteor@localhost/XE'||'.'||'todos';
		o := '';
		o := o || '"_id": "'||replace(replace(:OLD."_id", chr(10), '\n'), '"', '\"')||'"';
		o := '{'||o||'}';
		o2 := null;
		insert into "oplog" ("id", "ts", "scn", "tr", "v", "op", "ns", "o", "o2")
		values ("oplog_seq".nextval, current_timestamp, dbms_flashback.get_system_change_number, dbms_transaction.local_transaction_id, 2, op, ns, o, o2);
	END IF;
 end;

INSERT INTO "todos" ("name", "priority", "userId", "_id") VALUES (:0, :1, :2, :3)
Parameters: [ 'Get milk', 1, 'Jane', '46662dSw2KkzpNXqo' ]

SELECT * FROM (SELECT * FROM "todos") WHERE "priority" = 1


#2

BRILLIANT. Thanks for sharing.

Okay I’m just shooting from the hip here:

Would it make sense to write to memory and restrict age/retention? Do records get flushed based on ‘ts’?

Could the oplog subscriber limiting the oplog data from their end to only the relevant data. i.e., limiting by select query, perhaps a column to tag records. Maybe this improve performance by simplifying computation on the Meteor side of things (i.e., select … where ns in (’…todos’ … ) and ts > … )

I wonder if desired joins between tables could be views (CREATE VIEW) and appear in the app as view-only collections. Behind the scenes the view logs an update from either of the joined entities.


Perhaps this could also be a great technique for PostgreSQL too. The “oplog” table could use NOTIFY etc.


#3

I thought Christmas was tomorrow? :gift:


#4

Thank you for your feedback.

Would it make sense to write to memory and restrict age/retention? Do records get flushed based on ‘ts’?

I guess this is an idea of write back cache with delayed writes into the database. Is there any solution already implemented for Meteor + MongoDB?

Could the oplog subscriber limiting the oplog data from their end to only the relevant data. i.e., limiting by select query

Oracle provides a mechanism for monitoring individual queries. It’s called Continuous Query Notification (CQN). It could possibly be used to build an oplog for queries not for the whole tables.

I wonder if desired joins between tables could be views (CREATE VIEW) and appear in the app as view-only collections. Behind the scenes the view logs an update from either of the joined entities.

The oplog solution with triggers should work with materialized views. Unfortunately the regular views in Oracle can’t have the DML triggers. The Continuous Query Notification feature should also work on queries with joins.


#5

Version 0.2.0 has just been released. The improvements include:

  • Connectivity to external Oracle databases
  • Strict mode that prevents automatic changes in db schema (turns off elastic data model)
  • Automatic creation of OPLOG table and METEOR_PKG package
  • Removed dependency on DBMS_FLASHBACK

#6

So the data selected from Oracle by the Meteor client gets inserted into miniMongo client side correct?
This way you can have Oracle as your server side db and still utilize offline/client side functionality with Meteor/miniMongo/


#7

So the data selected from Oracle by the Meteor client gets inserted into miniMongo client side correct?

Yes, that is exactly how it works, on the client there is the same miniMongo which doesn’t even know that it’s working with Oracle database on the server side.


#8

I’m working on a project based on an existing oracle DB, is that means i have to insert a ‘_id’ column to the existing DB before I can use the data normal as what we did when using mongoDB?


#9

No. Meteor can work with SQL databases without any need to do anything to the SQL schema. Obviously, if you want to implement some sort of relationship between MongoDB and Oracle, you’ll need to have some sort of reference from one to the other.

If you are thinking about relationships between MongoDB and Oracle, you should consider Apollo in your data stack.


#10

Thanks, just saw the reply, I even opened another post
So when you mentioned 'Meteor can work with SQL databases without any need to do anything to the SQL schema’
Do you can to use node_oracledb? I think I might get that working if I can’t install it correctly with meteor.


#11

Yeah - install using npm:

meteor npm install oracle --save

and then use

import oracle from 'oracle'`;
// or you can use const oracle = require('oracle');

in the files where you need it.


#12

Interesting enough, I’ll look into both, Apollo seems promising.


#13

The package metstrike:meteor-oracle, was designed for developers who prefer to write reactive Meteor applications for Oracle the same way as if they were writing them for MongoDB. It integrates minimongo on the client side with Oracle SQL database on the server side and fully supports reactivity in real time. The node oracle drivers for node (e.g. npm packages oracle or oracledb) provide ability to send get data from Oracle database, but still a lot of programming would be required to achieve reactivity and caching on the client side (minimongo).

The _id column is actually required by minimongo and the current version of this package has a limited ability to work with existing seamlessly creates and alters all objects in Oracle database as needed.
If you have an existing table which doesn’t have “_id” key column, and you don’t want them to be modified automatically by this package, you should switch to the strict mode and also try to have _id added automatically to the resultset.

Oracle.setDefaultOracleOptions({strict: true});
Oracle.setDefaultOracleOptions({sqlAddId: true});

#14

There is also official npm package “oracledb” which seems to be more popular.


#15

Thank you, I’d look into that, really like the package :slight_smile: