Quickstart: Use Ruby to query an Azure SQL database
This quickstart demonstrates how to use Ruby to connect to an Azure SQL database and query data with Transact-SQL statements.
To complete this quickstart, you need the following prerequisites:
An Azure SQL database. You can use one of these quickstarts to create and then configure a database in Azure SQL Database:
Single database Managed instance Create Portal Portal CLI CLI PowerShell PowerShell Configure Server-level IP firewall rule Connectivity from a VM Connectivity from on-site Load data Adventure Works loaded per quickstart Restore Wide World Importers Restore or import Adventure Works from BACPAC file from GitHub
The scripts in this article are written to use the Adventure Works database. With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.
Ruby and related software for your operating system:
MacOS: Install Homebrew, rbenv and ruby-build, Ruby, FreeTDS, and TinyTDS. See Steps 1.2, 1.3, 1.4, 1.5, and 2.1 in Create Ruby apps using SQL Server on macOS.
Ubuntu: Install prerequisites for Ruby, rbenv and ruby-build, Ruby, FreeTDS, and TinyTDS. See Steps 1.2, 1.3, 1.4, 1.5, and 2.1 in Create Ruby apps using SQL Server on Ubuntu.
Windows: Install Ruby, Ruby Devkit, and TinyTDS. See Configure development environment for Ruby development.
Get SQL server connection information
Get the connection information you need to connect to the Azure SQL database. You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.
Sign in to the Azure portal.
Navigate to the SQL databases or SQL managed instances page.
On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. To copy the server name or host name, hover over it and select the Copy icon.
Create code to query your SQL database
In a text or code editor, create a new file named sqltest.rb.
Add the following code. Substitute the values from your Azure SQL database for
The code in this example uses the sample AdventureWorksLT data, which you can choose as source when creating your database. If your database has different data, use tables from your own database in the SELECT query.
require 'tiny_tds' server = '<server>.database.windows.net' 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
Run the code
At a command prompt, run the following command:
Verify that the top 20 Category/Product rows from your database are returned.