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

To install the MSSQL connector, execute the following command:

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

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 database tables will be listed in the Models tab of the console. Now, you can 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 example, to map the account model to the accounts table set it as:

const Account = APIBuilder.Model.extend('account', {
  fields: {
    Name: { type: String, required: false, validator: /[a-zA-Z]{3,}/ }
  },
  connector: 'mssql',
  metadata: {
    'mssql': {
      table: 'accounts'
    }
  }
});

Known issues and limitations

For a list of known issues and limitations, refer to the API Builder Release Notes.

Related Links