@openfn/language-mssql
v5.0.7
Published
A Microsoft SQL language pack for OpenFn
Downloads
312
Keywords
Readme
Language MSSQL
Language Pack for connecting to Azure SQL Server via OpenFn.
Documentation
Configuration
View all the required and optional properties for state.configuration
in the
official
configuration-schema
definition.
Sample expression
sql query
sql({
query: `
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_CATALOG='my-demo'
`,
});
sql({
query: `SELECT * FROM Household`,
options: {
writeSql: true, // Keep to true to log query (otherwise make it false).
execute: true, // keep to false to not alter DB
},
});
Find a single value for a table
This helper function allows to build a specific query where sql
would not be
best suited. It returns a single value and not a promise. An example of usage
would be in building a mapping object with a value from a lookup table.
fn(async state => {
const user = {
id: 1,
name: 'Mamadou',
user_id: await findValue({
uuid: 'id',
relation: 'users',
where: { first_name: 'Mama%' },
operator: { first_name: 'like' }, // operator is optional. "=" is used by default.
})(state),
};
return upsert(...)(state);
});
Insert one single record
insert(
'SomeDB.dbo.SupplierTest',
{
SupplierNumber: 1,
Name: dataValue('name'),
Address: 'Nunya Bihz-Nash',
},
{
// The optional `options` argument allows for global string replacement with
// NULL. This is useful if you want to map an undefined value (e.g., x.name)
// to NULL. It can be a single string or an array of strings.
// It DEFAULTS to "'undefined'", and can be turned off w/ `false`.
setNull: "'undefined'",
logValues: true,
}
);
Insert or Update using a unique column as a key
This function insert or update depending on the existence of a record in the database.
upsert(
'SomeDB.dbo.Supplier',
'SupplierNumber',
{
SupplierNumber: 1,
Name: dataValue('name'),
Address: 'Now I can tell!',
},
// Do NOT replace any instances of 'undefined' in the final SQL statement.
{ setNull: false, logValues: true }
);
Insert or Update if a value exist in the record
This function will upsert a record only if the logical given is true. In this
case we check if dataValue('name')
exists.
upsertIf(
dataValue('name'),
'users',
'user_id',
{
name: 'Elodie',
id: 7,
},
// Replace any occurence of '' and 'undefined' to NULL
{
setNull: ["''", "'undefined'"],
writeSql: true,
execute: false,
logValues: true,
}
);
Insert Many records
This function allows the insert of a set of records inside a table all at once.
Pass logQuery
option to true
to display the query.
// Note that insertMany takes a function which returns an array—this helps
// enforce that each item in the array has the same keys.
insertMany(
'SomeDB.dbo.Supplier',
state =>
state.data.supplierArray.map(s => {
return {
SupplierNumber: s.id,
Name: s.name,
Address: s.address,
};
}),
{ writeSql: true, logValues: true }
);
Insert or Update Many records
This function inserts or updates many records all at once depending on their existence in the database.
// Note that insertMany takes a function which returns an array—this helps
// enforce that each item in the array has the same keys.
upsertMany(
'SomeDB.dbo.Supplier',
'SupplierNumber',
state =>
state.data.supplierArray.map(s => {
return {
SupplierNumber: s.id,
Name: s.name,
Address: s.address,
};
}),
{ writeSql: true, execute: false, logValues: true }
);
In case we need to check on multiple columns before upserting, we can have an
array of uuids
.
upsertMany(
'SomeDB.dbo.Supplier',
['SupplierNumber', 'SupplierCode'],
state =>
state.data.supplierArray.map(s => {
return {
SupplierNumber: s.id,
Name: s.name,
Address: s.address,
SupplierCode: s.code,
};
}),
{ writeSql: true, execute: false, logValues: true }
);
Describe a table from mssql
This function is used to fetch the list of columns of a given table in the database.
describeTable('users', { writeSql: false, execute: true });
Create a table in the database
This function allows to create a table in a database from a given array of
columns. The key identity
can be use for a column to auto-generate a value.
insertTable('users', state =>
state.data.map(column => ({
name: column.name,
type: column.type,
required: true, // optional
unique: false, // optional - set to true for unique constraint
}))
);
Alter a table in the database
This function allows to add new columns to a table. Beware of the fact that you cannot add new columns with names that already exist in the table.
modifyTable(
'users',
state =>
state.data.map(newColumn => ({
name: newColumn.name,
type: newColumn.type,
required: true, // optional
unique: false, // optional - set to true for unique constraint
})),
{ writeSql: false, execute: true }
);
Development
Clone the adaptors monorepo. Follow the
Getting Started
guide inside to get set up.
Run tests using pnpm run test
or pnpm run test:watch
Build the project using pnpm build
.
To just build the docs run pnpm build docs