Creating SQL Server database project from existing database

Many enterprise solutions have an existing database in place. To start working with SQL Server project developers have to migrate existing database in it. In this article, I will describe how to migrate the existing database to the database project.

Creating schema compare files

Using Schema comparison tool in Visual Studio, it is possible to compare existing database schema and the schema presented in the project. To start comparison process right-click on project → Schema Compare.

 

After that new windows will open. On that screen, we need to select the source and the target databases. Click on 'Select source' drop-down list, then select 'Select source' option.

On opened dialog select 'Database' radio button and press 'Select Connection' button. After that, you have to provide database connection information.

For 'Target database' select project option.

Click on 'Compare' button. It might take some time to generate differences, but after that, you will see all items which should be updated in the target database.

We will save this Schema compare in our solution. It is better to do that because you will rather frequently run the comparison. It will save some time for you in future. You can have multiple 'schema compare' items, e.g., for local database, staging or production databases. I create the _schemaCompare folder in solution to store schema compare items there. Click save schema and save it to created folder and after that add a new item to solution folder.

Configuring schema compare

Sometimes you need to exclude some objects from schema compare or set it in some specific way. It is possible to do. Click on 'Gear' button on the top panel. 

After you configure the schema compare save it and check into project repository. Your schema compare will be available for all team members.

Creating database project from existing database

Now we are ready to create new database project from existing database. Double-click on local.compare.scmp file → Click Compare button → Click Update button → Done!

Conclusion 

In this article, I showed how to work with schema compare in SQL Server database project and how to create database project from existing database using schema compare.  Source code example for this tutorial could be found using the following link: https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/05-Create-Database-Project-from-Database.

Setting up Visual Studio Code for TypeScript development

Install TypeScript compiler

To setup TypeScript on your computer, you need to setup node.js with npm. There is a good article, which will help you to do that, please find it using following link: Installing Node.js and updating npm. 

We just installed node.js and updated npm. So we are ready to install TypeScript on your computer. If you want you can install the latest build of TypeScript. To do that you have to run following command:

npm install -g typescript@next #install nightly build

Although you have the possibility to install nightly builds, I would recommend installing the latest stable version of the language. To do that type following command in cmd.exe and hit enter:

npm install -g typescript #install the latest stable version

Install Visual Studio Code with extensions

Visual Studio Code

Let's install Visual Studio Code first. You can download and install it from https://code.visualstudio.com.

TSLint

An extensible linter for the TypeScript language. TSLint supports:

• custom lint rules
• custom formatters (failure reporters)
• inline disabling / enabling of rules
• configuration presets (tslint:latest, tslint-react, etc.) & composition

For more info please use http://palantir.github.io/tslint/

To install TSLint extension: Open Visual Studio Code → Navigate to Extensions tab on left sidebar → Type 'TsLint' in search text-box → Install extension.

To make extension work you need to install tslint globally. To do that run in cmd.exe tool the command:

npm install -g tslint #installing tslint globally

Gulp Snippets

We are going to use Gulp to compile our TypeScript code, so let's install Visual Studio Code extension to help us with Gulp syntax.
Open Visual Studio Code → Navigate to Extensions tab on left sidebar → Type 'Gulp Snippets' in search text-box → Install extension.

Creating the project

First of all, you need to create the project folder. I will create mine with the name "TypeScriptBlog". Create the folder and open it in cmd.exe tool. Now we need to create package.json file. To do that run in cmd.exe command:

cd path/to/project
npm init

Open created folder in Visual Studio Code and check created file. It should contain something like this:

{
  "name": "typescriptblog",
  "version": "1.0.0",
  "description": "Blog engine written on TypeScript",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "MIT"
}

We are now ready to install the third party dependencies:

npm install  --save-dev typescript
npm install  --save-dev tslint 
npm install  --save-dev gulp
npm install  --save-dev gulp-tslint
npm install  --save-dev gulp-typescript

Compiling the project

Create gulpfile.js in the project's  folder and copy following  content to it:

var gulp = require("gulp"),
    tslint = require("gulp-tslint"),
    tsc = require("gulp-typescript");

var tsProject = tsc.createProject("tsconfig.json");

gulp.task("build-app", function () {
    return gulp.src([
        "app/**/**.ts"
    ])
        .pipe(tsc(tsProject))
        .js.pipe(gulp.dest("app/"));
});

// Rebuild project on any ts file changed
gulp.task('watch', function() {
  gulp.watch("app/**/**.ts", ['build-app']);
});

Create tsconfig.json file and copy following content to it:

{
    "compilerOptions": {
        "sourceMap":  true
    }
}

Now we can build our application. Press Ctrl + Shift + P in Visual Studio Code → Type "Run Task" → run "build-app" task. If you want to rebuild your code each time when any file changed, execute task with name "watch".

To test that the Gulp is working you can use following files. Create app folder in the root folder and add files to app folder.

greeter.ts

class Greeter {
    constructor(public greeting: string) { }
    greet() {
        return "<h1>" + this.greeting + "</h1>";
    }
};

var greeter = new Greeter("Hello, world!");
    
document.body.innerHTML = greeter.greet();

greeter.html

<!DOCTYPE html>
<html>
  <head><title> TypeScript Greeter </title></head>
  <body>
    <script src='greeter.js'></script>
  </body>
</html>

Project structure

Linting the project

Earlier we installed all necessary tools for linting. Now it is time to configure them. Open cmd.exe and execute command:

cd path/to/project
tslint --init

After that you can find that tslint.json file was added to the solution. This file contains default linting settings, if you would like to extend or change them, please use https://palantir.github.io/tslint/rules/ for more info.
One more thing what we can do. We want tslint be executed using Gulp, so open gulpfile.js file and add the following code to it:

// Linting the project
gulp.task("lint", function () {
    return gulp.src([
        "app/**/**.ts"
    ])
        .pipe(tslint({
            formatter: "verbose"
        }))
        .pipe(tslint.report())
});

If 'lint' task does not appear in the task list, restart Visual Studio Code and try again.

Сonclusion

Now we know how to setup Visual Studio Code with TypeScript. With this article, I open a cycle of articles about TypeScript, in which we are going to create blog engine written on TypeScript. All the code you can find under repository https://github.com/aliakseimaniuk/TypescriptBlog.