mssql.md

Quick Start Guide for Microsoft SQL Server

Table of Contents

• Initialize Project
• Creating Your First Migration
• Creating Objects (Functions, Procedures or Views)
• Publishing

Initialize Project

The first thing you will want to do is create a drift.yml configuration file for your project. This is really simple all you need to do is run the following command.

$ drift init

This will create the drift.yml file in the current directory and configure it to use mssql and store your script and object files in the ./.drift folder. The basic folder structure that will be used by Drift in your project would look something like this.

my-project
├── .drift
│   ├── migrations
│   ├── mssql
│   │   ├── functions
│   │   ├── procedures
│   │   └── views
│   └── postDeploy
└── drift.yml

For a more in-depth look at the options when creating a new project, take a look at the Creating a Project Guide.

Creating Your First Migration

Now it is time to create your first migration script. Migrations are used to define the structure of your database for things like schemas, tables and indexes. To create a new migration script you just need to run Drift's create migration command.

$ drift create migration my-first-migration
Created File: mssql: C:/repos/my-project/.drift/migrations/1552675076196-my-first-migration.js

Drift migration scripts are written in Javascript. This command will create a template script file for you. For an in-depth look at creating migrations, take a look at the Writing Migration and Post Deployment Scripts Guide.

Below is an example of the migration script that would create a simple Person table.

/******************************************************************************
 * Migration: 1552675076196-my-first-migration
 *****************************************************************************/
async function exec(context, Sequelize, provider, replacements) {
  await context.createTable('Person', {
    id: {
      type: Sequelize.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    firstName: {
      allowNull: false,
      type: Sequelize.STRING(100)
    },
    lastName: {
      allowNull: false,
      type: Sequelize.STRING(100)
    }
  })
}

exports.exec = exec

Creating Objects

In Drift, database objects for Functions, Procedures and Views are written in SQL files. Additionally, a separate SQL file is created for each database provider used in a project. An object SQL file should contain the command to create a single object.

For Microsoft SQL Server, you only need to write the create command. The Drift publisher will translate the create command to an alter command automatically if the object already exists in the database.

Drift provides several commands that will generate placeholder object SQL files for you.

# Create a scalar function
$ drift create function my-cool-scalar-function
Created file: mssql: E:/repos/drift-test/.drift/mssql/functions/my-cool-scalar-function.sql

# Create a table valued function
$ drift create function my-table-function --table
Created file: mssql: E:/repos/drift-test/.drift/mssql/functions/my-table-function.sql

# Create a stored procedure
$ drift create procedure my-awesome-procedure
Created file: mssql: E:/repos/drift-test/.drift/mssql/procedures/my-awesome-procedure.sql

# Create a view
$ drift create view my-nifty-view
Created file: mssql: E:/repos/drift-test/.drift/mssql/views/my-nifty-view.sql

For an in-depth look at creating objects, take a look at the Creating Functions, Procedures, and Views Guide.

Publishing

Drift provides a publish command that will apply the scripts and objects you have created to a database server. If the database does not exist, the user will need to have permissions to create the database. Otherwise, the user only needs be the db_owner of the database.

$ drift publish mssql -h localhost -d MyProjectDb -u my-user -p my-super-secure-password

For an overview of publishing process and an advanced look at the publishing options, see the Publishing Guide.