fingertips-db

fingertips-db

This directory contains a SQL database project that defines the structure of the Fingertips database, as well as any pre or post-deployment scripts used. It can be built into a .dacpac file that can be applied to a SQL Server database to allow for repeatable database deployments.

Prerequisites

You'll need to following tools to work on the database project:

• .Net 8
• A suitable IDE
  • On Windows, if you have Visual Studio, you can use SQL Server Data Tools
  • You can also use Visual Studio Code or Azure Data Studio with the SQL Database Projects extension on all platforms.
• SqlPackage CLI (if you want to deploy the .dacpac from the command line)

Building

You can build a .dacpac file from this project by running the following command from this directory:

dotnet build fingertips-db.sqlproj

This will produce a fingertips-db.dacpac file in the bin/Debug/ directory.

Deploying

Once you have built a .dacpac file (see building above) you can deploy it to a SQL Server instance using SqlPackage using the following command:

sqlpackage \
    /Action:publish \
    /SourceFile:bin/Debug/fingertips-db.dacpac \
    /TargetServerName:<DB server hostname>,<DB server port> \
    /TargetDatabaseName:<database name> \
    /TargetUser:<DB username> \
    /TargetPassword:<DB password>

If your database server is using a self-signed certificate (e.g. you are running one locally in a container) you will need to add the /TargetTrustServerCertificate:True option to disable certificate verification.

Build/Deployment Using a Container

A Dockerfile is provided to build a .dacpac file and produce a container image that will run the .dacpac against a SQL Server. You can build and run a container by doing the following:

1. Install Docker on your machine, if required
2. Build the container: docker build -t fingertips-db .
3. Run your container: docker run -e DB_SERVER="<DB server hostname>" -e DB_PORT="<DB server port>" -e DB_NAME="<database name>" -e SA_USERNAME="DB username" -e SA_PASSWORD="DB password" fingertips-db

Note: If your database server is using a self-signed certificate (e.g. you are running one locally in a container) you will need to add the -e TRUST_CERT="True" flag to disable certificate verification.