MSSQL connector
3 minute read
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 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.