Skip to content

Contains source code for database project and documentation on how to migrate from framework based project to SDK based project.

License

Notifications You must be signed in to change notification settings

iqan/sql-project-with-dacfx

Repository files navigation

Cross-platform SQL project with DacFx

Contains source code for database project and documentation on how to migrate from framework based project to SDK based project. This SQL Database project can be developed, built and published from windows, linux, osx or Docker container.

banner

Install SQL project templates

dotnet new install Microsoft.Build.Sql.Templates

Initialise new SQL project

dotnet new sqlproj -n Database.DacFx

If you are planning to keep both projects for any reason, copy database objects (.sql) scripts from existing project to new project

Add new objects (optional)

You can create new objects as plain .sql scripts and put it anywhere in the new project.

For example, here's Tables/User.sql script to create User table.

CREATE TABLE [dbo].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [varchar](50) NOT NULL
);

VS Code Tasks

VS Code tasks are configured to build database, publish database and run unit tests.

Task Description
clean database   clean bin and obj folders
build database   build database project and produces dacpac
publish database   uses dacpac produced from build to deploy on localhost
Run database in docker container   builds docker image with ready to use database
Run database unit tests in docker container   runs database unit tests using docker

Build dacpac

dotnet build

Publish

To local database using SQLPackage

Install SqlPackage as dotnet tool dotnet tool install -g microsoft.sqlpackage

sqlpackage /Action:Publish /SourceFile:"bin/Debug/Database.DacFx.dacpac" /TargetServerName:"(localdb)\MSSQLLocalDB" /TargetDatabaseName:Database.DacFx

Generated SQL scripts

Another way to deploy changes to database is to copy contents of script from bin/Debuig/Database.DacFx_Create.sql and run it using SSMS or sqlcmd tool.

Using Docker for debugging and testing in local environment

Build Image

docker build -t todo-database .

Run Image

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SuperSecretPassword#1" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   todo-database

Connect to SQL Server

  • Using SSMS

    • Server: localhost,1433
    • Authentication method: SQL Server Authentication
    • User: sa
    • Password: SuperSecretPassword#1
  • Connection String

    Server=localhost,1433;Database=Database.DacFx;User=sa;Password=SuperSecretPassword#1;TrustServerCertificate=true

Stop and Remove container

docker rm -f sql1

References

About

Contains source code for database project and documentation on how to migrate from framework based project to SDK based project.

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Languages