If you’re developing web applications, you’re almost certainly going to be constantly interacting with a database. And when it comes time to select the you’ll interact, the choices can be overwhelming. way In this article, we’re going to look in detail at 5 different ways to interact with your database using JavaScript, and we’ll talk about the pros and cons of each. We’ll start with the lowest-level choice — SQL Commands — then move through to higher-level abstractions. Choosing the right database library for your JavaScript application can have a big impact on the maintainability, scalability, and performance of your code, so it’s worth spending some time to figure out your options. Our Sample Application We’re going to use a trivial Express application hosted on as our example. All the code for this article is in this . Feel free to clone it and follow along. Heroku GitHub repository Pre-requisites To run the sample application, you’ll need the following software on your machine: A unix-like terminal environment (Mac OSX and Linux are fine. If you’re using Windows, you’ll need the ). Windows Subsystem for Linux (and a github account). git (version 6 or later). npm The Heroku . command-line tool If you don’t have a Heroku account already, you’ll need to If you don’t want to sign up for Heroku, you can also run the application locally against a local Postgres instance. If you’re comfortable with that, it should be pretty easy to see what changes you need to make instead of deploying to Heroku. sign up for a free account. Once you’ve installed all the above, run in a terminal, and you're ready to get started. heroku login Build and Deploy the Hello World App To start, we’ll set up the following: A trivial application that just serves a “Hello, World” web page. Express A database. Postgres Two tables, representing “users” and “comments” (a user has many comments). Some sample data (in this case, generated via ). mockaroo.com I’ve created a sample application which will set all this up for you (provided you’ve run as mentioned above). To set it up, please execute the following commands from the command line: heroku login git clone http //github. /digitalronin/query-database-javascript.git query-database-javascript setup s: com cd make This will take a few minutes to complete. While you are waiting, you can view the makefile to see the relevant commands, which carry out the following: Create a new Heroku application. Add a Postgres database instance. Deploy the application to Heroku.Run a command on Heroku to set up the database tables and import the CSV sample data. Open the URL of your Heroku application in a new browser window. At the end of this process, you should see “Hello, World” on a web page. Fetching Data With SQL OK — we’re all set up! We’ve created a database with two tables and some sample data. But we’re not doing anything with it yet. The next step is to enable our web application to retrieve data from the database. Whenever you interact with a relational database, you do so by sending SQL commands to the network socket on which the database is listening. This is true for all the libraries we’re going to look at in this article — at the lowest level, they all send SQL commands to the database and retrieve whatever output comes back. So, the first way we’re going to look at interacting with our database is to do just that — send SQL commands. To do this, we’re going to install the pg JavaScript library, which lets us send SQL to a Postgres database and retrieve the results. To install the pg library, execute the following command: npm pg install This will fetch and install the library, and it will add it to your package.json and package-lock.json files. Let’s commit those changes: git package.json package- .json git -m "Install the pg library" add lock commit To talk to our database, we need some details: The hostname of the machine Postgres is running on. The network port Postgres is listening on. The name of the database our data is in. A username and password with permission to access that data. Most database libraries will let us establish a connection either by supplying an object to the library which has keys and values for all of those details, or by combining them all into a single “database URL”, which is what we’re going to do. When you add a database to a Heroku application, you automatically get an environment variable called DATABASE_URL, containing all the details you need to connect to the database. You can see the value of your DATABASE_URL by running: heroku config This will output all the environment variables your application can use. There should only be one for now, so you should see something like this in the output: DATABASE_URL: : a266c0f5b .compute- .amazonaws. : /dfb3aad8c026in postgres //clqcouauvejtvw:1b079cad50f3ff9b48948f15a7fa52123bc6795b875348d668864 07 @ec2-52-73-247-67 1 com 5432 In the case of our example, that breaks down like this: {
  "hostname": "ec2-52-73-247-67.compute-1.amazonaws.com",
  "port": 5432,
  "database": "dfb3aad8c026in",
  "username": "clqcouauvejtvw",
  "password": "1b079cad50f3ff9b48948f15a7fa52123bc6795b875348d66886407a266c0f5b"
} Your DATABASE_URL value will be different, but the structure will be the same. Now that we have the pg library installed, and we know how to connect to our database, let’s execute our first example of interacting with a database. We’ll simply fetch the list of users and display them on our web page. At the top of our index.js file, we’ll require our pg library, and create a database connection object. { Pool } = ( ); conn = Pool({ : process.env.DATABASE_URL }); const require 'pg' const new connectionString In the block, we'll alter the get line to call a method that displays a list of users from the database: express() .get( , ) '/' ( , ) => req res list Users( , ) req res Finally, we’ll implement the listUsers function: { { db = conn.connect() result = db.query( ); results = { : (result) ? result.rows : };

    res.render( , results );

    db.release();

  } (err) { .error(err);

    res.send( + err);

  }

} async ( ) function listUsers req, res try const await const await 'SELECT * FROM users' const users null 'pages/index' catch console "Error " This code waits until a connection is established to our database, then sends an SQL query using the query function and retrieves the result. Now, this step could fail for lots of different reasons, so in the code we test to ensure we’ve got some data and, if we do, we assign result.rows to the key users of our results object. Next, we pass results to the render function, then release our database connection. In views/pages/index.ejs we have access to the results object, so we can display our user data like this: HTML Users { %> - < > h1 </ > h1 < > ul < (( ) => % users.map user < > li < %> %= user.id < %> %= user.first_name < %> %= user.last_name </ > li < }); %> % </ > ul You can see the code with these changes and are the names of two columns from the users table of our database. here. first_name last_name Let’s deploy these changes so we can see the data in our Heroku application: git .js views/pages/ .ejs
git -m "Display a list of users"
git push heroku master add index index commit This will take a minute or two to deploy. When that command has finished executing, reload your browser and you should see a list of users on the web page. MySQL Example The above example is for Postgres, but the code for other common relational databases will be similar. For example, if you are using : MySQL Instead of use (use mysql2, not mysql - mysql2 is faster and supports async/await) npm install pg npm install mysql2 In index.js you would require mysql like this: = require( ); const mysql 'mysql2/promise' The listUsers function would look like this: { { conn = mysql.createConnection(process.env.DATABASE_URL); [rows, fields] = conn.execute( ); results = { : rows };

   res.render( , results ); conn.end();

 } (err) { .error(err);

   res.send( + err);

 }

} async ( ) function listUsers req, res try const await const await 'SELECT * FROM users' const 'users' 'pages/index' await catch console "Error " views/pages/index.ejs remains the same. You can see the sample project with these changes here. Conclusion We’ve covered a lot of ground here, but this is all fundamental to understanding how all database access works. In the next part, we’ll see how query builder and object-relational modeling libraries build on top of this foundation to allow you to work with database data in your code in a way that feels more like working with JavaScript functions and objects. Previously published at https://dzone.com/articles/the-complete-tutorial-on-the-top-5-ways-to-query-y?fromrel=true

Too Long; Didn't Read

Making security fun one episode at a time

[Tutorial] Top 5 Ways to Query your Relational Database in JavaScript [Part 1]

Too Long; Didn't Read

Michael

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

[Tutorial] Top 5 Ways to Query your Relational Database in JavaScript [Part 1]

Too Long; Didn't Read

Michael

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES