Project folder structure

The generated project has the following file/folder structure:

  • Database.sqlproj (file)
  • Database object type (folder)
  • Migrations (folder)
    • Baseline (folder)
  • Static Data (folder - optional)

    Database.sqlproj

    This file is a Visual Studio Database Project file. It can be opened with Visual Studio 2017 or newer. However, the scripted database files are not fully compatible with Visual Studio.

    The database project file contains project settings, such as:

    • Database comparison options:
      • Ignore permissions
      • Ignore collations
      • Ignore extended properties
      • Ignore index fill factor
      • Ignore Ansi Padding
      • Fire triggers during data sync
      • Detect column renames
    • Schema objects to include
    • Static data:
      • Scripting format
      • Tables to include
      • Hierarchy (for XML/JSON format)

      The database connection strings are not stored in the Database.sqlproj file.

      A folder for each database object type

      Each database object (that is being versioned) is scripted out as CREATE statement into object type folder, for example:

      • Functions
        • dbo.function1.sql
        • dbo.function2.sql
      • Stored Procedures
        • dbo.storedprocedure1.sql
        • dbo.storedprocedure2.sql
      • Tables
        • dbo.table1.sql
        • dbo.table2.sql
      • Views
        • dbo.view1.sql
        • dbo.view2.sql

      Migrations folder

      The generated and manually added migration scripts are stored in the Migrations folder. The scripts can be stored in subfolders, such as V1 or NewFeature - the only requirement is that script file names would be unique within the project.

      The Baseline subfolder is a special folder where Create database and Baseline scripts are stored.

      There are couple requirements for files inside the Migrations folder:

       An example of the migrations folder structure:

      • Baseline

        • V0__2020.01.01_000000__!Create database.sql

        • V0__2020.01.01_000000__Baseline.sql

        • V0__2020.02.01_000000__Baseline2.sql

      • V1

        • V1__2020.01.15_000000__V1.sql

        • V1__2020.01.20_000000__Bug fix.sql

      • V2__2020.03.01_000000__V2.sql

      Static Data

      Multiple static data scripting formats are supported.

      Default

      Each table (that is being versioned) is scripted out into a separate file using INSERT statements.

       An example of static data folder structure:

      • dbo.table1.sql
      • dbo.table2.sql
      • dbo.table3.sql

      XML/JSON

      Depending on scripting format, tables could be scripted as:

      • a single XML/JSON file per table
      • a single XML/JSON file per multiple tables
      • a subfolder with multiple XML/JSON files per table

      An example of static data folder structure:

      • dbo.TableA
        • part1.xml
        • part2.xml
        • part3.xml
      • dbo.TableB.xml
      • dbo.TableC.xml