Read Record From Access Database With Javascript
five Means to Query your Relational DB using JavaScript
If y'all're developing web applications, y'all're almost certainly going to be constantly interacting with a database. And when it comes time to select the way y'all'll interact, the choices tin exist overwhelming.
In this article, we're going to await in detail at v unlike 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 college-level abstractions.
Choosing the right dat a base library for your JavaScript application can accept a big impact on the maintainability, scalability, and performance of your code, and so it's worth spending some time to effigy out your options.
Our Sample Application
Nosotros're going to use a footling Express application hosted on Heroku equally our example. All the code for this article is in this GitHub repository. Feel free to clone it and follow along.
Pre-requisites
To run the sample application, y'all'll demand the following software on your machine:
- A unix-similar terminal environs (Mac OSX and Linux are fine. If yous're using Windows, you'll need the Windows Subsystem for Linux).
- git (and a github account).
- npm (version 6 or subsequently).
- The Heroku control-line tool.
If you lot don't have a Heroku business relationship already, you'll need to sign up for a free account. If you don't want to sign up for Heroku, y'all can also run the application locally against a local Postgres instance. If y'all're comfy with that, it should exist pretty easy to encounter what changes you need to make instead of deploying to Heroku.
Once you've installed all the higher up, run heroku login in a terminal, and yous're ready to become started.
Build and Deploy the Hello World App
To start, we'll set the following:
- A trivial Express application that just serves a "How-do-you-do, World" web page.
- A Postgres database.
- Two tables, representing "users" and "comments" (a user has many comments).
- Some sample information (in this instance, generated via mockaroo.com).
I've created a sample application which will fix all this up for y'all (provided you've run heroku login as mentioned above). To set it up, please execute the following commands from the command line:
git clone https://github.com/digitalronin/query-database-javascript.git
cd query-database-javascript make setup
This volition take a few minutes to complete. While yous are waiting, you tin view the makefile to see the relevant commands, which carry out the post-obit:
- Create a new Heroku application.
- Add a Postgres database instance.
- Deploy the application to Heroku.
- Run a command on Heroku to gear up the database tables and import the CSV sample information.
- Open the URL of your Heroku application in a new browser window.
At the end of this process, you should run into "Howdy, Globe" on a spider web page.
Fetching Data With SQL
OK — nosotros're all set up! Nosotros've created a database with two tables and some sample information. Only we're not doing anything with it still. The next step is to enable our web application to call up data from the database.
Whenever you interact with a relational database, you do and then by sending SQL commands to the network socket on which the database is listening. This is truthful for all the libraries we're going to wait at in this article — at the lowest level, they all send SQL commands to the database and recall any output comes dorsum.
Then, 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 u.s. send SQL to a Postgres database and retrieve the results.
To install the pg library, execute the following command:
npm install pg
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 add package.json packet-lock.json git
commit -m "Install the pg library"
To talk to our database, we demand some details:
- The hostname of the machine Postgres is running on.
- The network port Postgres is listening on.
- The name of the database our information is in.
- A username and password with permission to admission that data.
Nearly 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 yous add together a database to a Heroku application, you automatically become an environment variable chosen DATABASE_URL, containing all the details you need to connect to the database. You can run across 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 at present, so you should run into something like this in the output:
DATABASE_URL: postgres://clqcouauvejtvw:1b079cad50f3ff9b48948f15a7fa52123bc6795b875348d668864
07a266c0f5b@ec2-52-73-247-67.compute-1.amazonaws.com:5432/dfb3aad8c026in
In the case of our example, that breaks downward similar this:
SQL
{ "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 volition be the same.
Now that nosotros 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 merely fetch the listing of users and display them on our spider web page. At the acme of our alphabetize.js file, we'll require our pg library, and create a database connectedness object.
JavaScript
const { Pool } = require('pg'); const conn = new Pool({ connectionString: process.env.DATABASE_URL });
In the express() block, we'll change the become line to call a method that displays a listing of users from the database:
.get('/', (req, res) => listUsers(req, res))
Finally, nosotros'll implement the listUsers office:
JavaScript
async function listUsers(req, res) { endeavour { const db = expect conn.connect() const result = await db.query('SELECT * FROM users'); const results = { users: (result) ? consequence.rows : zippo}; res.render('pages/alphabetize', results ); db.release(); } grab (err) { console.error(err); res.ship("Error " + err); } }
This code waits until a connexion is established to our database, then sends an SQL query using the query function and retrieves the outcome.
Now, this step could fail for lots of dissimilar reasons, and so in the lawmaking nosotros exam to ensure we've got some data and, if nosotros do, nosotros assign outcome.rows to the key users of our results object. Adjacent, we laissez passer results to the render function, then release our database connectedness.
In views/pages/index.ejs nosotros have access to the results object, so we can display our user information like this:
HTML
<h1>Users</h1> <ul> <% users.map((user) => { %> <li><%= user.id %> - <%= user.first_name %> <%= user.last_name %></li> <% }); %> </ul>
You can meet the code with these changes here. first_name and last_name are the names of ii columns from the users tabular array of our database.
Allow'southward deploy these changes and then nosotros tin can see the data in our Heroku application:
git add index.js views/pages/index.ejs
git commit -yard "Display a list of users"
git button heroku master
This will take a infinitesimal 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, simply the code for other mutual relational databases will be similar. For case, if you are using MySQL:
- Instead of
npm install pgusenpm install mysql2(use mysql2, not mysql - mysql2 is faster and supports async/await) - In alphabetize.js you would require mysql like this:
const mysql = require('mysql2/promise');
- The listUsers function would look similar this:
JavaScript
async function listUsers(req, res) { endeavour { const conn = look mysql.createConnection(process.env.DATABASE_URL); const [rows, fields] = expect conn.execute('SELECT * FROM users'); const results = { 'users': rows }; res.render('pages/alphabetize', results ); await conn.end(); } catch (err) { console.mistake(err); res.ship("Error " + err); } }
views/pages/index.ejs remains the same.
You can meet the sample project with these changes here.
Now permit'due south examine a few libraries which build on top of this foundation, adding layers of abstraction that let you lot read and manipulate database data in a more "JavaScript-like" way.
So far, we've seen how to send raw SQL to the database; statements like:
SELECT * FROM users
If we wanted to get comments by a specific user, say the user whose id is 1, we might utilize something like this:
SELECT * FROM comments WHERE user_id = 1
In that location's nothing wrong with interacting with your database in this way, but it tin feel a bit cumbersome, and it requires you to keep "shifting gears" mentally. Y'all think about your JavaScript code in one way, simply when you need to think about the data in your database, you lot accept to get-go thinking in SQL.
The purpose of the rest of the database libraries nosotros're going to consider is to allow you treat the data in your database more like the JavaScript objects and code in your application. "Nether the hood" information technology'due south all SQL, but you lot won't need to care much about that unless you want to.
Knex — Abstracting Away SQL
The first library we'll talk nearly is Knex. The documentation page describes Knex as a "query architect", and its purpose is to provide a layer of abstraction on top of raw SQL.
Installing Knex
Knex requires pg (or MySQL if you're using a MySQL database). We already take pg installed, so we just add knex like this:
npm install knex
git add together package.json package-lock.json
git commit -m "Install the knex library"
Using Knex
The NPM page for knex describes information technology as a "query builder." Knex abstracts SQL to a certain extent, but not very far. We still demand to understand the underlying SQL, but we can write it in a more JavaScript-like syntax, rather than having to piece and dice SQL strings. More than importantly, nosotros can use limerick to chain knex terms in a manner that is much more comfortable for JavaScript programmers.
And so, when nosotros used pg, we had this statement:
const effect = await db.query('SELECT * FROM users');
When we employ knex, we can write this:
const result = await db.select().from('users');
That might not look like much of a difference, but considering of the way we can compose knex function calls, we can besides practice things similar this:
const result = await db.select().from('users').limit(5).first(eight);
Here, nosotros're getting five user records, starting at position viii in the total set up of all possible user records that match our query. You can see the full set of options available in the knex documentation.
Let's modify our Limited app to use knex to display some records from our database. Get-go, in index.js replace these 2 lines:
JavaScript
const { Pool } = require('pg');
const conn = new Pool({ connectionString: process.env.DATABASE_URL }); …with this:
JavaScript
const db = crave('knex')({
customer: 'pg',
connection: process.env.DATABASE_URL
}); Then, change the implementation of listUsers to this:
JavaScript
async function listUsers(req, res) {
try {
const result = wait db.select().from('users').limit(5).offset(5);
const results = { 'users': (upshot) ? effect : nil}; res.render('pages/alphabetize', results );
} take hold of (err) {
console.error(err); res.send("Error " + err);
}
}
Our views/pages/alphabetize.ejs file can stay exactly the same as before.
Commit, push button, and deploy:
git add alphabetize.js
git commit -m "Use knex to display user information"
git button heroku primary
When you refresh your browser, you should see user records 6 to 10 on the folio.
You can meet the code with these changes here.
Object Relational Mapping (ORM)
Knex gives us a fashion of interacting with our database, which is much more like JavaScript, but nosotros are still required to think in a database-centric mode when we need to manipulate data.
The side by side three libraries we're going to talk nearly are all built on top of knex (which is congenital on top of pg or MySQL), and are examples of "object relational mapping" or ORM libraries. Equally the name implies, the purpose of an ORM library is to translate between data in a relational database and JavaScript objects in your application. What that means is, instead of thinking about records in the users table when y'all write your JavaScript code, you lot can think well-nigh user objects.
Objection
The beginning library nosotros'll look at is objection, which is built on top of knex:
npm install objection
git add together parcel.json package-lock.json
git commit -m "Install the objection library"
To highlight some of the utility of ORM libraries, nosotros're going to modify our application to display users and their comments. Objection is congenital on top of knex, then in our alphabetize.js file, we have to go out the knex block in place, and add a bit more lawmaking (I'm putting everything in the index.js file, to go on things simple. In a real application you would interruption the code into separate files):
const { Model } = crave('objection');
Model.knex(db);
This gives us a Model class from which we can inherit to define two classes User and Comment. We'll define Annotate first:
JavaScript
grade Annotate extends Model { static get tableName() {
return 'comments';
}
}
Our class needs to extend Model, and must implement a tableName function to tell Objection which database table contains the underlying records.
The User class is similar, but nosotros're going to add some behavior to our course; a fullName function, which we can use in our view template. Nosotros're also going to tell Objection that Users have Comments (i.e. a user owns zero or more comments). In ORM-speak, this is commonly described as a "has many human relationship" - i.e. a user has many comments. Here'south what the code for this looks like:
JavaScript
class User extends Model { static go tableName() {
return 'users';
} fullName() {
return `${this.first_name} ${this.last_name}`;
} static go relationMappings() { return {
comments: {
relation: Model.HasManyRelation,
modelClass: Comment,
join: {
from: 'users.id',
to: 'comments.user_id'
}
}
};
}
}
Nosotros define a relationMappings object inside our User class, with a single primal of comments and a value telling Objection that this is a HasManyRelation on the Comment class, where the value of the id column of the users tabular array matches the value of the user_id column of the comments tabular array.
Now that we've defined our classes, allow'southward use them in our code. Hither'due south the new implementation of listUsers:
JavaScript
async office listUsers(req, res) { attempt {
const users = await User.query().limit(5); for (i in users) {
const user = users[i];
user.comments = await User.relatedQuery('comments').for(user.id);
} const results = { 'users': users }; res.return('pages/index', results );
} catch (err) {
console.fault(err); res.send("Mistake " + err);
}
}
Here, we fetch five users, then for each of those users, nosotros fetch their comments and assign those to the comments property of our user object. In views/pages/index.ejs nosotros can display our users and their comments like this:
HTML
<h1>Users</h1>
<ul>
<% users.map((user) => { %>
<li><%= user.id %> - <%= user.fullName() %></li>
<ul>
<% user.comments.map((comment) => { %>
<li><%= comment.body %></li>
<% }); %>
</ul>
<% }); %>
</ul> Yous can run into the code with these changes here. As usual, commit, and push to deploy:
git add index.js views/pages/index.ejs
git commit -m "Show users and comments using Objection"
git push heroku master
At present, when you reload the page, you should see users and comments.
The "Northward+1 Selects" Problem
This lawmaking highlights a common problem that people run into when using ORM libraries, known equally the "N+1 selects" problem.
This is the block of code we used to fetch users and their comments:
JavaScript
const users = expect User.query().limit(5); for (i in users) {
const user = users[i];
user.comments = await User.relatedQuery('comments').for(user.id);
}
This works, but is very inefficient. Beginning, we fetch 5 users, and so for each of those v users, nosotros fetch their comments past making another call to the database. So, we've made i call for the users, then another 5 calls to go the comments. That's v calls plus the first 1, i.due east. 5+1 or N+i where Northward == 5. Hence the "N+1 selects" problem.
Unless your database queries are very complex, the time it takes to brand a round-trip phone call to the database is a lot longer than the time information technology takes the database to calculate and transmit the results of your queries. So, to keep our applications fast, we need to minimize the number of calls to the database, as much as we tin. The code in a higher place is the exact contrary of this.
For this piffling instance, you won't notice any difference, but for real-globe applications, the performance striking tin be very serious, and cause lots of issues.
Fortunately, every ORM library has features that arrive easy to avoid this problem (provided you know information technology'southward there). Here's how Objection does it; in index.js, replace the code block above with this:
const users = await User.query().limit(v).withGraphFetched('comments');
This one line does the same as the code block above, only in a much more database-efficient way. Objection will utilise the human relationship information nosotros provided to figure out how to fetch the user data and the comments data in a single query, and unpack and stitch together the results into the same object structure nosotros built before using our for loop.
Yous can see the code with these changes here.
Bookshelf
The next ORM library we'll look at is Bookshelf.
A lot of the differences betwixt ORM libraries depend on what use-case the library is optimized for. In the case of Bookshelf, information technology'south clearly designed to arrive as easy as possible to render paginated lists of data, which is a very common employ example in spider web applications.
Allow's replace Objection with Bookshelf in our awarding:
npm uninstall objection
npm install bookshelf
git add packet.jsonpackage-lock.json
git commit -g "Replace Objection with Bookshelf"
In alphabetize.js, supervene upon these lines:
JavaScript
const { Model } = require('objection');
Model.knex(db); …with this:
JavaScript
const bookshelf = require('bookshelf')(db); Replace our class definitions with these:
JavaScript
const Comment = bookshelf.model('Comment', {
tableName: 'comments'
}); const User = bookshelf.model('User', {
tableName: 'users', comments() {
// by default, bookshelf infers that the strange key is 'user_id'
return this.hasMany('Comment');
} });
Our listUsers part at present looks similar this:
JavaScript
async function listUsers(req, res) {
try {
const models = await new User()
.fetchPage({
pageSize: 5,
folio: 1,
withRelated: ['comments']
}); users = []; models.map(g => {
const user = grand.attributes;
const comments = m.related('comments'); user.comments = comments.map(c => c.attributes);
users.push(user);
}); const results = { 'users': users }; res.render('pages/index', results );
} catch (err) {
console.error(err);
res.send("Error " + err);
}
}
As you tin come across, the definition of the classes is a chip more than concise, but Bookshelf needs a more verbose definition of how to unpack our information to build the users/comments structure. Find also how the concept of pages of information is built direct into the library'south API.
The lawmaking in views/pages/index.ejs is nigh identical (I've removed the fullName office from the User class):
HTML
<h1>Users</h1>
<ul>
<% users.map((user) => { %>
<li><%= user.id %> - <%= user.first_name %> <%= user.last_name %></li>
<ul>
<% user.comments.map((comment) => { %>
<li><%= comment.body %></li>
<% }); %>
</ul>
<% }); %>
</ul> You tin can encounter the code with these changes here. And of course, once once again commit and deploy.
git add alphabetize.js views/pages/alphabetize.ejs
git commit -yard "Show users and comments using Bookshelf"
git push heroku principal
Sequelize
The concluding library we're going to await at is Sequelize.
Sequelize is quite opinionated in the way it expects your information to exist structured. If you lot follow its conventions, you tin can write less code and allow Sequelize to do a lot of the work for you. In detail, Sequelize has a lot of features to help create tables for you, and by default, information technology will create them following its ain construction and naming conventions.
The database we've been using isn't structured in exactly the way Sequelize expects, and so nosotros need to add together a flake of extra configuration to allow Sequelize to piece of work with it.
Installing Sequelize
To remove bookshelf and install sequelize, run these commands:
npm uninstall bookshelf
npm install sequelize
git add package.json bundle-lock.json
git commit -grand "Replace Bookshelf with Sequelize"
Using Sequelize
In alphabetize.js, supervene upon these lines:
JavaScript
const db = require('knex')({
customer: 'pg',
connection: process.env.DATABASE_URL
}); const bookshelf = require('bookshelf')(db)
…with these:
JavaScript
const { Sequelize, DataTypes } = crave('sequelize');
const sequelize = new Sequelize(process.env.DATABASE_URL); And so, supersede the form definitions for User and Annotate with this code:
JavaScript
const User = sequelize.ascertain('User', {
first_name: { blazon: DataTypes.String },
last_name: { blazon: DataTypes.Cord },
email: { type: DataTypes.STRING }
},
{
tableName: 'users',
timestamps: fake
}
); const Annotate = sequelize.ascertain('Comment', {
body: { type: DataTypes.STRING }
}, {
tableName: 'comments',
timestamps: false
}
); User.hasMany(Comment, { foreignKey: 'user_id' });
Notation that we passed ii objects to sequelize.define. The showtime object divers our object's properties, and the second independent some metadata.
In this case, we told Sequelize that the database table that underpins the User class is called 'users' (past default, Sequelize would infer that the table was called 'Users'), and the timestamps: false tells Sequelize that our table does non accept timestamp columns, called createdAt and updatedAt.
Sequelize makes it very easy to write lawmaking that volition create tables for you, and when it does and then, it adds these timestamp columns and sets their values accordingly when you write to the database. The sequelize documentation is excellent, and has more about this.
The foreignKey: 'user_id' that we laissez passer to hasMany is another of the places where we take to tell Sequelize we're not following its conventions. Information technology expects (and would create for us) a cavalcade called UserId to link comments to users.
Inside our listUsers part, we can replace all of this lawmaking:
JavaScript
const models = await new User() .fetchPage({
pageSize: 5,
page: 1,
withRelated: ['comments']
}); users = []; models.map(chiliad => {
const user = m.attributes;
const comments = g.related('comments'); user.comments = comments.map(c => c.attributes);
users.push(user);
});
…with this single line:
JavaScript
const users = await User.findAll({ include: Comment }); We also have to make one tiny modify in views/pages/index.ejs. Replace this line:
<% user.comments.map((annotate) => { %>
…with this (the difference is user.Comments instead of user.comments):
<% user.Comments.map((annotate) => { %>
You can see the code with these changes here.
git add together alphabetize.js views/pages/index.ejs
git commit -m "Show users and comments using Sequelize"
git push heroku master
So Which Option Is Best?
And then there y'all accept it — v ways you can query a relational database from your JavaScript application. We started with raw SQL via the pg/mysql library, then looked at the knex query builder, before moving on to iii ORM libraries; objection, bookshelf, and sequelize.
So, which is the correct choice for your application?
As e'er, it depends. In that location's zip you can do with an ORM library that you tin't practice using a query builder or even raw SQL. Since everything works using SQL "nether the hood". That's not surprising. Besides, even if y'all determine to employ an ORM, almost libraries notwithstanding give you a manner to ship raw SQL to your database. And then what level of abstraction you use depends on the trouble yous're trying to solve, and what kind of lawmaking you desire to focus your attention on.
If you're making heavy employ of the features of your database, peradventure with circuitous views or stored procedures, y'all might find it easier to use knex or raw SQL. Just, for nearly web applications information technology's quite likely that an ORM library volition make your life easier past abstracting abroad the table structure and allowing you to think about your application data equally JavaScript objects.
If y'all've decided on ORM, the choice of which ORM library to utilize isn't e'er clear-cut. The landscape of JavaScript libraries is very dynamic. New libraries are created quite often, and older ones autumn out of favor. Hither are a few things to retrieve nigh when making your choice:
- Scan through the documentation of the library, and see if it's clear and comprehensive. Then, decide if the manner the API is put together makes sense to y'all. Different libraries utilize dissimilar approaches, and you might find one of them a meliorate fit than others for your requirements and preferences. This is particularly true if yous are writing code to work with an existing database, or creating your database as you develop your application.
- Accept a look at the community around the library. Is it something a lot of people are actively using? If so, there will probably exist enough of help and advice available if you need information technology. Some libraries also have extensive plugin ecosystems around them, and it may be that detail plugins make your life much easier.
- A related issue is the age of the library. If it's been effectually for a while, information technology's more likely that mutual problems have been found and fixed. If it's a relatively new library, you might have to figure more things out for yourself (which might be a skilful thing if you're someone who loves to play with new, shiny toys and solve puzzles).
- Performance is more likely to depend on how you utilise the library than on the library itself. Just, if y'all admittedly, positively must squeeze the last few microseconds of latency out of your application, and then working closer to the database using SQL or knex will be a footling faster. Exist warned that this is commonly quite a marginal benefit, and the toll in the maintainability of your code is very likely to exist higher than the proceeds in criterion performance.
Happy querying!
Source: https://towardsdatascience.com/5-ways-to-query-your-relational-db-using-javascript-d5499711fc7d
0 Response to "Read Record From Access Database With Javascript"
Post a Comment