{"id":3896,"date":"2024-07-14T21:13:33","date_gmt":"2024-07-14T14:13:33","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3896"},"modified":"2024-07-22T09:41:25","modified_gmt":"2024-07-22T02:41:25","slug":"nodejs-sql-server-connect","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-connect\/","title":{"rendered":"Node.js SQL Server: Connect"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to connect to the SQL Server from Node.js<\/code> using the mssql<\/code> package.<\/p>\n\n\n\n

Create a SQL Server Database #<\/a><\/h2>\n\n\n\n

Before continuing, you need to follow this tutorial to create a BookStore database<\/a> and set up a user with full access permissions.<\/p>\n\n\n\n

Creating a Node.js Project #<\/a><\/h2>\n\n\n\n

Step 1. Open your terminal and create a new directory such as sqlserver-node<\/code> to store the project files:<\/p>\n\n\n

mkdir sqlserver-node<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Navigate to the project directory:<\/p>\n\n\n
cd sqlserver-node<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Initialize a Node.js<\/code> project:<\/p>\n\n\n
npm init --yes<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Install the mssql<\/code> package using the following npm<\/code> command:<\/p>\n\n\n
npm install mssql<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Create a .env file that stores the SQL server configuration:<\/p>\n\n\n
DB_USER=YourUser\nDB_PASSWORD=YourPassword\nDB_SERVER=YourServer\nDB_NAME=YourDatabaseName<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
DB_USER=joe\nDB_PASSWORD=YourPassword\nDB_SERVER=localhost\nDB_NAME=BookStore<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Modify the package.json<\/code> file and add the type=\"module\"<\/code> to the top-level key\/value pairs to use the ES6<\/code> module:<\/p>\n\n\n
 \"type\"<\/span>: \"module\"<\/span>,<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Additionally, change the scripts<\/code> section to the following:<\/p>\n\n\n
  \"scripts\"<\/span>: {\n    \"start\"<\/span>: \"node --env-file=.env index.js\"<\/span>\n  },<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
By doing this, you can run the command:<\/p>\n\n\n
npm start<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Node.js<\/code> will load the .env file and run the index.js<\/code> file.<\/p>\n\n\n\n
Step 7. Create the config.js<\/code> file to store the database configuration:<\/p>\n\n\n
export<\/span> const<\/span> config = {\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n  server<\/span>: process.env.DB_SERVER,\n  options<\/span>: {\n    encrypt<\/span>: true<\/span>,\n    trustServerCertificate<\/span>: true<\/span>,\n  },\n};<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In the config.js<\/code> file we create a config object used to connect to the SQL Server. The config object uses the process.env<\/code> object to access the database configuration stored in the .env file.<\/p>\n\n\n\n
Step 8. Create index.js<\/code> file that connects to the SQL Server:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nconst<\/span> connect = async<\/span> () => {\n  try<\/span> {\n    await<\/span> sql.connect(config);\n    console<\/span>.log('Connected to the database!'<\/span>);\n  } catch<\/span> (err) {\n    console<\/span>.error(err);\n  }\n};\n\nconnect();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import sql<\/code> object from mssql<\/code> module and config<\/code> object from the config.js<\/code> module:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define a connect()<\/code> function that connects to the SQL Server:<\/p>\n\n\n
const<\/span> connect = async<\/span> () => {\n  try<\/span> {\n    await<\/span> sql.connect(config);\n    console<\/span>.log('Connected to the database!'<\/span>);\n  } catch<\/span> (err) {\n    console<\/span>.error(err);\n  }\n};<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
To connect to the SQL Server, you call the connect function of the sql<\/code> object and pass the config<\/code> object. Since the sql.connect<\/code>() function returns a promise<\/a>, we can use the await<\/code> keyword.<\/p>\n\n\n\n
Since we use the await<\/code> keyword inside the connect<\/code> function, we must also mark the function as an async<\/a> function.<\/p>\n\n\n\n
If any error occurs during the connection process, we catch the exception<\/code> using the try…catch<\/a> statement and display the error in the catch<\/code> block.<\/p>\n\n\n\n
Third, call the connect()<\/code> function to establish a connection to the SQL Server:<\/p>\n\n\n
connect();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 9. Run the npm start<\/code> command in the terminal to execute the index.js<\/code> file:<\/p>\n\n\n
npm start<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you see the following message, meaning that you have successfully connected to the SQL Server from the Node.js<\/code> program:<\/p>\n\n\n
Connected to the database!<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the mssql<\/code> package to connect to an SQL Server from Node.js<\/code><\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n