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.
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)
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.
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.
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.
IF (SELECT db_id('SQLSyncDemo')) IS NULL CREATE DATABASE SQLSyncDemo;
ALTER DATABASE SQLSyncDemoSET CHANGE_TRACKING = ON(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)​ALTER DATABASE SQLSyncDemoSET ALLOW_SNAPSHOT_ISOLATION ONGO​USE SQLSyncDemoGO
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');
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);
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
Find a desirable location on your server and run the following commands from your terminal:
mkdir mssql-testcd mssql-test#press enter through all the prompts that are returned to create a projectnpm initnpm install realm#replace the path below with your local path to your adapter package -- you will want to cahnge the version accordinglynpm install ~/Downloads/realm-mssql-adapters-2.1.0.tgz
Create the file by running the following command:
touch config.js
Using your preferred text editor, paste the following into your config file:
config.jsmodule.exports = {// Database namedatabase_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-hostedrealm_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 necessaryadmin_username: 'realm-admin',admin_password: '',​// The synced Realm path for the datatarget_realm_path: '/SQLDemoNew',​// MSSQL config used for all connections - replace with your datasqlserver_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 serverencrypt: 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.
Create the file by running the following command:
touch realmmodels.js
Using your preferred text editor, paste the following into your models file:
realmmodels.jsconst 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.
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.jsconst 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 exceptionsprocess.on('uncaughtException', (err) => console.log(err));​async function main() {//login as an admin uservar 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 ROSrealmConfig: {server: Config.realm_object_server_url, // or specify your realm-object-server locationuser: admin_user,},dbName: Config.database_name,dbSchema: Config.database_schema,// SQL Server configuration and database namesqlserverConfig: Config.sqlserver_config,​// Set to true to create the SQL Server DB if not already createdcreateSQLServerDB: 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 tomapSQLServerChangeToRealmPath: 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 replicatedschema: 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.
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.jsconst 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 exceptionsprocess.on('uncaughtException', (err) => console.log(err));​async function main() {//login as an admin uservar 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 ROSrealmConfig: {server: Config.realm_object_server_url, // or specify your realm-object-server locationuser: admin_user,},dbName: Config.database_name,dbSchema: Config.database_schema,// SQL Server configuration and database namesqlserverConfig: Config.sqlserver_config,​// Set to true to create the SQL Server DB if not already createdcreateSQLServerDB: 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 tomapSQLServerChangeToRealmPath: 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 replicatedschema: 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​
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;
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.jsvar 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.jsvar 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.jsconst 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.
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 loaderSQLBatchSize
parameter 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.
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.
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.
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.
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.
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.
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).
This is achieved by using the mapSQLServerTableName
and 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 RealmmapSQLServerTableName?: (name: string) => {let out_name = name;if (name === 'UserData') {out_name = 'RealmUserData';}return out_name;}//from Realm to SQLmapRealmClassName?: (class_name) => {let out_name = class_name;if (class_name === 'RealmUserData') {out_name = 'UserData';}return out_name;}
This is achieved by using the mapSQLServerColumnName
and 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 RealmmapSQLServerColumnName: (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 RealmmapRealmPropertyName: (class_name, property_name) => {let out_name = property_name;if (class_name === 'MapTable') {if (property_name === 'firstname') {out_name = 'name';}}return out_name;},
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 data
which would be used in your model definitions.
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 casesif (value === null) {return value;}if (value === undefined) {return value;}//performn the value conversionif (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 casesif (value === null) {return value;}if (value === undefined) {return value;}//performn the value conversionif (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 millisecondsconnectionRetryInterval: 10 // default 3}}
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.
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.
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 ignoreSQLerrors
flag 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.
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.
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 availablenode --stack_size=16384 adapter.js​# if you're using PM2pm2 start adapter.js --node-args="--stack_size=16384"
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 availablenode --max_old_space_size=16384 adapter.js​# if you're using PM2pm2 start adapter.js --node-args="--max_old_space_size=16384"
Parameter | Overview | Type/Default Setting |
| If set to true the adapter will ignore and log most errors, instead of stopping the adapter. | Boolean: true |
| 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] |
| 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 |
| Creates a SQL database if it does not already exist based on the Realm Object schema. | Boolean: false |
| If set to true the adapter will change the SQL schema when the realm schema changes. | Boolean: false |
| This maps a table and its properties to the appropriate realm path. | String or Function ​See example for details​ |
| 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 | Boolean: true |