Connect to a Database

This topic covers the following areas:

Setting service bindings

Service bindings allows runtime configuration values for one service group to be shared with another service group, forming a producer/consumer relationship. When you want to share out configuration data with another service group, you must add the pkg_exports setting to your plan.

The following shows how a plan.ps1 exports a port key for consuming services, such as a reverse proxy or load balancer.

$pkg_exports=@{"port"="port"}

This will expose the following from the default.toml:

port = 8099

Conversely, if your web application wants to bind to a key, such as a port from a database, you need to either use the pkg_binds or pkg_binds_optional settings in your plan. This requires that you know what keys are exported by the producer service group.

You can find exported keys by searching for the package at https://bldr.habitat.sh/#/pkgs/core and viewing the manifest for a given release of a package.

If the producer service is running, you can also retrieve this information by connecting to the HTTP API endpoint for the service and filtering the results. The following retrieves the exported keys and their associated configuration values from a running sqlserver service.

[HAB-STUDIO] Chef Habitat:\src> ((Invoke-WebRequest http://127.0.0.1:9631/services/sqlserver/default -UseBasicParsing).Content | ConvertFrom-Json).pkg.exports
instance password port username
-------- -------- ---- --------
instance app_password port app_user

Once you have identified the key or keys of the producer that you want to consume in your web application, add that information to your plan file.

$pkg_binds=@{ "database"="username password port" }

At this point the first 16 lines of your plan.ps1 file (all lines above the build callbacks) should look like the following:

$pkg_name="contosouniversity"
$pkg_origin="myorigin"
$pkg_version="0.1.0"
$pkg_maintainer="The Chef Habitat Maintainers <humans@habitat.sh>"
$pkg_license=@("Apache-2.0")
$pkg_deps=@(
"core/dotnet-45-runtime",
"core/iis-aspnet4",
"core/dsc-core"
)
$pkg_build_deps=@(
"core/nuget",
"core/dotnet-45-dev-pack",
"core/visual-build-tools-2017"
)
$pkg_binds=@{"database"="username password port"}

For more examples and information on binding, see Runtime Binding.

Defining database connection logic

At the most basic level, connecting to a database like SQL Server from a ASP.NET application requires setting the connection string parameters in the application's web.config file. One way to do this is to maintain your web.config as a templated configuration file in your plan's config directory. You can then link that file at runtime to your application root. Let's move our web.config from the root of our application source and into our habitat/config folder. We will also need to delete all ASP.NET web.config files from source and remove them from the project's .csproj file as well.

PS C:\contosouniversity> Copy-Item .\C#\ContosoUniversity\Web.config .\habitat\config\
PS C:\contosouniversity> Remove-Item .\C#\ContosoUniversity\Web*.config
PS C:\contosouniversity> [xml]$xml = Get-Content .\C#\ContosoUniversity\ContosoUniversity.csproj
PS C:\contosouniversity> $nodes = $xml.Project.ItemGroup.Content | ? { $_.Include -like "Web.*" }
PS C:\contosouniversity> $nodes | % { $_.ParentNode.RemoveChild($_) }
PS C:\contosouniversity> $f = Resolve-Path .\C#\ContosoUniversity\ContosoUniversity.csproj
PS C:\contosouniversity> $xml.Save($f)

By placing the file in the config folder, it will get copied into the /hab/svc/contosouniversity/config directory at runtime and rendered with the appropriate values. We can then use Handlebars helper logic and the Data Source, User ID, and Password values will be derived from the database service binding information gossiped around the Supervisor ring between the web application and database service groups. Let's change the connectionStrings element of our habitat/config/web.config file to the following:

<connectionStrings>
<add name="SchoolContext" connectionString="Data Source={{bind.database.first.sys.ip}},{{bind.database.first.cfg.port}};Initial Catalog=ContosoUniversity2;User ID={{bind.database.first.cfg.username}};Password={{bind.database.first.cfg.password}};" providerName="System.Data.SqlClient" />
</connectionStrings>

In order for our templatized web.config to be seen by the ASP.NET runtime, we need to link the Supervisor rendered file to the root of our application which is where ASP.NET expects it to be. We can do this by adding the following to our init hook:

Set-Location {{pkg.svc_path}}\var
New-Item -Name Web.config -ItemType SymbolicLink -target "{{pkg.svc_config_path}}/Web.config" -Force | Out-Null

Lastly we need to edit the permissions of the Chef Habitat rendered web.config template so that the IIS app pool user has the rights to access it. When Chef Habitat renders the template files in out plan's config and hooks directories, the permissions granted to the rendered files include Full Control access for Administrators, SYSTEM, and the user running the Supervisor process. Because these rendered files could possibly include sensitive values, we do not want to grant access to all users. By default, IIS will run our application using a user named after its application pool and this user will not have rightrs to view the rendered web.config which will cause our application to fail. So lets edit our run hook after we apply the DSC configuration that creates and starts our application pool:

Start-DscCore (Join-Path {{pkg.svc_config_path}} website.ps1) NewWebsite
$pool = "{{cfg.app_pool}}"
$access = New-Object System.Security.AccessControl.FileSystemAccessRule "IIS APPPOOL\$pool", "ReadAndExecute", "Allow"
$acl = Get-Acl "{{pkg.svc_config_path}}/Web.config"
$acl.SetAccessRule($access)
$acl | Set-Acl "{{pkg.svc_config_path}}/Web.config"
try {
...

Connecting to a database running inside the studio

This is the easiest scenario and useful for local testing of service binding between your database and application package. It includes three steps:

  • Load SQL Server in the Supervisor
  • Build our plan
  • Load our package and bind it to SQL Server
[HAB-STUDIO] Chef Habitat:\src> hab svc load core/sqlserver
[HAB-STUDIO] Chef Habitat:\src> build
[HAB-STUDIO] Chef Habitat:\src> hab svc load myorigin/contosouniversity --bind database:sqlserver.default

Note The first time that the core/sqlserver package is loaded and started, it may take several minutes to initialize because its install hook actually runs the SQL Server installer. This will cause the third command above that loads our application to hang until that initialization completes.

Connecting to a database running inside a Docker container

Export the core/sqlserver package to a Docker image and then export your application's build .hart file.

PS C:\contosouniversity> hab pkg export docker core/sqlserver
PS C:\contosouniversity> . ./results/last_build.ps1
PS C:\contosouniversity> hab pkg export docker ./results/$pkg_artifact

Note You can only export Docker images in an environment where a Docker engine is running. Therefore you cannot export inside of a container based studio. You must exit the studio and export inside your Docker host environment.

As the Docker exporter builds these images, it will run all install hooks in the application dependency tree. This means that the Docker build operation could take a while as it runs the hook, but running containers from this built image may be significantly faster because all install operations are baked into the image.

Once you've created Docker images for both your web application and your database, you can start them up via docker run.

PS C:\contosouniversity> $sql = docker run --env HAB_LICENSE="accept-no-persist" -d --memory 2GB core/sqlserver
PS C:\contosouniversity> $ip = docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $sql
PS C:\contosouniversity> docker run --env HAB_LICENSE="accept-no-persist" -it -p 80:8099 myorigin/contosouniversity --peer $ip --bind database:sqlserver.default

You should now be able to access the web application and receive a 200 OK response:

PS C:\contosouniversity> Invoke-WebRequest http://localhost/contoso_university -UseBasicParsing -Method head
Next: Next Steps