Skip to content

development_graph_db_setup.md

Latest commit

 

History

History
142 lines (92 loc) · 6.07 KB

development_graph_db_setup.md

File metadata and controls

142 lines (92 loc) · 6.07 KB

SQL Graph DB Setup for local development

Introduction

This section aims to provide information for developers to establish connections with either the local SQL Graph DB or the Dev SQL Graph DB for their development tasks. To ensure a successful connection and facilitate your development tasks, it is essential to provide the specific server details. By following below instructions, you'll be able to seamlessly add the required connection and proceed with your development work.

In this context, we make the following assumptions:

  • Visual Studio Code will serve as the preferred Integrated Development Environment (IDE) and the code is running in a Dev Container.
  • SQL extensions, namely ms-mssql.mssql and ms-mssql.sql-database-projects-vscode are already installed in the development container.

SQL Graph design documentation can be found here.

How to add SQL connection profiles in VS Code using connection string format?

Specific server details are provided in the later sections for reference.

Step 1: To add a SQL connection, from SQL Server click on Add Connection as shown

Step 1

Step 2: Provide Server name

Step 2

Step 3: Provide Database name

Step 3

Step 4: Select Authentication type

Step 4

Step 5: Provide User name

Step 5

Step 6: Provide Password

Step 6

Step 7: Select Yes for Save Password

Step 7

Step 8: Provide name for Profile Name

Step 8

  • More guidance can be found here on connection profiles.

  • If there is a connection issue, then look for this common issue.

Server details

Connection details for local SQL Graph DB

  • Server name - db,1433
  • [Optional] Database name - graph-data-model
  • User name - sa
  • Password (SQL Login) - Password from devcontainer.json file
  • Save Password? - Yes
  • [Optional] Enter a name for this profile - graph-data-model

You can use a similar approach to connect to Azure SQL Graph DB.

Creating the database, schema, and tables

If the local SQL Graph graph-data-model DB and schema is not yet created then below steps need to be executed after connection is established using VS Code SQL DB project publish feature:

  1. Firstly, go to this and open the SQL DB project using VS Code or Visual Studio.

  2. Right click on DB project and first Build to make sure there are no errors. Then Click on Publish Step 1

  3. Click on Publish to an existing Azure SQL logical server. Step 2

  4. Click on Don't use profile. Step 3

  5. Select the localhost connection profile that was created. Step 4

  6. Select the database to where the publish needs to happen Step 5

  7. Click Publish Step 6

Make sure it runs fine and the necessary tables are installed. Make sure the Database exists, if not create it beforehand.

How to run SQL queries in VSCode?

Here is the necessary tutorial to get started on how to run queries via VSCode.

Graph SQL querying

Sample queries to find data -

SELECT * FROM
[pnide].[sheet] as sheet,
[pnide].[belongs] as belongs,
[pnide].[pnid] as pnid
WHERE MATCH (sheet-(belongs)->pnid)
AND pnid.Id = 'PNID1'

SELECT * FROM
[pnide].[Asset] as asset1,
[pnide].[Asset] as asset2,
[pnide].[Connected] as connected
WHERE MATCH (asset1-(connected)->asset2)
AND asset1.Id = 'Asset1'

Tutorial to figure on how to write queries.

Azure AD authentication setup

The code has been configured to work with either the provided connecting string or through Azure AD authentication.

If you choose to use Azure AD authentication, it is essential to set up the required roles and permissions on the SQL Graph DB resource, as outlined in the following resource: Azure SQL AD Authentication