Get from database javascript

Querying Data in SQLite Database from Node.js Applications

Summary: in this tutorial, you will learn how to query data from the SQLite database from a Node.js application using sqlite3 API.

To query data in SQLite database from a Node.js application, you use these steps:

1. Open a database connection.
2. Execute a SELECT statement and process the result set.
3. Close the database connection.

The sqlite3 module provides you with some methods for querying data such as all() , each() and get() .

Querying all rows with all() method

The all() method allows you to execute an SQL query with specified parameters and call a callback to access the rows in the result set.

The following is the signature of the all() method:

db.all(sql,params,(err, rows ) => < // process rows here >); Code language: JavaScript (javascript)

The err argument stores the error detail in case there was an error occurred during the execution of the query. Otherwise, the err will be null .

If the query is executed successfully, the rows argument contains the result set.

Because the all() method retrieves all rows and places them in the memory, therefore, for the large result set, you should use the each() method.

The following example illustrates how to query data from the playlists table in the sample database using the all() method:

const sqlite3 = require('sqlite3').verbose(); // open the database let db = new sqlite3.Database('./db/chinook.db'); let sql = `SELECT DISTINCT Name name FROM playlists ORDER BY name`; db.all(sql, [], (err, rows) => < if (err) < throw err; > rows.forEach((row) => < console.log(row.name); >); >); // close the database connection db.close(); Code language: JavaScript (javascript)

>node all.js 90's Music Audiobooks Brazilian Music Classical Classical 101 - Deep Cuts Classical 101 - Next Steps Classical 101 - The Basics Grunge Heavy Metal Classic Movies Music Music Videos On-The-Go 1 TV Shows Code language: JavaScript (javascript)

The output shows all playlists as expected.

Query the first row in the result set

When you know that the result set contains zero or one row e.g., querying a row based on the primary key or querying with only one aggregate function such as count, sum, max, min, etc., you can use the get() method of Database object.

db.get(sql, params, (err, row) => < // process the row here >); Code language: JavaScript (javascript)

The get() method executes an SQL query and calls the callback function on the first result row. In case the result set is empty, the row argument is undefined .

The following get.js program demonstrates how to query a playlist by its id:

const sqlite3 = require('sqlite3').verbose(); // open the database let db = new sqlite3.Database('./db/chinook.db'); let sql = `SELECT PlaylistId id, Name name FROM playlists WHERE PlaylistId = ?`; let playlistId = 1; // first row only db.get(sql, [playlistId], (err, row) => < if (err) < return console.error(err.message); > return row ? console.log(row.id, row.name) : console.log(`No playlist found with the id $ `); >); // close the database connection db.close(); Code language: JavaScript (javascript)

Let’s run the get.js program.

>node get.js 1 'Music' Code language: JavaScript (javascript)

The output shows the Music playlist which is correct.

If you change the playlistId to 0 and execute the get.js program again:

>node get.js No playlist found with the id 0 Code language: JavaScript (javascript)

It showed that no playlist was found with id 0 as expected.

Query rows with each() method

The each() method executes an SQL query with specified parameters and calls a callback for every row in the result set.

The following illustrates the each() method:

db.each(sql,params, (err, result) => < // process each row here >); Code language: JavaScript (javascript)

If the result set is empty, the callback is never called. In case there is an error, the err parameter contains detailed information.

The following each.js program illustrates how to use the each() method to query customers’ data from the customers table.

const sqlite3 = require('sqlite3').verbose(); // open the database let db = new sqlite3.Database('../db/chinook.db'); let sql = `SELECT FirstName firstName, LastName lastName, Email email FROM customers WHERE Country = ? ORDER BY FirstName`; db.each(sql, ['USA'], (err, row) => < if (err) < throw err; > console.log(`$ $ - $ `); >); // close the database connection db.close(); Code language: JavaScript (javascript)

Let’s run the each.js program:

>node each.js Dan Miller - dmiller@comcast.com Frank Harris - fharris@google.com Frank Ralston - fralston@gmail.com Heather Leacock - hleacock@gmail.com Jack Smith - jacksmith@microsoft.com John Gordon - johngordon22@yahoo.com Julia Barnett - jubarnett@gmail.com Kathy Chase - kachase@hotmail.com Michelle Brooks - michelleb@aol.com Patrick Gray - patrick.gray@aol.com Richard Cunningham - ricunningham@hotmail.com Tim Goyer - tgoyer@apple.com Victor Stevens - vstevens@yahoo.com Code language: JavaScript (javascript)

As you see, the callback function was called for each row to print out the customer’s information.

In this tutorial, you have learned how to use various methods of the Database object to query data from the SQLite database.

Источник

How to Fetch / Show Data from MySQL Database in Node Js

Node Get and Show data from MySQL tutorial; When it comes to managing data, MySQL is the first choice of the developers. Obviously, certain requirements are always considered when choosing a tech stack. However, this MySQL always has the upper hand for storing a large number of records.

Similarly, Node js is the popular open-source server environment that allows you to create a robust backend not only but also allow you to build a powerful frontend application.

In this tutorial, we will show you the confluence of Node js and MySQL. We will create a simple node app, this app will Get records or data from the MySQL database and display the MySQL data in an HTML template in a Node js app.

To make the request to the MySQL database, we will take the help of Express js, and the express allows us to write code for routes that will request and communicate with the MySQL database through the Node platform.

Node js MySQL Render and Display Records from Database in HTML Example

• Step 1: Build Node Project
• Step 2: Install NPM Dependencies
• Step 3: Create SQL Table
• Step 4: Make MySQL Database Connection
• Step 5: Display Records in Html
• Step 6: Build Express Route
• Step 7: Build Server File
• Step 8: Serve Node Application

Build Node Project

On the terminal’s command-prompt enter the given command, then execute the command to build a new folder for new node project.

Next, enter inside the app folder.

In order to install additional packages in the node, we need to have a specific package.json file located in our app; hence run the given below command to generate the package file modules.

Install NPM Dependencies

Add the given set of commands and run the commands respectively to install packages from npm registry altogether.

npm install -g express-generator npm install npm install mysql body-parser express-session nodemon express-flash

Create SQL Table

In this step, we will create a table in the MySQL database; creating a table is super easy with SQL query; hence run the given command to complete the task.

CREATE TABLE `users ` ( `id` int(20) NOT NULL, `name` varchar(155) NOT NULL, `email` varchar(155) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Make MySQL Database Connection

To connect to MySWL database, create a database.js file, then in this file you have to add your hostname, username, password and database name as given in the given code example.

var mysql = require('mysql') var connection = mysql.createConnection( host: 'localhost', user: 'root', // password: '', // database: 'test', >) connection.connect((err) =>  if (err)  console.log(err) return > console.log('Database connected') >) module.exports = connection

Display Records in HTML

To display data in the HTML view file, we need to create the profile.ejs file in the views/ folder, also insert the given code in the view file.

DOCTYPE html> html lang="en"> head> title> Node Js MySQL Fetch and Show Records from MySQL Database Example title> meta charset="UTF-8" /> meta name="viewport" content="width=device-width, initial-scale=1" /> link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" /> head> body> div class="container mt-4">  if (messages.success)  %> p class="alert alert-success mt-4"> messages.success %>p>  > %> br /> table class="table"> thead> tr> th>#Idth> th>Nameth> th>Emailth> th>Actionth> tr> thead> tbody>  if(data.length) for(var i = 0; i data.length; i++) %> tr> th scope="row"> (i+1) %>th> td> data[i].name%>td> td> data[i].email%>td> tr>  > >else %> tr> td>No data ever existed.td> tr>  > %> tbody> table> div> body> html>

Build Express Route

Head over to the users.js file; you can find this file inside the routes directory; here, we will use the express js instance to formulate the route that will get the result from the database.

var express = require('express') var connection = require('../database.js') var router = express.Router() router.get('/', function (req, res, next)  connection.query('SELECT * FROM users ORDER BY id desc', function (err, rows)  if (err)  req.flash('error', err) res.render('profile',  data: '' >) > else  res.render('profile',  data: rows >) > >) >) module.exports = router

Build Server File

To set up the node server, you have to create the app.js file. In this file, we will write the code to run the node app.

var createError = require('http-errors') var express = require('express') var path = require('path') var logger = require('morgan') var bodyParser = require('body-parser') var flash = require('express-flash') var cookieParser = require('cookie-parser') var expressValidator = require('express-validator') var session = require('express-session') var mysql = require('mysql') var connection = require('./database') var nodeRoutes = require('./routes/index') var userRoute = require('./routes/users') var app = express() app.set('views', path.join(__dirname, 'views')) app.set('view engine', 'ejs') app.use(logger('dev')) app.use(bodyParser.json()) app.use(cookieParser()) app.use(bodyParser.urlencoded( extended: true >)) app.use(express.static(path.join(__dirname, 'public'))) app.use( session( secret: '123@abcd', resave: false, saveUninitialized: true, cookie:  maxAge: 60000 >, >), ) app.use(expressValidator()) app.use(flash()) app.use('/', nodeRoutes) app.use('/users', userRoute) app.use(function (req, res, next)  next(createError(404)) >) app.listen(5555, function ()  console.log('Node server running on port : 5555') >) // error app.use(function (err, req, res, next)  res.locals.message = err.message res.locals.error = req.app.get('env') === 'development' ? err : > res.status(err.status || 500) res.render('error') >) module.exports = app

Serve Node Application

We can execute the node script using the given command, make sure to run the given command.

You have to type the given url on the browser to run the app.

Conclusion

Throughout this guide, we learned in detail how to connect the node js app to the MySQL database simultaneously, render the data from the MySQL database, and show the data into the HTML table within a Node js application.

To accomplish this small functionality, we installed and used various NPM modules, and we shared the idea utterly with you to build such a feature.

A Full-stack developer with a passion to solve real world problems through functional programming.

Источник