@axway/api-builder-plugin-dc-mssql
v4.0.0
Published
MSSQL plugin
Downloads
91
Readme
Microsoft SQL Server (MSSQL) Connector
The Microsoft SQL Server data connector is a plugin for API Builder that can connect to your MSSQL instance and interrogate your schema that will automatically provision Models into to your project, and optionally, automatically generate a rich CRUD API to the underlying tables. The Models can be used programmatically, or can be used within the flow editor to interact with your database.
Minimum requirements
The following are the supported versions and features and the approximate memory and disk space requirements.
Supported versions
- SQL Server 2008 or later
Memory
- Approximately 8 MB
Disk space
- Approximately 15 MB
Supported features
- Automatic generation of Models from SQL tables
- Automatic generation of API for Models
- Full CRUD operations on tables via Models
Installation
npm install --no-optional @axway/api-builder-plugin-dc-mssql
A configuration file is generated for you and placed into the ./conf
directory of your API Builder project. By default, we use a host of localhost
, and expect user and password to come from the OS host environment.
Configuration
Once the plugin is installed, the configuration file is located in
<project>/conf/mssql.default.js
.
| Option name | Type | Description |
| ----------- | ---- | ----------- |
| connector | string | Must be: @axway/api-builder-plugin-dc-mssql
|
| user | string | The user with which to connect to the database. |
| password | string | The user's password with which to connect to the database. |
| host | string | The database host. |
| port | number | The database post. |
| database | string | The database instance name. |
| connectionTimeout | number | Connection timeout in MS |
| requestTimeout | number | Request timeout in MS |
| generateModelsFromSchema | boolean | If enabled, API Builder will automatically interrogate the database and auto-generate Models from SQL tables. |
| modelAutogen | boolean | If enabled, API Builder will automatically generate a full and rich CRUD API from the generated Models. |
| options | object | MSSQL Connection options |
| options.encrypt | boolean | Encrypt the database connection. Required when connecting to Azure. |
Usage
After you configure the connector, you can start up your API Builder project and visit the console (normally found under http://localhost:8080/console). Your connector will also be listed as a Connector when creating a new model in the console.
Your database tables will be listed under the Models section of the console. You can now click on the gear icon to the right of the table names and generate flow based APIs.
You can also reference the connector in a custom model.
const Account = APIBuilder.Model.extend('Account', {
fields: {
Name: {
type: String,
required: true
}
},
connector: 'mssql'
});
If you want to map a specific model to a specific table, use metadata for schema
and table
name. For example, to map the account
model to the table named the table "[dbo].[accounts]":
const Account = APIBuilder.Model.extend('account', {
fields: {
Name: {
type: String,
required: false,
validator: /[a-zA-Z]{3,}/
}
},
connector: 'mssql',
metadata: {
schema: 'dbo',
table: 'accounts'
}
});
If your table has a primary key, then you can use additional metadata:
const Account = APIBuilder.Model.extend('account', {
fields: {
Name: {
type: String,
required: false,
validator: /[a-zA-Z]{3,}/
}
},
connector: 'mssql',
metadata: {
schema: 'dbo',
table: 'accounts',
primarykey: 'Name',
primaryKeyDetails: {
autogenerated: false,
type: 'varchar'
}
}
});
Known issues and limitations
- Does not support range queries
For a list of known issues and limitations, refer to the API Builder known issues.
Changes
4.0.0
- #6089: Breaking change: requires minimum Node.js version 16.x.
3.3.2
- #7412: Pin in-house dependencies.
3.3.1
- #7408: Internal bump.
3.3.0
- #7371: Added support for tables that are not part of the configured user's default schema.
3.2.0
- #6933: Replace peerDependency on @axway/api-builder-runtime with
engines.apibuilder
.
3.1.0
- #7084: Fixed security issue CVE-2021-3807 with
ansi-regex
. - #7084: Fixed issue querying records with falsy values, (e.g.
{ value: 0 }
). - #7084: Fixed issue querying records expecting values to be equal to
null
(e.g.{ value: null }
). - #7084: Fixed issue querying records expecting values to be not equal to
null
(e.g.{ value: $ne: { null } }
).
3.0.4
- #7008: Fixed the issue where the plugin configuration file is not installed correctly if the project
conf
directory does not exist.
3.0.3
- #6934: Internal refactoring around code style.
3.0.2
- #6459: Added
@axway/api-builder-project-utils
to handle copying config to a project after installing this plugin.
3.0.1
- #6837: Updated driver dependency. Now using tedious directly.
3.0.0
- #6837: Breaking change: requires minimum Node.js version 12.x.
- #6837: Breaking change: The driver encrypts connections to SQL Server by default, but self-signed certificates, or certificates not matching SNI, unknown certificates, or otherwise invalid certificates, will not be trusted and connections will fail. For testing, it may be necessary to set
options.trustServerCertificate
totrue
. - #6837: Breaking change: Passing unknown properties to connection pool configuration now throws errors (see https://github.com/Vincit/tarn.js/issues/19 for details);
- #6837: Breaking change:
beforeDestroy
pool configuration option was removed. You should use tarn.js event handlers if you still need similar functionality. - #6837: Breaking change: Connection URL parsing changed from legacy url.parse to WHATWG URL. If you have symbols, unusual for a URL (not A-z, not digits, not dot, not dash) - check Node.js docs for details
- #6837: Upgraded mssql to 7.x.x, see changelog for details of changes.
- #6837: Upgraded knex to 0.95.x, see changelog for details of changes.
2.0.0
- #6545: Upgraded knex to 0.21.x, see changelog for details of changes.
- #6545: Upgraded mssql to 6.x.x, see changelog for details of changes.
1.1.4
- #6315: Internal chore.
1.1.3
- #6116: Internal cleanup chore.
1.1.2
- #6114: Refactor code for style, security and performance improvements.
1.1.1
- #6115: Internal fix for Sonar scans.
1.1.0
- #6093: Removed the explicit maximum supported Node.js version limitation in package.json.
1.0.13
- #6026: Documents ranges of supported Node.js versions in package.json
1.0.12
- #6074: Internal CI chore
1.0.11
- #5704: Fixes issue where distinct failed to paginate with error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
1.0.10
- #5711: Internal cleanup of dependencies.
1.0.9
- #5711: Internal cleanup of npm scripts.
1.0.8
- #5715: Internal changes to remove integration tests.
1.0.7
- #5709: Internal changes to update eslint rules.
1.0.6
- #5707: Internal cleanup to code coverage during build process.
1.0.0
- #4544: Initial Implementation
License
This code is proprietary, closed source software licensed to you by Axway. All Rights Reserved. You may not modify Axway’s code without express written permission of Axway. You are licensed to use and distribute your services developed with the use of this software and dependencies, including distributing reasonable and appropriate portions of the Axway code and dependencies. Except as set forth above, this code MUST not be copied or otherwise redistributed without express written permission of Axway. This module is licensed as part of the Axway Platform and governed under the terms of the Axway license agreement (General Conditions) located here: https://support.axway.com/en/auth/general-conditions; EXCEPT THAT IF YOU RECEIVED A FREE SUBSCRIPTION, LICENSE, OR SUPPORT SUBSCRIPTION FOR THIS CODE, NOTWITHSTANDING THE LANGUAGE OF THE GENERAL CONDITIONS, AXWAY HEREBY DISCLAIMS ALL SUPPORT AND MAINTENANCE OBLIGATIONS, AS WELL AS ALL EXPRESS AND IMPLIED WARRANTIES, INCLUDING BUT NOT LIMITED TO IMPLIED INFRINGEMENT WARRANTIES, WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE, AND YOU ACCEPT THE PRODUCT AS-IS AND WITH ALL FAULTS, SOLELY AT YOUR OWN RISK. Your right to use this software is strictly limited to the term (if any) of the license or subscription originally granted to you.