Accessing database from javascript

How to Connect to Database in JavaScript

Yesterday was super happy day to meet many friends! Such a great reunion online during the pandemic period.

Also, I have fetched data from external api to javascript file and render map successfully. I will work on building database. Good guidance to start with.

INTRODUCTION DATABASE TROUBLE

Welcome to a tutorial on how to connect to a database in Javascript. Yes, it is totally possible to connect to a database with modern Javascript these days. But it is a different process depending on where you are applying it to:

• When used on a web page (client-side Javascript), we usually make an AJAX call to a server-side script first. That in turn, will make a connection to the database.
• Javascript can also directly access a server-side database, but provided that it has an open HTTP API. This is a security risk though, and it should never be considered.
• Alternatively, there is the web storage API that allows Javascript to save some data on the client computer.
• Finally, with NodeJS (server-side Javascript), we can easily connect to a database with the correct module installed.

So just how exactly do we connect to the database in the above cases? Let us walk through some examples, and read on to find out!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

NAVIGATION TABLE OF CONTENTS

Extra Source Code Download	Section A The Basics	Section B AJAX Database
Section C Local Storage	Section D NodeJS Database	Extra Useful Bits & Links
Closing What’s Next?

EXTRA SOURCE CODE DOWNLOAD

First, here is the download link to the example source code as promised.

SOURCE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

QUICK START

• Download and unzip into a folder.
• There are 3 sets of code in this tutorial, each prepended with a number.
  • Follow files starting with 1- if you want to connect to a database on the server (AJAX call to a server-side script).
  • Files with 2- for local storage.
  • Finally, files with 3- for NodeJS database connection.

  SECTION A THE BASICS

  Before we go into any code, here is a small section dedicated to the confused beginners – What server-side and client-side is, and how Javascript can be used beyond webpages. Feel free to skip this section if you are already a code ninja.

  SERVER AND CLIENT

  

  To clear things up a little bit, most beginners probably know Javascript from web development. I.E. Creating web pages. In this traditional client-server model:

  • A user accesses a web page, the browser sends a request to the server.
  • The server then responds by sending back the requested web page.

  As simple as this might be, please take extra note that Javascript is downloaded and runs on the client computer.

  CLIENT JAVASCRIPT TO DATABASE

  

  So for Javascript to connect to the database in this model:

  • We will usually create a server-side script first (in PHP, ASP, Python, etc…) that will connect to the database.
  • Javascript will communicate with this server-side script instead.

  WHY DIRECT DATABASE CONNECTION IS BAD

  

  If you are looking to directly connect a client Javascript to the server database, without any server-side scripts – I will strongly discourage it for security reasons.

  • Remember that Javascript is downloaded onto the client computer? This means that anyone can mess around with the scripts, do funny things to the database.
  • We will have to open a direct connection to the database on the Internet, which also means the database will be vulnerable to any attack on the Internet.
  • There is a reason why server-side scripts exist – They are transparent and cannot be easily messed with. Users will not know how server scripts run, and we can put protection checks in place.
  • There is also a reason why most databases don’t offer a direct HTTP interface – It adds another layer of security when the database cannot be directly accessed.

  With that, I will omit this type of connection from this guide. But if you still want to swim in the shark-infested cyber ocean – Check out the HTTP API for MySQL database servers.

  LOCAL STORAGE

  

  So alternatively, if you are just looking to save some simple temporary data – There is a Web Storage API that allows Javascript to save limited stuff onto the client computer.

  SERVER-SIDE JAVASCRIPT

  Now, if you have not heard of it – Yes, Javascript has evolved beyond “the thing behind web pages”, and we can run it independently on a server. There is a project called NodeJS, and we can do all sorts of awesome stuff with it – Chat servers, GPS tracking, HTTP servers, and of course, connecting to database servers. So check it out if you have not already done so.

  SECTION B DATABASE VIA AJAX

  So far so good? Let us now go into one of the most common situations, connecting to the server database via an AJAX call to a server-side script. Take note that this example is based on PHP and MySQL, but the concept of creating a server-side script first is the same for any programming language – I will leave links to database connections for various languages in the extras section below.

  DUMMY DATABASE

  For the purpose of demonstration, we will use this simple dummy table.

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  INSERT INTO `users` (`user_id`, `user_name`) VALUES
  (1, ‘John Doe’),
  (2, ‘Jane Doe’),
  (3, ‘Johan Doe’),
  (4, ‘June Doe’);

  ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `user_name` (`user_name`);

  ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

  SERVER-SIDE SCRIPT

  Next, we will create the server-side script that connects to the database.

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  
  /* (A) SETTINGS */
  // ! CHANGE THESE TO YOUR OWN !
  error_reporting(E_ALL & ~E_NOTICE);
  define(‘DB_HOST’, ‘localhost’);
  define(‘DB_NAME’, ‘test’);
  define(‘DB_CHARSET’, ‘utf8’);
  define(‘DB_USER’, ‘root’);
  define(‘DB_PASSWORD’, »);

  /* (B) CONNECT DATABASE */
  try {
  $_PDO = new PDO(
  «mysql:host=» . DB_HOST . «;charset=» . DB_CHARSET . «;dbname=» . DB_NAME,
  DB_USER, DB_PASSWORD, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES => false,
  PDO::ATTR_PERSISTENT => true
  ]
  );
  }
  catch (Exception $ex) {
  if (LOG_KEEP) { addlog($ex->getMessage()); }
  die(«Error connecting to the database»);
  }

  /* (C) HANDLE REQUESTS */
  if ($_POST[‘req’]) { switch ($_POST[‘req’]) {
  case «get»:
  $_STMT = $_PDO->prepare(«SELECT * FROM `users`»);
  $_STMT->execute();
  $users = $_STMT->fetchAll();
  echo json_encode($users);
  break;

  case «add»:
  $pass = true;
  $error = «»;
  try {
  $_STMT = $_PDO->prepare(«INSERT INTO `users` (`user_name`) VALUES (?)»);
  $_STMT->execute([$_POST[‘name’]]);
  // $lastID = $_PDO->lastInsertID();
  } catch (Exception $ex) {
  $pass = false;
  $error = $ex->getMessage();
  }
  echo json_encode([
  «status» => $pass,
  «message» => $error
  ]);
  break;
  }}

  /* (D) CLOSE DATABASE CONNECTION */
  $_PDO = null;
  $_STMT = null;
  ?>

  Just change the database settings to your own, and how we work with this script works is easy – Simply post a request and the required parameters. For example, send $_POST[‘req’] = «add» and $_POST[‘name’] = «USER NAME» to add a new user.

  Remember from earlier that we mentioned using the server-side script as a layer of protection? This is exactly it – Users can only make certain requests through this script, and we can implement more checks as required. For example, only administrators who are logged in can get all users.

  CLIENT-SIDE JAVASCRIPT

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  
  
  
  
  function getUsers() {
  // APPEND FORM DATA
  var data = new FormData();
  data.append(‘req’, ‘get’);

  // AJAX CALL
  var xhr = new XMLHttpRequest();
  xhr.open(‘POST’, «1b-database.php», true);
  xhr.onload = function(){
  console.log(this.response);
  };
  xhr.send(data);
  }

  function addUser() {
  // APPEND FORM DATA
  var data = new FormData();
  data.append(‘req’, ‘add’);
  data.append(‘name’, document.getElementById(«user-name»).value);

  // AJAX CALL
  var xhr = new XMLHttpRequest();
  xhr.open(‘POST’, «1b-database.php», true);
  xhr.onload = function(){
  console.log(this.response);
  };
  xhr.send(data);
  return false;
  }
  
  
  

  
  
  
  

  Finally, all that is left in the Javascript is to make AJAX calls to the server-side script.

  WHAT ABOUT OTHER PROGRAMMING LANGUAGE & DATABASES?

  As in the introduction above, the concept is the same and there are 3 parts to deal with:

  • First, create the relevant tables in the database.
  • Secondly, create the server-side script that will act as the middle man – Accept requests from Javascript, do checks, and connect to the database.
  • Finally, deal with the Javascript that will call the server-side script.

  SECTION C LOCAL STORAGE

  Only need some temporary storage? Here is how we do it with the simple Web Storage API.

  THE BASICS

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  
  
  
  
  // (1) SET — Save data into local storage
  // In format of key => value
  localStorage.setItem(«Foo», «Bar»);
  localStorage.setItem(«Hello», «World»);

  // (2) GET — Retrive data with given key
  var data = localStorage.getItem(«Foo»);
  console.log(data);
  data = localStorage.getItem(«Hello»);
  console.log(data);

  // (3) REMOVE — Remove data with given key
  localStorage.removeItem(«Foo»);
  data = localStorage.getItem(«Foo»);
  console.log(data);

  // (4) CLEAR — Remove everything
  localStorage.clear();

  Local storage is very simple, and there are only 4 operations:

  • localStorage.setItem(KEY, VALUE) – Saves data into the local storage.
  • localStorage.getItem(KEY) – Retrieves data from the local storage.
  • localStorage.removeItem(KEY) – Removes data from the local storage.
  • localStorage.clear() – Remove everything.

  OBJECT STORAGE

  But the problem with local storage is that it only stores strings. To store and retrieve arrays/objects, we will have to use JSON.stringify() and JSON.parse() .

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  
  
  
  
  // (1) DATA OBJECT
  var user = {
  name : «John Doe»,
  email : «john@doe.com»,
  gender : «Doe»
  };

  // (2) CONVERT JSON STRING
  user = JSON.stringify(user);
  console.log(user);

  // (3) STORE IN LOCAL STORAGE
  localStorage.setItem(«User», user);

  // (4) RETRIEVE — SIMPLY GET AND DECODE
  user = localStorage.getItem(«User»);
  user = JSON.parse(user);
  console.log(user);

  SECTION D NODEJS DATABASE

  In this final section, we will walk through how to connect to a database in NodeJS.

  INSTALL DATABASE MODULE

  First, navigate to your project folder in the command line, and install the database module:

  D:\YOUR\PROJECT> npm install mysql

  For the sake of simplicity, we are just going to reuse the above dummy database table and MySQL. If you are not planning to use MySQL, there are also several other Node Database Modules:

  THE SCRIPT

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  // (1) LOAD DB MODULE
  const mysql = require(«mysql»);

  // (2) CREATE CONNECTION
  // ! CHANGE THESE TO YOUR OWN !
  const db = mysql.createConnection({
  host: ‘localhost’,
  user: ‘root’,
  password: »,
  database: ‘test’
  });
  db.connect((err) => {
  if (err) { throw err; }
  console.log(«DB connection OK»);
  });

  // (3) QUERY
  db.query(«SELECT * FROM `users`», function (err, results) {
  if (err) { throw err; }
  console.log(results);
  });

  Yep, it’s that simple – Just load the database module and make a connection. But take note that this is based on MySQL again, install and load your own if you are using something else.

  EXTRA USEFUL BITS

  That’s all for this tutorial, and here is a small section on some extras and links that may be useful to you.

  LINKS & REFERENCES

  OTHER NOTEWORTHY TUTORIALS

  Источник