Get started with the SQL Server integrations

Dieser Inhalt ist noch nicht in deiner Sprache verfügbar.

SQL Server is a relational database management system developed by Microsoft. The Aspire SQL Server integrations enable you to connect to existing SQL Server instances or create new instances from Aspire with the mcr.microsoft.com/mssql/server container image.

In this introduction, you’ll see how to install and use the Aspire SQL Server integrations in a simple configuration. If you already have this knowledge, see SQL Server Hosting integration for full reference details.

Set up hosting integration

To begin, install the Aspire SQL Server Hosting integration in your Aspire AppHost project. This integration allows you to create and manage SQL Server database instances from your Aspire hosting projects:

aspire add sqlserver

Die Aspire CLI ist interaktiv; das passende Suchergebnis wählen, wenn gefragt:

Select an integration to add:

> sqlserver (Aspire.Hosting.SqlServer)
> Other results listed as selectable options...

#:package Aspire.Hosting.SqlServer@*

<PackageReference Include="Aspire.Hosting.SqlServer" Version="*" />

Next, in the AppHost project, create instances of SQL Server server and database resources, then pass the database to the consuming client projects:

Select your programming language to get started

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");

var exampleProject = builder.AddProject<Projects.ExampleProject>("apiservice")
                            .WaitFor(sqldb)
                            .WithReference(sqldb);

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");

var exampleProject = builder.AddUvicornApp("api", "./api", "main.app")
                            .WithExternalHttpEndpoints()
                            .WaitFor(sqldb)
                            .WithReference(sqldb);

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");

var api = builder.AddNodeApp("api", "./api", scriptPath: "index.js")
                 .WithExternalHttpEndpoints()
                 .WaitFor(sqldb)
                 .WithReference(sqldb);

Use the integration in client projects

Now that the hosting integration is ready, the next step is to install and configure the client integration in any projects that need to use it.

Set up client projects

In each of these consuming client projects, install the Aspire SQL Server client integration:

dotnet add package Aspire.Microsoft.Data.SqlClient

#:package Aspire.Microsoft.Data.SqlClient@*

<PackageReference Include="Aspire.Microsoft.Data.SqlClient" Version="*" />

In the Program.cs file of your client-consuming project, call the AddSqlServerClient extension method on any IHostApplicationBuilder to register a SqlConnection for use via the dependency injection container. The method takes a connection name parameter.

builder.AddSqlServerClient(connectionName: "sqldb");

To interact with SQL Server databases in your Python consuming projects, you need to include a SQL Server adapter library. A popular option is pyodbc (the Python ODBC database driver). You can install this library using pip:

pip install pyodbc

Ensure that you import pyodbc in code files that interact with the database. You should also import the os module to access environment variables:

import pyodbc
import os

To interact with SQL Server databases in your JavaScript consuming projects, you need to include a SQL Server client library. A popular option is mssql (the official SQL Server client for Node.js). You can install this library using npm:

npm install mssql

Ensure that you import mssql in code files that interact with the database:

import sql from 'mssql';

Use injected SQL Server properties

In the AppHost, when you used the WithReference method to pass a SQL Server server or database resource to a consuming client project, Aspire injects several configuration properties that you can use in the consuming project.

Aspire exposes each property as an environment variable named [RESOURCE]_[PROPERTY]. For instance, the Uri property of a resource called sqldb becomes SQLDB_URI.

Use the GetValue() method to obtain these environment variables in consuming projects:

string hostname = builder.Configuration.GetValue<string>("SQLDB_HOST");
string port = builder.Configuration.GetValue<string>("SQLDB_PORT");
string jdbcconnectionstring = builder.Configuration.GetValue<string>("SQLDB_JDBCCONNECTIONSTRING");
string databasename = builder.Configuration.GetValue<string>("SQLDB_DATABASE");

Use the os.getenv() method to obtain these environment variables in consuming projects:

sql_host = os.getenv("SQLDB_HOST")
sql_port = os.getenv("SQLDB_PORT", "1433")
sql_user = os.getenv("SQLDB_USERNAME")
sql_password = os.getenv("SQLDB_PASSWORD")
sql_database = os.getenv("SQLDB_DATABASE", "sqldb")

Use the process.env method to obtain these environment variables in consuming projects:

const sqlHost = process.env.SQLDB_HOST;
const sqlPort = process.env.SQLDB_PORT;
const sqlUser = process.env.SQLDB_USERNAME;
const sqlPassword = process.env.SQLDB_PASSWORD;
const sqlDatabase = process.env.SQLDB_DATABASE;

Use SQL Server resources in client code

Now that you’ve added SqlConnection to the builder in the consuming project, you can use the SQL Server resource to get and store data. Get the SqlConnection instance using dependency injection. For example, to retrieve your connection object from an example service define it as a constructor parameter and ensure the ExampleService class is registered with the dependency injection container:

public class ExampleService(SqlConnection connection)
{
    // Use connection to query the database...
}

Having obtained the connection, you can work with the SQL Server database as you would in any other C# application.

Use the information you have obtained about the SQL Server resource to connect to the database. Here is an example of how to connect using pyodbc:

connection_string = (
    "DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={sql_host},{sql_port};"
    f"DATABASE={sql_database};"
    f"UID={sql_user};"
    "PWD={sql_password};"
)

connection = pyodbc.connect(connection_string)
cursor = connection.cursor()

Having obtained the connection, you can work with the SQL Server database as you would in any other Python application.

Use the information you have obtained about the SQL Server resource to connect to the database. Here is an example of how to connect using mssql:

const config = {
  server: sqlHost,
  port: parseInt(sqlPort),
  user: sqlUser,
  password: sqlPassword,
  database: sqlDatabase
};

const pool = await sql.connect(config);

Having obtained the connection, you can work with the SQL Server database as you would in any other JavaScript application.

Next steps

Now that you have an Aspire app with SQL Server integrations up and running, you can use the following reference documents to learn how to configure and interact with the SQL Server resources:

Understand the SQL Server hosting integration Discover how to configure persistence, database scripts, and more.

Understand the SQL Server client integration Discover how to configure keyed clients, client integration health checks, and more.

Understand the SQL Server community extensions Discover how to add the DbGate management user interface, and more.