Edit

Share via

Create a SQL database with the REST API for Microsoft Fabric

Applies to: ✅ SQL database in Microsoft Fabric

You can use the Fabric REST API to deploy and manage resources, include SQL databases in Fabric.

This article and sample script demonstrate how to use PowerShell to call the Fabric REST API to deploy a Fabric SQL database.

Prerequisites

Create a new SQL database via REST API

This example script uses Connect-AzAccount, an alias of az login to prompt for credentials. It uses those credentials to obtain an access token to use for the REST API calls. SQLCMD uses the context of the account that was given to Connect-AzAccount.

The script creates a database named with the logged-in user's alias and the date. Currently, the REST API doesn't return a status so we must loop and check for the database to be created. After the database is created, SQLCMD is used to create some objects and then query for their existence. Finally, we delete the database.

In the following script, replace <your workspace id> with your Fabric workspace ID. You can find the ID of a workspace easily in the URL, it's the unique string inside two / characters after /groups/ in your browser window. For example, 11aa111-a11a-1111-1abc-aa1111aaaa in https://fabric.microsoft.com/groups/11aa111-a11a-1111-1abc-aa1111aaaa/.

This script demonstrates:

  1. Retrieve an access token using Get-AzAccessToken and convert it from a secure string. If using PowerShell 7, ConvertFrom-SecureString is also an option.
  2. Create a new SQL database using the Items - Create Item API.
  3. List all SQL databases in a Fabric workspace.
  4. Connect to the database with SQLCMD to run a script to create an object.
  5. Delete the database using the Items - Delete Item API.
Import-Module Az.Accounts

az login

$workspaceid = '<your workspace id>'

$databaseid = $null 
$headers = $null
$responseHeaders = $null 

# 1. Get the access token and add it to the headers

$access_token = (Get-AzAccessToken -AsSecureString -ResourceUrl https://api.fabric.microsoft.com) 

$ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($access_token.Token)

try {

    $headers = @{ 
       Authorization = $access_token.Type + ' ' + ([System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr))
    }

    $access_token.UserId -match('^[^@]+') | Out-Null

    # 2. Create the database and wait for it to be created.

    $body = @{
        displayName = $matches[0] + (Get-Date -Format "MMddyyyy")
        type = "SQLDatabase"
        description = "Created using public api"
    }

    $parameters = @{
        Method="Post"
        Headers=$headers
        ContentType="application/json"
        Body=($body | ConvertTo-Json)
        Uri = 'https://api.fabric.microsoft.com/v1/workspaces/' + $workspaceid + '/items'
    }

    Invoke-RestMethod @parameters -ErrorAction Stop

    $databases = (Invoke-RestMethod -Headers $headers -Uri https://api.fabric.microsoft.com/v1/workspaces/$($workspaceid)/SqlDatabases).value
    $databaseid = $databases.Where({$_.displayName -eq $body.displayName}).id

    While($databaseid -eq $null)
    {
        Write-Host 'Waiting on database create.'
        Start-Sleep 30
        $databases = (Invoke-RestMethod -Headers $headers -Uri https://api.fabric.microsoft.com/v1/workspaces/$($workspaceid)/SqlDatabases).value
        $databaseid = $databases.Where({$_.displayName -eq $body.displayName}).id
    }

    # 3. List all SQL databases in a Fabric workspace

    Write-Host 'Listing databases in workspace.'

    Invoke-RestMethod -Headers $headers -Uri https://api.fabric.microsoft.com/v1/workspaces/$($workspaceid)/items?type=SQlDatabase | select -ExpandProperty Value | ft

    $databaseProperties = (Invoke-RestMethod -Headers $headers -Uri https://api.fabric.microsoft.com/v1/workspaces/$($workspaceid)/SqlDatabases/$($databaseid) | select -ExpandProperty Properties)

    #4. Connect to the database and create a table

    Write-Host 'Attempting to connect to the database.'

    sqlcmd.exe -S $databaseProperties.ServerFqdn -d $databaseProperties.DatabaseName -G -Q 'create table test2 
    ( 
    id int 
    );
    insert into test2 values (1);
    insert into test2 values (2);
    insert into test2 values (3);
    select * from test2;' 

    #5. Delete the database

    $parameters = @{
        Method="Delete"
        Headers=$headers
        ContentType="application/json"
        Body=($body | ConvertTo-Json)
        Uri = 'https://api.fabric.microsoft.com/v1/workspaces/' + $workspaceid + '/items/' + $databaseid
    }

    Invoke-RestMethod @parameters

    Write-Output 'Cleaned up:' $body.displayName
 
 } finally {
    # The following lines ensure that sensitive data is not left in memory.
    $headers = [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
    $parameters = [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
}

Database collation

By default, the database is created with the case-insensitive collation SQL_Latin1_General_CP1_CI_AS.

Database collation can't be modified after creation, though collations on individual columns are supported.

When you create a SQL database with the REST API, you can specify the collation in PowerShell. Add a property named creationPayload in the $body hashtable. The collation property contains the collation name, for example:

$collation = 'Latin1_General_100_BIN2_UTF8' # Desired collation name

$body = @{
    displayName = $matches[0] + (Get-Date -Format "MMddyyyy")
    type = "SQLDatabase"
    description = "Created using public api"
    creationPayload = @{
        collation = $collation
        creationMode = "new"
    }
}

Related content

  • Last updated on