Quickstart: Use Ruby to query a database in Azure SQL Database or Azure SQL Managed Instance

Applies to: Azure SQL Database Azure SQL Managed Instance

This quickstart demonstrates how to use Ruby to connect to a database and query data with Transact-SQL statements.

Prerequisites

To complete this quickstart, you need the following prerequisites:

• A database. You can use one of these quickstarts to create and then configure the database:

  Action	SQL Database	SQL Managed Instance	SQL Server on Azure Virtual Machines
  Create	Portal	Portal	Portal
  	CLI	CLI
  	PowerShell	PowerShell	PowerShell
  Configure	Server-level IP firewall rule	Connectivity from a VM
  		Connectivity from on-premises	Connect to a SQL Server instance
  Load data	Wide World Importers loaded per quickstart	Restore Wide World Importers	Restore Wide World Importers
  		Restore or import Adventure Works from a BACPAC file from GitHub	Restore or import Adventure Works from a BACPAC file from GitHub

  Important

  The scripts in this article are written to use the Adventure Works database. With a SQL Managed Instance, either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.

• For installing Ruby and related software for your operating system, see Configure development environment for Ruby development.

Get server connection information

Get the information you need to connect to a database in Azure SQL Database. You'll need the fully qualified server name or host name, database name, and sign-in information for the upcoming procedures.

1. Sign in to the Azure portal.

2. Navigate to the SQL databases or SQL Managed Instances page.

3. On the Overview page, review the fully qualified server name next to Server name for a database in Azure SQL Database or the fully qualified server name (or IP address) next to Host for an Azure SQL Managed Instance or SQL Server on Virtual Machines. To copy the server name or host name, hover over it and select the Copy icon.

Create code to query your database in Azure SQL Database

1. In a text or code editor, create a new file named sqltest.rb.

2. Add the following code. Substitute the values from your database in Azure SQL Database for <server>, <database>, <username>, and <password>.

   require 'tiny_tds'
   server = '<server>.database.chinacloudapi.cn'
   database = '<database>'
   username = '<username>'
   password = '<password>'
   client = TinyTds::Client.new username: username, password: password,
       host: server, port: 1433, database: database, azure: true
   
   puts "Reading data from table"
   tsql = "SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
           FROM [SalesLT].[ProductCategory] pc
           JOIN [SalesLT].[Product] p
           ON pc.productcategoryid = p.productcategoryid"
   result = client.execute(tsql)
   result.each do |row|
       puts row
   end
   

This article requires the AdventureWorks2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

Run the code

1. At a command prompt, run the following command:

   ruby sqltest.rb
   

2. Verify that the top 20 Category/Product rows from your database are returned.

Next steps

• Design your first database in Azure SQL Database
• GitHub repository for TinyTDS
• Report issues or ask questions about TinyTDS
• Ruby driver for SQL Server