MSSQL Data Connector

Distribution of the Postgres data connector package is currently paused. This page is only available for reference. We apologize for the inconvenience.
This guide walks you through how to use Realm’s data adapter to sync data from the Realm Object Server to a Microsoft SQL Server and vice versa. This guide shows the installation of the data adapter on an Ubuntu Server and assumes communication with an MSSQL Server in Amazon RDS, but any MSSQL Server will work as long as you are able to enable change tracking and snapshot isolation.
Requirements 
Realm Object Server 3.11.0 (or higher) or Realm Cloud
MSSQL Server 2014 or newer 
The ability to enable Change Tracking and Snapshot Isolation on your MSSQL Server
A SQL User with sysadmin privileges (to run the adapter)
Releases
Distribution of the MSSQL data connector package is currently paused. We apologize for the inconvenience.
You can see release notes for the latest releases on Github, and watch the repo if you want to be notified about new releases.
Example: Setting up Microsoft SQL Server 
If you are running through this tutorial, we assume that you have a running MSSQL Server. If you do not, it is simple enough to create one using Amazon RDS.
Loading in Test Data:
To get you up and running, we will provide some test data to show how the data synchronization works. You can run these SQL statements from any SQL client like SSMS or SQLPro for MSSQL.
Create the Database
IF (SELECT db_id('SQLSyncDemo')) IS NULL CREATE DATABASE SQLSyncDemo;
Enable Change Tracking on the Database
ALTER DATABASE SQLSyncDemo  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
​
ALTER DATABASE SQLSyncDemo
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
​
USE SQLSyncDemo  
GO
Create Tables
CREATE TABLE dbo.Address (
    ID int NOT NULL,
    ZipCode varchar(10) NOT NULL
);
​
ALTER TABLE dbo.Address ADD CONSTRAINT PK_Address PRIMARY KEY (ID);
​
CREATE TABLE dbo.Customer (
    ID int NOT NULL,
    Company varchar(100) NULL
);
​
ALTER TABLE dbo.Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (ID);
​
CREATE TABLE dbo.Estimate (
    ID int NOT NULL,
    customerid int NOT NULL,
    something varchar(50) NULL
);
​
ALTER TABLE dbo.Estimate ADD CONSTRAINT PK__Estimate PRIMARY KEY (ID);
​
/* INSERT DATA */
​
INSERT INTO Address (ID, ZipCode) VALUES 
(1,'90101'),
(2,'80100'),
(3,'90100'),
(4,'80100'),
(6,'91111');
​
INSERT INTO Customer (ID, Company) VALUES 
(1,'C2'),
(2,'C2'),
(3,'C3');
​
INSERT INTO Estimate (ID, customerid, something) VALUES 
(13,1,'Some estimate');
Enable Change Tracking on each Table
ALTER TABLE Address ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Customer ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Estimate ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
Example: Setting up The Data Connector
The data adapter relies on a number of components for two way data synchronization.
Data Adapter Package: This contains the core codebase of the adapter
Adapter.js: This runs as a process to facilitate synchronization 
realmmodels.js: This defines the schema to be synchronized 
config.js: this defines a number of variables which are used during adapter configuration 
loader.js: You may choose to perform a one time bulk import of data into the Realm Object Server before running the adapter 
Setup the Data Adapter Package
Find a desirable location on your server and run the following commands from your terminal:
mkdir mssql-test
cd mssql-test
#press enter through all the prompts that are returned to create a project
npm init
npm install realm
#replace the path below with your local path to your adapter package -- you will want to cahnge the version accordingly 
npm install ~/Downloads/realm-mssql-adapters-2.1.0.tgz
Prepare your Config File
Create the file by running the following command:
touch config.js
Using your preferred text editor, paste the following into your config file:
config.js
module.exports = {
    // Database name
    database_name: 'SQLSyncDemo',
​
    database_schema: 'dbo',
​
    // Realm Object Server URL
    //self-hosted: realm://10.0.0.7:9080
    //cloud: realms://small-plastic-handle.us1a.cloud.realm.io/
    //note: port only required for self-hosted
    realm_object_server_url: "realm://<IP_OR_DNS_OF_ROS>",
​
    //self-hosted:http://10.0.0.7:9080
    //cloud: https://small-plastic-handle.us1a.cloud.realm.io/
    auth_server_url: "http://<IP_OR_DNS_OF_ROS>",
​
    //enter as necessary
    admin_username: 'realm-admin',
    admin_password: '',
​
    // The synced Realm path for the data
    target_realm_path: '/SQLDemoNew',
​
    // MSSQL config used for all connections - replace with your data
    sqlserver_config: {
        user:       'realm',
        password:   'my-sql-password',
        server:     'mydbinstanceAddress.us-east-1.rds.amazonaws.com',
        port:        1433,
        connectionTimeout: 300000,
        requestTimeout:    300000,
        pool: {
           idleTimeoutMillis: 300000
       },
      options: {
          //only if needed for server 
          encrypt: true
      }    
  },
}
You'll need to change a number of the various variables to match your own environment like the SQL Server information, admin user token, and the ROS address.
Prepare your Models File
Create the file by running the following command:
touch realmmodels.js
Using your preferred text editor, paste the following into your models file:
realmmodels.js
const Customer = {
    name: 'Customer',
    primaryKey: 'ID',
    properties: {
        ID: 'int',
        Company: 'string',
        Estimates: { type: 'list', objectType: 'Estimate', watch_table: true }, 
    }
}
​
const Address = {
    name: 'Address',
    primaryKey: 'ID',
    properties: {
        ID: 'int',
        ZipCode: 'string',
    },
}
​
const Estimate = {
    name: 'Estimate',
    primaryKey: 'ID',
    properties: {
        ID: 'int',
        customerid: { type: 'linkingObjects', objectType: 'Customer', property: 'Estimates' },
        Something: 'string',
    }
}
​
​
module.exports = [
    Customer,  
    Address, 
    Estimate
];
This models file is built specifically to work with the SQL data which we loaded into our SQL Server. You will edit this schema to match your own data that you wish to synchronize.
Load the existing SQL Data into ROS
Create your loader file by running the following command:
touch loader.js
Using your preferred text editor, paste the following into your loader file:
loader.js
const Realm = require('realm');
const fs = require('fs');
const path = require('path');
const SQLServerRealmLoader = require('realm-mssql-adapters').SQLServerRealmLoader;
const Config = require('./config');
​
const Models = require('./realmmodels');
​
if (process.env.NODE_ENV !== 'production') {
    require('source-map-support').install();
}
​
// Print out uncaught exceptions
process.on('uncaughtException', (err) => console.log(err));
​
async function main() {
    //login as an admin user 
    var admin_user = await Realm.Sync.User.login(Config.auth_server_url, Config.admin_username, Config.admin_password)
​
    const conf = {
        // Realm configuration parameters for connecting to ROS
        realmConfig: {
            server: Config.realm_object_server_url, // or specify your realm-object-server location
            user:   admin_user,
        },
        dbName: Config.database_name,
        dbSchema: Config.database_schema,
        // SQL Server configuration and database name
        sqlserverConfig: Config.sqlserver_config,
​
        // Set to true to create the SQL Server DB if not already created
        createSQLServerDB: false,
        initializeRealmFromSQLServer: false,
        // Enable and set this function if you'd like to speed up your loader 
        //loaderSQLBatchSize: 1000,
​
        // Set to true to indicate SQL Server tables should be created and
        // properties added to these tables based on schema additions
        // made in Realm. If set to false any desired changes to the
        // SQL Server schema will need to be made external to the adapter.
        applyRealmSchemaChangesToSQLServer: false,
​
        // Only match a single Realm called 'testRealm'
        //realmRegex: `/*`+Config.database_name,
        realmRegex: Config.target_realm_path,
​
        // Specify the Realm name all SQL Server changes should be applied to
        mapSQLServerChangeToRealmPath: Config.target_realm_path,
​
        // Specify the Realm objects we want to replicate in SQL Server.
        // Any types or properties not specified here will not be replicated
        schema: Models,
​
        printCommandsToConsole: true,
​
    }
    var loader = new SQLServerRealmLoader(conf);
        loader.init().catch(console.error)
}
​
main();
Now you should be able to run the loader to import your SQL data into the Realm Object Server. Simply run:
node loader.js
Note: At this point, you might check to see if the data is available in Realm Studio. If you see the Realm files created but no resulting data, this is due to the loader / adapter design. After completion, the loader will attempt to upload all local data to your Realm Object Server instance. If it does not complete within the loaderUploadMaxWaitTime (default 60 seconds), it will simply persist the data locally until it is ultimately uploaded when the adapter process is run.
You should now be able to use Realm Studio to see that the data was synchronized to your Realm Object Server.
MSSQL Data Loaded into ROS
Run the Adapter for Bidirectional Sync
Finally, we need to create and configure the adapter file which will facilitate bidirectional synchronization. Create the file by running the following command:
touch adapter.js
Using your preferred text editor, paste the following into your adapter file:
adapter.js
const fs = require('fs');
const path = require('path');
const process = require('process');
const SQLServerAdapter = require('realm-mssql-adapters').SQLServerAdapter;
const Config = require('./config');
const Models = require('./realmmodels');
​
// Print out uncaught exceptions
process.on('uncaughtException', (err) => console.log(err));
​
async function main() {
    //login as an admin user 
    var admin_user = await Realm.Sync.User.login(Config.auth_server_url, Config.admin_username, Config.admin_password)
    const conf = {
        // Realm configuration parameters for connecting to ROS
        realmConfig: {
            server: Config.realm_object_server_url, // or specify your realm-object-server location
            user:   admin_user,
        },
        dbName: Config.database_name,
        dbSchema: Config.database_schema,
        // SQL Server configuration and database name
        sqlserverConfig: Config.sqlserver_config,
​
        // Set to true to create the SQL Server DB if not already created
        createSQLServerDB: false,
        initializeRealmFromSQLServer: false,
​
        // Set to true to indicate SQL Server tables should be created and
        // properties added to these tables based on schema additions
        // made in Realm. If set to false any desired changes to the
        // SQL Server schema will need to be made external to the adapter.
        applyRealmSchemaChangesToSQLServer: false,
​
        // Only match a single Realm called 'testRealm'
        //realmRegex: `/*`+Config.database_name,
        realmRegex: Config.target_realm_path,
​
        // Specify the Realm name all SQL Server changes should be applied to
        mapSQLServerChangeToRealmPath: Config.target_realm_path,
​
        // Specify the Realm objects we want to replicate in SQL Server.
        // Any types or properties not specified here will not be replicated
        schema: Models,
​
        printCommandsToConsole: false,
​
    }
​
    var adapter = new SQLServerAdapter(conf);
}
​
main();
Finally, run the adapter file:
node adapter.js
In a production environment, make sure to run the adapter in the background using a process manager like PM2.
You can now make changes on either the MSSQL or ROS side and see the changes synchronized between the two databases.
Not what you were looking for? Leave Feedback​
Running the adapter without a sysadmin
In the event that you cannot or prefer not to use a sysadmin user, you may create a user that is granted select, insert, delete, update and change tracking privileges. You will need to grant these privileges for every table in the database.
create login foo with password = 'password';    
create user foo for login foo with default_schema = dbo;
​
alter database test set change_tracking = on;
​
grant create database to foo;
grant create table to foo;
grant select on test.dbo.MapTable to foo;
grant insert on test.dbo.MapTable to foo;
grant alter on test.dbo.MapTable to foo;
grant delete on test.dbo.MapTable to foo;
grant update on test.dbo.MapTable to foo;
grant view change tracking on test.dbo.MapTable to foo;
Logging
The adapter uses winston for logging, it creates a default logger that logs to Console, you can control the default log level (info) using logLevel parameter. For a list of available log levels, see the following page.
adapter.js
 var adapter = new SQLServerAdapter({
 // ...
 logLevel: 'verbose',// use LogLevels.VERBOSE for TypeScript
 });
You can also provide a custom winston logger using the logger parameter.adapter.js
adapter.js
var adapter = new SQLServerAdapter({
​
 // ...
 logger: winston.createLogger({ /* ... */ }),
 });
Alternatively, we provide another logger that outputs to a file instead of Console. This is helpful for verbose logging as sometimes the Console buffer can't keep up which causes the node process to crash (see issue).
adapter.js
const createFileLogger = require('realm-mssql-adapters').createFileLogger;
var adapter = new SQLServerAdapter({
 // ...
​
logger: createFileLogger(loglevel, filename),
});
Both loglevel and filename arguments are optional. loglevel will default to info if not specified and filename (which indicates an absolute path) will default to a file named realm-adapter-log-YYYY-MM-DD-MM-SS.txt(created in the current directory where node was started).
For debugging purpose it could be helpful to log the SQL commands, in order to activate this feature, the log level must be set to verbose and printCommandsToConsole set to true in the configuration.
Improving the Performance of your Loader
Importing a large amount of data can sometimes take a fair amount of time. Here's a few tips to increase the performance of your loader
Increase the batch size from the default size of 1000 by using the loaderSQLBatchSizeparameter in your loader file. 
When using JOIN tables, seed the RealmID field with a uuid on the SQL side before running the loader script.
Set loadOnline to false in your loader configuration. This will load the MSSQL data within a local Realm in the loader/adapter directory. Once the adapter is started up, it will sync the locally stored data as it processes incoming updates.
Decrease the log level on your adapter and server. It is common to increase the logging verbosity when troubleshooting issues. However, leaving it at debug or higher levels can cause slowdowns.
Relationships
One of the first things you will need to add to support the Realm Adapter for MSSQL is a RealmId string field to each object you want to support in Realm. This will be used as the PrimaryKey for your Realm models on the Realm side. You will also need to add a RealmId column as a nvarchar(128) to each table that matches your classes in Realm. This will be auto-generated and loaded into SQL when the Realm loader is run - to improve performance generate these GUID values on the SQL side first. This is necessary to enable the linking of models and List relationships from Realm primary keys to SQL primary keys. Additionally, this will allow an offline mobile client to create objects without getting a primary key from the SQL Server. The adapter will then create a mapping of Realm object to a SQL row by creating a mapping of the primary keys. In the Realm models the actual primary key of the SQL server will be made optional in Realm (even though it is required in SQL) but designated in the Realm model with sqlserverPrimaryKey key. This is why it is important to designate an IDENTITY column which will automatically insert a Primary Key into a new row when the Realm adapter inserts a new object into SQL.
When designing your schema for the Realm adapter to match the schema of your MSSQL database you must consider how your relationships should be structured in the Realm Object model compared to relations between tables on SQL Server.
1 to 1 Relationship
This is the most straightforward relationship. In your SQL schema you have one table that has a foreign key column to another table thus forming a relationship. In the Realm data model relationships are defined by declaring a name of a Realm object as a property of a parent object as shown here:
Let’s say your SQL schema looked like this:
CREATE TABLE dbo.Passport
(
PassportId INT NOT NULL IDENTITY(1,1),
RealmId VARCHAR(255),
Country     VARCHAR(255) NOT NULL,
CONSTRAINT PK_Passport PRIMARY KEY (PassportId)
);
​
CREATE TABLE dbo.Person
(
PersonId INT NOT NULL IDENTITY(1,1),
Name VARCHAR(255) NOT NULL,
RealmId VARCHAR(255),
PassportId INT FOREIGN KEY REFERENCES Passport(PassportId),
CONSTRAINT PK_Person PRIMARY KEY (PersonId)
);
Notice how we added an optional RealmId column to each table.
With this schema we can see that there is a Person table and there is a column for PassportId which means every Person must have a Passport or foreign key to the ID column of the Passport table. Now we go to create this schema with a Realm model, notice how the PassportId field for Person now just have the relation or link to the Passport class.
const Person = {
   name: 'Person',
   primaryKey: 'RealmId',
   sqlserverPrimaryKey: 'PersonId',
   properties: {
       RealmId: 'string',
       PassportId: 'Passport',
       PersonId: { type: 'int', optional: true },
       Name: 'string',
   }
}
​
const Passport = {
       name: 'Passport',
       primaryKey: 'RealmId',
       sqlserverPrimaryKey: 'PassportId',
       properties: {
           Country: 'string',
           RealmId: 'string',
           PassportId: { type: 'int', optional: true },
       }
   }
Now lets insert some data into SQL Server by executing the commands below
SET IDENTITY_INSERT Passport ON
​
INSERT INTO dbo.Passport (PassportId, Country) VALUES
(1, 'USA'),
(2, 'Canada');
​
SET IDENTITY_INSERT Passport OFF
​
SET IDENTITY_INSERT Person ON
​
INSERT INTO dbo.Person (PersonId, Name, PassportId) VALUES
(1, 'Curly', 1),
(2, 'Larry', 1),
(3, 'Moe', 2);
​
SET IDENTITY_INSERT Person OFF
Now run the loader and it should then finish without errors and then run the adapter. If you take a look in Realm Studio you should see the Person objects populated with links to a Passport objects.
If you now run the adapter you can insert into Realm or SQL and watch as the data is replicated to the opposite side. If you are inserting from the Realm side - do not worry about inserting the SQL primary key - the database should take care of that for you. Conversely, if you are inserting from the SQL side the Realm object will automatically have a RealmId generated for it from the adapter.
1 to Many Relationships
Now the next question becomes how to represent one to many relationships where a single parent object can have many child objects. In SQL these are represented with Foreign Keys from the child object back to its particular parent. In Realm we represent these as a list or array of Child objects as a property of the Parent object as shown here​
Let’s say your SQL Schema looks like so:
CREATE TABLE dbo.Author
(
AuthorId INT NOT NULL IDENTITY(1,1),
FullName     VARCHAR(255) NOT NULL,
RealmId VARCHAR(255),
CONSTRAINT PK_Author PRIMARY KEY (AuthorId)
);
​
CREATE TABLE dbo.Book
(
BookId INT NOT NULL IDENTITY(1,1),
Name VARCHAR(255) NOT NULL,
RealmId VARCHAR(255),
Author   INT FOREIGN KEY REFERENCES Author(AuthorId),
CONSTRAINT PK_Book PRIMARY KEY (BookId)
);
Your corresponding Realm schema would look like this:
const Author = {
   name: 'Author',
   primaryKey: 'RealmId',
   sqlserverPrimaryKey: 'AuthorId',
   properties: {
       RealmId: 'string',
       AuthorId: { type: 'int', optional: true },
       FullName: 'string',
       books: { type: 'linkingObjects', objectType: 'Book', property: 'Author' },
   }
}
const Book = {
   name: 'Book',
   primaryKey: 'RealmId',
   sqlserverPrimaryKey: 'BookId',
   properties: {
       Author: 'Author',
       BookId: { type: 'int', optional: true },
       RealmId: 'string',
       Name: 'string',
   }
}
We use a linking object in Author to return an array of all Book objects that are linked to that Author. While it will not show up in Studio - you will be able to access these Author.Book objects in your Swift/Kotlin/Java/JS/.NET models.
Now lets insert some data:
SET IDENTITY_INSERT Author ON
​
INSERT INTO dbo.Author (AuthorId, FullName) VALUES
(1, 'Ernest Hemingway'),
(2, 'Kurt Vonnegut'),
(3, 'Mikhail Bulgakov');
​
SET IDENTITY_INSERT Author OFF
​
SET IDENTITY_INSERT Book ON
​
INSERT INTO dbo.Book (BookId, Name, Author) VALUES
(1, 'For Whom the Bell Tolls', 1),
(2, 'The Sun Also Rises', 1),
(3, 'A Farewell to Arms', 1),
(4, 'Slaughterhouse-Five', 2),
(5, 'Cats Cradle', 2),
(6, 'The Master and Margarita', 3);
​
SET IDENTITY_INSERT Book OFF
Now if you run the loader and adapter you should see the Foreign Key column mapping to the appropriate relationship back to Author.
1 to Many Relationships with Order
One of the things about Relational Databases is that they do not have an inherent order in their tables - rows are ordered by when they are inserted. In Realm, and with all object-oriented programming Lists with order give the developer one less thing to worry about. The MSSQL-Realm adapter is no exception.
Let’s say your SQL Schema looks like so:
CREATE TABLE dbo.BusLine
(
BusLineId INT NOT NULL IDENTITY(1,1),
Name     VARCHAR(255) NOT NULL,
RealmId VARCHAR(255),
CONSTRAINT PK_BusLine PRIMARY KEY (BusLineId)
);
​
CREATE TABLE dbo.Stop
(
StopId INT NOT NULL IDENTITY(1,1),
Name VARCHAR(255) NOT NULL,
RealmId VARCHAR(255),
BusLineId INT FOREIGN KEY REFERENCES BusLine(BusLineId),
CONSTRAINT PK_Stop PRIMARY KEY (StopId)
);
The corresponding Realm schema would look like so:
const Stop = {
   name: 'Stop',
   primaryKey: 'RealmId',
   sqlserverPrimaryKey: 'StopId',
   properties: {
       RealmId: 'string',
       BusLineId: { type: 'linkingObjects', objectType: 'BusLine', property: 'Stops' },
       StopId: { type: 'int', optional: true },
       Name: 'string',
   }
}
​
const BusLine = {
   name: 'BusLine',
   primaryKey: 'RealmId',
   sqlserverPrimaryKey: 'BusLineId',
   properties: {
       RealmId: 'string',
       BusLineId: { type: 'int', optional: true },
       Name: 'string',
       Stops: { type: 'list', objectType: 'Stop', watch_table: true },
   }
}
Notice the special watch_table: true designation in its List definition. This instructs the adapter that order matters here and will continue to make sure that order is maintained each time inserts or modifications occur in the RealmList.
Now let’s insert some data:
SET IDENTITY_INSERT BusLine ON
​
INSERT INTO dbo.BusLine (BusLineId, Name) VALUES
(1, 'Marina'),
(2, 'SOMA'),
(3, 'Mission');
​
SET IDENTITY_INSERT BusLine OFF
​
SET IDENTITY_INSERT Stop ON
​
INSERT INTO dbo.Stop (StopId, Name, BusLineId) VALUES
(1, 'Market', 1),
(2, 'Gough', 1),
(3, 'Union', 1),
(4, 'Townsend', 2),
(5, 'Mission', 3),
(6, 'Van Ness', 3);
​
SET IDENTITY_INSERT Stop OFF
Now if you rerun the loader and the adapter you will see a List of Stops associated with each BusLine.
On the SQL side you will see a special table created called __BusLine__Stops - this is an internal table for the Realm Adapter to maintain order.
Many to Many Relationships
In SQL Many-Many relationships are represented through JOIN tables that often use a compound primary key between two Foreign Keys. Such concepts are not needed in Realm because it is an object database that uses links to other classes to form relationships with backlinks as shown here​
However, in order to integrate with relational database constructs the adapter provides support for JOIN tables and compound primary keys.
Let’s say your SQL schema looked like this:
CREATE TABLE dbo.Route (
    RouteKey int NOT NULL IDENTITY(1,1),
    RouteName nvarchar(32),
    RealmID nvarchar(128),
    CONSTRAINT PK_Route PRIMARY KEY (RouteKey),
)
CREATE TABLE dbo.Employee (
    EmployeeKey int NOT NULL IDENTITY(1,1),
    EmployeeName nvarchar(64),
    RealmID nvarchar(128),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeKey)
)
CREATE TABLE dbo.EmployeeRoute (
    RouteKey int FOREIGN KEY REFERENCES Route(RouteKey) NOT NULL,
    EmployeeKey int FOREIGN KEY REFERENCES Employee(EmployeeKey) NOT NULL
)
ALTER TABLE dbo.EmployeeRoute ADD CONSTRAINT PK_EMPLOYEE_ROUTE PRIMARY KEY (EmployeeKey, RouteKey); /* Compound PK */
Then your corresponding Realm schema would look like this:
const Route = {
   name: 'Route',
   primaryKey: 'RealmID',
   sqlserverPrimaryKey: 'RouteKey',
   properties: {
       RealmID: 'string',
       RouteKey: { type: 'int', optional: true },
       RouteName: 'string',
       Employees: { type: 'linkingObjects', objectType: 'EmployeeRoute', property: 'RouteKey' }
   }
}
​
const EmployeeRoute = {
   name: 'EmployeeRoute',
   primaryKey: 'PK',
   properties: {
       PK: 'string',
       EmployeeKey: 'Employee',
       RouteKey: 'Route'
   }
}
​
const Employee = {
   name: 'Employee',
   primaryKey: 'RealmID',
   sqlserverPrimaryKey: 'EmployeeKey',
   properties: {
       RealmID: 'string',
       EmployeeKey: { type: 'int', optional: true },
       EmployeeName: { type: 'string', optional: true },
       Routes: { type: 'linkingObjects', objectType: 'EmployeeRoute', property: 'EmployeeKey' }
   }
}
Where every Employee has Routes and every Route has Employees. Notice the new field PK on the EmployeeRoute object. This is an internal Realm Adapter construct which will represent the JOIN table compound key as a concatenation of the two foreign keys.
Now let’s insert some data into SQL:
SET IDENTITY_INSERT Employee ON
​
INSERT INTO dbo.Employee (EmployeeKey, EmployeeName) VALUES
(1, 'Tom'),
(2, 'David'),
(3, 'Nathan');
​
SET IDENTITY_INSERT Employee OFF
​
SET IDENTITY_INSERT Route ON
​
INSERT INTO dbo.Route (RouteKey, RouteName) VALUES
(1, 'East'),
(2, 'West'),
(3, 'North');
​
SET IDENTITY_INSERT Route OFF
​
INSERT INTO dbo.EmployeeRoute  (EmployeeKey, RouteKey) VALUES
(1, 1),
(2, 1),
(3, 1),
(2, 3);
You will also need to configure the compound primary keys in the adapter and the loader like this:
   compoundPrimaryKeys: {
       'EmployeeRoute': [
           { property: 'EmployeeKey', dbColumn: 'EmployeeKey', type: 'int' },
           { property: 'RouteKey', dbColumn: 'RouteKey', type: 'int' },
       ]
​
   }
In order to insert or delete directly to/from EmployeeRoute (or any JOIN object), a few rules must be obeyed.
The EmployeeRoute.PK (on the Realm side) must be made up of two parts. For example ‘2-3’, where the first number(2) is the SQL ID (Employee.EmployeeKey), and the second number (3) is also the SQL ID Route.RouteKey. Also, the EmployeeRoute.EmployeeKey and EmployeeRoute.RouteKey columns have to store references (Realm IDs) to the corresponding entities as defined in the PK.
Updating an EmployeeRoute (or a JOIN object) is not possible because the PK is made up of two parts which reference the linked objects. If you update the other columns (the referenced objects), you would need to update the PK as well, but updating PKs is neither possible nor advisable. Instead it is recommend to delete the JOIN object and then re-insert with the new object relational mapping.
Deleting an Employee or a Route while any EmployeeRoute referencing them still exists causes an error. If you wish to do this please delete the JOIN object first and then delete the corresponding related object. MSSQL will throw a FK constraint violation if you attempt to do this.
Mapping and Renaming
When integrating with your existing MSSQL database, it is not uncommon to want to rename some of your SQL tables or columns. For example, you may have a SQL column whose name is a reserved keyword within your client SDK (like "description" is in Swift).
Mapping a SQL table name to a Realm class name
This is achieved by using the mapSQLServerTableNameand the mapRealmClassName functions. These functions can be considered sibling functions. They are typically defined within your configuration or constants files and then called from your loader and adapter scripts. mapSQLServerTableName is required in both your loader and adapter. mapRealmClassName is only required within your adapter since the loader does not perform bidirectional sync.
For example, let's imagine we have a SQL table called UserData that we would like to rename RealmUserData
You can do this with the follow functions:
   //from SQL to Realm 
   mapSQLServerTableName?: (name: string) => {
      let out_name = name;
      if (name === 'UserData') {
         out_name = 'RealmUserData';
      }
      return out_name;
   }
   //from Realm to SQL
   mapRealmClassName?: (class_name) => {
      let out_name = class_name;
      if (class_name === 'RealmUserData') {
         out_name = 'UserData';
      }
      return out_name;
   }
Mapping a SQL column name to a Realm property name
This is achieved by using the mapSQLServerColumnNameand the mapRealmPropertyName functions. These functions can be considered sibling functions. They are typically defined within your configuration or constants files and then called from your loader and adapter scripts. mapSqlServerColumnName is required in both your loader and adapter. mapRealmPropertyName is only required within your adapter since the loader does not perform bidirectional sync.
For example, if your SQL schema looks like:
CREATE TABLE MapTest.dbo.MapTable 
(
  id INT NOT NULL IDENTITY(1,1),
  RealmId VARCHAR(255),
  name VARCHAR(255),
  age INT,
  CONSTRAINT PK_id PRIMARY KEY (id)
)
If you wanted to rename the name column in SQL to be called firstname in realm. You can do this with the following:
        //table_name denotes the name of the table in SQL and is optional
        //column_name denotes the column to be renamed.  
        //simply, return the value of the new name in Realm 
        mapSQLServerColumnName: (table_name, column_name) => {
            let out_name = column_name;
            if (table_name === 'MapTable') {
                if (column_name === 'name') {
                    out_name = 'firstname';
                }
            }
            return out_name;
        },
​
        //class_name denotes the name of the class/model in Realm and is optional
        //property_name denotes the name of the Realm property 
        //simply, return the value of the new name in Realm 
        mapRealmPropertyName: (class_name, property_name) => {
            let out_name = property_name;
            if (class_name === 'MapTable') {
                if (property_name === 'firstname') {
                    out_name = 'name';
                }
            }
            return out_name;
        },
Natively Supported MSSQL Types 
All SQL Server data types are mapped to JavaScript types using the Tedious driver. For a complete list of supported types, see the following documentation. Realm supports all JavaScript types details on how they are represented can be found here. Only SQL Server's geographical data types are not supported.
For example, the Tedious driver will represent the binary type as a Buffer. The Buffer type is represented in Realm JS as datawhich would be used in your model definitions.
Retrying Failed Queries
Converting a SQL Value Type to a different Realm Value Type
While integrating with your SQL server you may want to remap one of your existing SQL types to a new type for easier use in your mobile applications. You can do this with the pair of convert sibling functions.
For example, if your SQL schema looks like:
CREATE TABLE dbo.Money  
(  
MoneyId INT NOT NULL IDENTITY(1,1),
RealmId VARCHAR(255),
Change decimal(5,2),
CONSTRAINT PK_Money PRIMARY KEY (MoneyId)
);
If you wanted to convert the Change column in SQL from a decimal to a string in Realm. You'll start by declaring your corresponding desired realm schema:
const Money = {
    name: 'Money',
    primaryKey: 'RealmId',
    sqlserverPrimaryKey: 'MoneyId',
    properties: {
        RealmId: 'string',
        MoneyId: { type: 'int', optional: true },
        Change: 'string',
    }
}
Then you'll implement these conversion functions in your loader and adapter scripts.
    convertSQLServerValueToRealm: (table_name, column_name, value) => {
        //handle some base cases 
        if (value === null) {
            return value;
        }
        if (value === undefined) {
            return value;
        }
        //performn the value conversion 
        if (table_name === 'Money') {
            if (column_name === 'Change') {
                console.log('BEFORE ' + 'TABLENAME: ' + table_name + 'COLUMNNAME: ' + column_name + 'VALUE: ' + value);
                newVal = value.toString();
                console.log('AFTER ' + newVal);
                return newVal;
            }
        }
        return value;
    },
    convertRealmValueToSQLServer: (class_name, property_name, value) => {
        //handle some base cases
        if (value === null) {
            return value;
        }
        if (value === undefined) {
            return value;
        }
        //performn the value conversion
        if (class_name === 'Money') {
            if (property_name === 'Change') {
                console.log('BEFORE ' + 'TABLENAME: ' + table_name + 'COLUMNNAME: ' + column_name + 'VALUE: ' + value);
                newVal = parseFloat(value);
                console.log('AFTER ' + newVal);
                return newVal;
            }
        }
        return value;
    },
It is possible that a SQL query made by the adapter could fail for various reasons like general server unavailability. The adapter uses the tedious driver which exposes two options to retry transient errors connectionRetryInterval and maxRetriesOnTransientErrors . This can be customized via the sqlserver_config block.
Example:
sqlserver_config: {
        user:       '...',
        password:   '...',
        server:     '...',
      options: {
          connectionRetryInterval: 1000, // default is 500 milliseconds
          connectionRetryInterval: 10 // default 3
      }
}
Recovering from Errors
While running the MSSQL Data Connector, it's possible that you'll receive an adapter error if the adapter tries to perform an illegal operation. Since the adapter has its own concept of state, errors can be handled in different ways.
Schema Mismatch
While running the adapter, it is important that the adapter schema is defined in a way that matches the reference SQL Database. It is also possible that you will need to change the schema over time (i.e. adding a new field from your MSSQL schema for the adapter to sync to your mobile apps.) Schema changes can either be additive or destructive.
Currently, additive schema changes require that you update your model definitions file and re-run the loader to instantiate the new schema additions. This will be change in the future, and will no longer require the loader to be re-run.
Destructive schema changes are handled differently, and will follow the same rules as any destructive schema change. The schema will need to be updated in your model definitions, and you will either need to delete the existing Realm on the server (which would result in a loss of data if not backed up or not stored in the MSSQL) or you will need to version your Realm paths and configure the adapter to write to the new Realm path. From there, the loader would need to be re-run in either scenario.
Invalid Value being Sent to MSSQL from ROS
It is possible that your MSSQL database has constraints on the data that can be inserted (for example, a varchar may have a certain length limitation). It is important to safeguard against as many of these constraints as possible at the client level. If this is not done and a value makes its way to the adapter, an error will be thrown. In a development environment, you'll want to react to this, fix the source of the error, and reinitialize your setup by running the loader over again. In a production environment, you will want to enable the ignoreSQLerrorsflag to ensure maximum uptime. This setting is configured in your adapter file and allows the adapter to continue running by ignoring errors from failed SQL statements. Note: while this will ensure uptime, it will also cause a divergence between ROS and MSSQL as the data will continue to persist within the ROS.
Last sync version is outdated!
This error indicates that change tracking has expired for one of your tables being watched by the adapter. This can happen if you stop running the adapter for an extended period of time or if the adapter becomes stuck or falls behind dramatically. To recover from this error, you will need to run the loader again to reinitialize change tracking. It may make sense to increase your Change Tracking window on MSSQL if this is a concern for you.
RangeError: Maximum call stack size exceeded
This is a generic node error which indicates that the process needs more available resources. Details on increasing the stack size can be found here.
Example:
#example shows 16MB made available 
node --stack_size=16384 adapter.js
​
# if you're using PM2 
pm2 start adapter.js --node-args="--stack_size=16384"
JavaScript heap out of memory
This is a generic node error which indicates that the process needs more available memory. This error can be thrown when working with very large databases. You can easily increase the memory available to the process like so:
Example:
# example shows 16gb made available 
node --max_old_space_size=16384 adapter.js
​
# if you're using PM2 
pm2 start adapter.js --node-args="--max_old_space_size=16384"
Configuration Options Quick Reference
Parameter
Overview
Type/Default Setting
ignoreSQLerrors
If set to true the adapter will ignore and log most errors, instead of stopping the adapter.
Boolean: true
realmIgnoreProperties
An optional dictionary which lists properties the adapter will ignore.
This can be used to create new properties for client Realms which are not synced back to MSSQL
[string]
switchToRefernceRealm
Indicates if Realms should be converted to a reference Realms when uploading to Realm Object Server. If you are using query-based sync, you will want this parameter set to true
Boolean: false
createSQLServerDB
Creates a SQL database if it does not already exist based on the Realm Object schema.
Boolean: false
applyRealmSchemaChangesToSQLServer
If set to true the adapter will change the SQL schema when the realm schema changes.
Boolean: false
mapSQLServerChangeToRealmPath
This maps a table and its properties to the appropriate realm path.
String or Function
​See example for details​
loadOnline
This feature determines if the MSSQL loader will upload data to ROS as it runs. When working with massive datasets, this can be set to falsewhich will cause the loader to only cache the data locally. This will improve the running time of the loader, and data will ultimately be uploaded to ROS after starting the adapter.
Boolean: true
Server SDKs
Postgres Connector
Last updated 1 year ago
Parameter	Overview	Type/Default Setting
`ignoreSQLerrors`	If set to true the adapter will ignore and log most errors, instead of stopping the adapter.	Boolean: true
`realmIgnoreProperties`	An optional dictionary which lists properties the adapter will ignore. This can be used to create new properties for client Realms which are not synced back to MSSQL	[string]
`switchToRefernceRealm`	Indicates if Realms should be converted to a reference Realms when uploading to Realm Object Server. If you are using query-based sync, you will want this parameter set to true	Boolean: false
`createSQLServerDB`	Creates a SQL database if it does not already exist based on the Realm Object schema.	Boolean: false
`applyRealmSchemaChangesToSQLServer`	If set to true the adapter will change the SQL schema when the realm schema changes.	Boolean: false
`mapSQLServerChangeToRealmPath`	This maps a table and its properties to the appropriate realm path.	String or Function See example for details
`loadOnline`	This feature determines if the MSSQL loader will upload data to ROS as it runs. When working with massive datasets, this can be set to `false`which will cause the loader to only cache the data locally. This will improve the running time of the loader, and data will ultimately be uploaded to ROS after starting the adapter.	Boolean: true