In this quickstart, you use Node.js to connect to an Azure SQL database and use T-SQL statements to query data.
An Azure account with an active subscription. Create an account for free.
Node.js-related software
* [macOS](#tabpanel_CeZOj-G++Q_macos)
Install Homebrew and Node.js, then install the ODBC driver and SQLCMD using steps 1.2 and 1.3 in Create Node.js apps using SQL Server on macOS.
Install Node.js, then install the ODBC driver and SQLCMD using steps 1.2 and 1.3 in Create Node.js apps using SQL Server on Ubuntu.
Install Chocolatey and Node.js, then install the ODBC driver and SQLCMD using steps 1.2 and 1.3 in Create Node.js apps using SQL Server on Windows.
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.
Go 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.
Open a command prompt and create a folder named sqltest. Open the folder you created and run the following command:
npm init -y
npm install tedious
In your favorite text editor, create a new file, sqltest.js.
Replace its contents with the following code. Then add the appropriate values for your server, database, user, and password.
const { Connection, Request } = require("tedious");
// Create connection to database
const config = {
authentication: {
options: {
userName: "username", // update me
password: "password" // update me
},
type: "default"
},
server: "your_server.database.windows.net", // update me
options: {
database: "your_database", //update me
encrypt: true
}
};
const connection = new Connection(config);
// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
if (err) {
console.error(err.message);
} else {
queryDatabase();
}
});
function queryDatabase() {
console.log("Reading rows from the Table...");
// Read all rows from table
const request = new Request(
`SELECT TOP 20 pc.Name as CategoryName,
p.name as ProductName
FROM [SalesLT].[ProductCategory] pc
JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid`,
(err, rowCount) => {
if (err) {
console.error(err.message);
} else {
console.log(`${rowCount} row(s) returned`);
}
}
);
request.on("row", columns => {
columns.forEach(column => {
console.log("%s\t%s", column.metadata.colName, column.value);
});
});
connection.execSql(request);
}
The code example uses the AdventureWorksLT sample database for Azure SQL.
At the command prompt, run the program.
node sqltest.js
Verify the top 20 rows are returned and close the application window.
#nodejs #azuresql #sql