import { CDCToSqliteRow, toSqliteInputRow } from '@module/common/mssqls-to-sqlite.js';
import { CDC_SCHEMA } from '@module/common/MSSQLSourceTable.js';
import { MSSQLConnectionManager } from '@module/replication/MSSQLConnectionManager.js';
import {
escapeIdentifier,
getCaptureInstances,
getLatestLSN,
getLatestReplicatedLSN,
toQualifiedTableName
} from '@module/utils/mssql.js';
import {
applyRowContext,
CompatibilityContext,
SQLITE_TRUE,
SqliteInputRow,
TimeValuePrecision
} from '@powersync/service-sync-rules';
import sql from 'mssql';
import { afterAll, beforeEach, describe, expect, test } from 'vitest';
import {
clearTestDb,
createUpperCaseUUID,
enableCDCForTable,
TEST_CONNECTION_OPTIONS,
waitForPendingCDCChanges
} from './util.js';
describe('MSSQL Data Types Tests', () => {
const connectionManager = new MSSQLConnectionManager(TEST_CONNECTION_OPTIONS, {});
beforeEach(async () => {
await clearTestDb(connectionManager);
await setupTestTable();
});
afterAll(async () => {
await connectionManager.end();
});
async function setupTestTable() {
await connectionManager.query(`
CREATE TABLE ${escapeIdentifier(connectionManager.schema)}.test_data (
id INT IDENTITY(1,1) PRIMARY KEY,
tinyint_col TINYINT,
smallint_col SMALLINT,
int_col INT,
bigint_col BIGINT,
float_col FLOAT,
real_col REAL,
decimal_col DECIMAL(10,2),
numeric_col NUMERIC(10,2),
money_col MONEY,
smallmoney_col SMALLMONEY,
bit_col BIT,
date_col DATE,
datetime_col DATETIME,
datetime2_col DATETIME2(7),
smalldatetime_col SMALLDATETIME,
datetimeoffset_col DATETIMEOFFSET(3),
time_col TIME(7),
char_col CHAR(10),
varchar_col VARCHAR(255),
varchar_max_col VARCHAR(MAX),
nchar_col NCHAR(15),
nvarchar_col NVARCHAR(255),
nvarchar_max_col NVARCHAR(MAX),
text_col TEXT,
ntext_col NTEXT,
binary_col BINARY(16),
varbinary_col VARBINARY(256),
varbinary_max_col VARBINARY(MAX),
image_col IMAGE,
uniqueidentifier_col UNIQUEIDENTIFIER,
xml_col XML,
json_col NVARCHAR(MAX),
hierarchyid_col HIERARCHYID,
geometry_col GEOMETRY,
geography_col GEOGRAPHY
)
`);
await enableCDCForTable({ connectionManager, table: 'test_data' });
}
test('Number types mappings', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(
tinyint_col,
smallint_col,
int_col,
bigint_col,
float_col,
real_col,
decimal_col,
numeric_col,
money_col,
smallmoney_col,
bit_col
) VALUES (
255, -- TINYINT maximum value
32767, -- SMALLINT maximum value
2147483647, -- INT maximum value
9223372036854775807, -- BIGINT maximum value
3.1415926535, -- FLOAT example
3.14, -- REAL example
12345.67, -- DECIMAL(10,2) example
12345.67, -- NUMERIC(10,2) example
12345.67, -- MONEY example
123.45, -- SMALLMONEY example
1 -- BIT value
)
`);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
const expectedResult: SqliteInputRow = {
tinyint_col: 255,
smallint_col: 32767,
int_col: 2147483647,
bigint_col: 9223372036854775807n,
float_col: 3.1415926535,
real_col: expect.closeTo(3.14, 2),
decimal_col: 12345.67,
numeric_col: 12345.67,
money_col: 12345.67,
smallmoney_col: 123.45,
bit_col: SQLITE_TRUE
};
expect(databaseRows[0]).toMatchObject(expectedResult);
expect(replicatedRows[0]).toMatchObject(expectedResult);
});
test('Character types mappings', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data (
char_col,
varchar_col,
varchar_max_col,
nchar_col,
nvarchar_col,
nvarchar_max_col,
text_col,
ntext_col
) VALUES (
'CharData', -- CHAR(10) with padding spaces
'Variable character data',-- VARCHAR(255)
'Variable character data MAX', -- VARCHAR(MAX)
N'UnicodeChar', -- NCHAR(15)
N'Variable Unicode data', -- NVARCHAR(255)
N'Variable Unicode data MAX', -- NVARCHAR(MAX)
'TextData', -- TEXT
N'UnicodeTextData' -- NTEXT
)
`);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
const expectedResult = {
char_col: 'CharData ', // CHAR pads with spaces up to the defined length (10)
varchar_col: 'Variable character data',
varchar_max_col: 'Variable character data MAX',
nchar_col: 'UnicodeChar ', // NCHAR pads with spaces up to the defined length (15)
nvarchar_col: 'Variable Unicode data',
nvarchar_max_col: 'Variable Unicode data MAX',
text_col: 'TextData',
ntext_col: 'UnicodeTextData'
};
expect(databaseRows[0]).toMatchObject(expectedResult);
expect(replicatedRows[0]).toMatchObject(expectedResult);
});
test('Binary types mappings', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
const binaryData = Buffer.from('BinaryData');
await connectionManager.query(
`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data (
binary_col,
varbinary_col,
varbinary_max_col,
image_col
) VALUES (
@binary_col,
@varbinary_col,
@varbinary_max_col,
@image_col
)
`,
[
{ name: 'binary_col', type: sql.Binary, value: binaryData },
{ name: 'varbinary_col', type: sql.VarBinary, value: binaryData },
{ name: 'varbinary_max_col', type: sql.VarBinary(sql.MAX), value: binaryData },
{ name: 'image_col', type: sql.Image, value: binaryData }
]
);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
const expectedBinary = new Uint8Array(binaryData);
const expectedBinaryPadded = new Uint8Array(16);
expectedBinaryPadded.set(expectedBinary.slice(0, 16), 0);
const expectedResult: SqliteInputRow = {
binary_col: expectedBinaryPadded,
varbinary_col: expectedBinary,
varbinary_max_col: expectedBinary,
image_col: expectedBinary
};
expect(databaseRows[0]).toMatchObject(expectedResult);
expect(replicatedRows[0]).toMatchObject(expectedResult);
});
test('Date types mappings', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
const testDate = new Date('2023-03-06T15:47:00.123Z');
// This adds 0.4567 milliseconds to the JS date, see https://github.com/tediousjs/tedious/blob/0c256f186600d7230aec05553ebad209bed81acc/src/data-types/datetime2.ts#L74.
// Note that there's a typo in tedious there. When reading dates, the property is actually called nanosecondsDelta.
// This is only relevant when binding datetime values, so only in this test.
Object.defineProperty(testDate, 'nanosecondDelta', {
enumerable: false,
value: 0.0004567
});
await connectionManager.query(
`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(
date_col,
datetime_col,
datetime2_col,
smalldatetime_col,
time_col
)
VALUES (
@date_col,
@datetime_col,
@datetime2_col,
@smalldatetime_col,
@time_col
)
`,
[
{ name: 'date_col', type: sql.Date, value: testDate },
{ name: 'datetime_col', type: sql.DateTime, value: testDate },
{ name: 'datetime2_col', type: sql.DateTime2(7), value: testDate },
{ name: 'smalldatetime_col', type: sql.SmallDateTime, value: testDate },
{ name: 'time_col', type: sql.Time(7), value: testDate }
]
);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
const expectedResult = {
date_col: '2023-03-06',
datetime_col: '2023-03-06T15:47:00.123000000Z',
datetime2_col: '2023-03-06T15:47:00.123456700Z',
smalldatetime_col: '2023-03-06T15:47:00.000000000Z',
time_col: '15:47:00.123456700'
};
expect(applyRowContext(databaseRows[0], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResult
);
expect(applyRowContext(replicatedRows[0], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResult
);
const restrictedPrecisionResult = {
date_col: '2023-03-06',
datetime_col: '2023-03-06T15:47:00.123000Z',
datetime2_col: '2023-03-06T15:47:00.123456Z',
smalldatetime_col: '2023-03-06T15:47:00.000000Z',
time_col: '15:47:00.123456'
};
const restrictedPrecision = new CompatibilityContext({
edition: 2,
maxTimeValuePrecision: TimeValuePrecision.microseconds
});
expect(applyRowContext(databaseRows[0], restrictedPrecision)).toMatchObject(restrictedPrecisionResult);
expect(applyRowContext(replicatedRows[0], restrictedPrecision)).toMatchObject(restrictedPrecisionResult);
});
test('Date types edge cases mappings', async () => {
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(datetime2_col)
VALUES ('0001-01-01 00:00:00.000')
`);
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(datetime2_col)
VALUES ('9999-12-31 23:59:59.999')
`);
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(datetime_col)
VALUES ('1753-01-01 00:00:00')
`);
const beforeLSN = await getLatestLSN(connectionManager);
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(datetime_col)
VALUES ('9999-12-31 23:59:59.997')
`);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const expectedResults = [
{ datetime2_col: '0001-01-01T00:00:00.000000000Z' },
{ datetime2_col: '9999-12-31T23:59:59.999000000Z' },
{ datetime_col: '1753-01-01T00:00:00.000000000Z' },
{ datetime_col: '9999-12-31T23:59:59.997000000Z' }
];
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
for (let i = 0; i < expectedResults.length; i++) {
expect(applyRowContext(databaseRows[i], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResults[i]
);
expect(applyRowContext(replicatedRows[i], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResults[i]
);
}
});
test('DateTimeOffset type mapping', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
// DateTimeOffset preserves timezone information
await connectionManager.query(`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(datetimeoffset_col)
VALUES ('2023-03-06 15:47:00.000 +05:00')
`);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const expectedResult = {
datetimeoffset_col: '2023-03-06T10:47:00.000000000Z' // Converted to UTC
};
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
// Note: The driver converts DateTimeOffset to Date, which incorporates the timezone offset which is then represented in UTC.
expect(applyRowContext(databaseRows[0], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResult
);
expect(applyRowContext(replicatedRows[0], CompatibilityContext.FULL_BACKWARDS_COMPATIBILITY)).toMatchObject(
expectedResult
);
});
test('UniqueIdentifier type mapping', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
const testGuid = createUpperCaseUUID();
await connectionManager.query(
`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(uniqueidentifier_col)
VALUES (@guid)
`,
[{ name: 'guid', type: sql.UniqueIdentifier, value: testGuid }]
);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
// GUIDs are returned as strings
expect(databaseRows[0].uniqueidentifier_col).toBe(testGuid);
expect(replicatedRows[0].uniqueidentifier_col).toBe(testGuid);
});
test('JSON type mapping', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
const expectedJSON = { name: 'John Doe', age: 30, married: true };
await connectionManager.query(
`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(json_col)
VALUES (@json)
`,
[{ name: 'json', type: sql.NVarChar(sql.MAX), value: JSON.stringify(expectedJSON) }]
);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
const actualDBJSONValue = JSON.parse(databaseRows[0].json_col as string);
const actualReplicatedJSONValue = JSON.parse(replicatedRows[0].json_col as string);
expect(actualDBJSONValue).toEqual(expectedJSON);
expect(actualReplicatedJSONValue).toEqual(expectedJSON);
});
test('XML type mapping', async () => {
const beforeLSN = await getLatestLSN(connectionManager);
const xmlData = '- value
';
await connectionManager.query(
`
INSERT INTO ${escapeIdentifier(connectionManager.schema)}.test_data(xml_col)
VALUES (@xml)
`,
[{ name: 'xml', type: sql.Xml, value: xmlData }]
);
await waitForPendingCDCChanges(beforeLSN, connectionManager);
const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
expect(databaseRows[0].xml_col).toBe(xmlData);
expect(replicatedRows[0].xml_col).toBe(xmlData);
});
// TODO: Update test when properly converting spatial types
// test('Spatial types mappings', async () => {
// const beforeLSN = await getLatestLSN(connectionManager);
// // Geometry and Geography types are stored as binary/WKT strings
// await connectionManager.query(`
// INSERT INTO [${connectionManager.schema}].test_data(geometry_col, geography_col)
// VALUES (
// geometry::STGeomFromText('POINT(1 2)', 0),
// geography::STGeomFromText('POINT(1 2)', 4326)
// )
// `);
// await waitForPendingCDCChanges(beforeLSN, connectionManager);
//
// const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
// const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
//
// // The driver currently returns spatial types as non standard objects. We just convert them to JSON strings for now
// expect(databaseRows[0].geometry_col).toBeDefined();
// expect(databaseRows[0].geography_col).toBeDefined();
// expect(replicatedRows[0].geometry_col).toBeDefined();
// expect(replicatedRows[0].geography_col).toBeDefined();
// });
// TODO: Enable when HierarchyID type is properly supported
// test('HierarchyID type mapping', async () => {
// const hierarchyid = '/1/';
// const beforeLSN = await getLatestLSN(connectionManager);
// await connectionManager.query(`
// INSERT INTO [${connectionManager.schema}].test_data(hierarchyid_col)
// VALUES (@hierarchyid)
// `,
// [{ name: 'hierarchyid', type: sql.VarChar, value: hierarchyid }]
// );
// await waitForPendingCDCChanges(beforeLSN, connectionManager);
//
// const databaseRows = await getDatabaseRows(connectionManager, 'test_data');
// const replicatedRows = await getReplicatedRows(connectionManager, 'test_data');
//
// const expectedBinary = new Uint8Array(Buffer.from(hierarchyid));
//
// expect(databaseRows[0].hierarchyid_col).toEqual(expectedBinary);
// expect(replicatedRows[0].hierarchyid_col).toEqual(expectedBinary);
// });
});
async function getDatabaseRows(
connectionManager: MSSQLConnectionManager,
tableName: string
): Promise {
const { recordset: rows } = await connectionManager.query(
`SELECT * FROM ${toQualifiedTableName(connectionManager.schema, tableName)}`
);
return rows.map((row) => {
const converted = toSqliteInputRow(row, rows.columns);
// Exclude id column from results
const { id, ...rest } = converted;
return rest;
});
}
/**
* Return all the updates from the CDC stream for the table.
*/
async function getReplicatedRows(
connectionManager: MSSQLConnectionManager,
tableName: string
): Promise {
const endLSN = await getLatestReplicatedLSN(connectionManager);
const captureInstances = await getCaptureInstances({
connectionManager,
table: {
schema: connectionManager.schema,
name: tableName
}
});
if (captureInstances.size === 0) {
throw new Error(`No CDC capture instance found for table ${tableName}`);
}
const captureInstance = Array.from(captureInstances.values())[0].instances[0];
const startLSN = captureInstance.minLSN;
// Query CDC changes
const { recordset: results } = await connectionManager.query(
`
SELECT * FROM ${CDC_SCHEMA}.fn_cdc_get_all_changes_${captureInstance.name}(@from_lsn, @to_lsn, 'all update old') ORDER BY __$start_lsn, __$seqval
`,
[
{ name: 'from_lsn', type: sql.VarBinary, value: startLSN.toBinary() },
{ name: 'to_lsn', type: sql.VarBinary, value: endLSN.toBinary() }
]
);
return results
.filter((row) => row.__$operation === 2) // Only INSERT operations
.map((row) => {
const converted = CDCToSqliteRow({ row, columns: results.columns });
// Exclude id column from results
const { id, ...rest } = converted;
return rest;
});
}