How to Connect to Database in Javascript (Simple Examples)

Welcome to a tutorial on how to connect to a database in Javascript. Need to connect to the database in Javascript to fetch or save some data?

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

 

 

TLDR – QUICK SLIDES

[web_stories_embed url=”https://code-boxx.com/web-stories/connect-database-javascript/” title=”How To Connect To Database In Javascript” poster=”https://code-boxx.com/wp-content/uploads/2021/11/STORY-JS-20230518.webp” width=”360″ height=”600″ align=”center”]

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

 

 

STEP 1) DUMMY DATABASE TABLE

CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO `users` (`user_id`, `user_name`) VALUES
(1, 'Joe Doe'),
(2, 'Jon Doe'),
(3, 'Joy Doe');

For the purpose of demonstration, we will use this dummy user table – Just 2 fields here, the user ID and name.

 

STEP 2) SERVER-SIDE SCRIPT (THAT CONNECTS TO DATABASE)

<?php
// (A) SETTINGS - CHANGE TO YOUR OWN !
error_reporting(E_ALL & ~E_NOTICE);
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (B) CONNECT TO DATABASE
$pdo = new PDO(
  "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
  DB_USER, DB_PASSWORD, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

// (C) GET USERS
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
$users = $stmt->fetchAll();
foreach ($users as $u) {
  printf("<div>[%u] %s</div>", $u['user_id'], $u['user_name']);
}

// (D) CLOSE DATABASE CONNECTION
$pdo = null;
$stmt = null;

Next, this server-side script will connect to the database. It simply gets all the users from the database and outputs them in HTML.

 

 

STEP 3) HTML & JAVASCRIPT (CALL SERVER-SIDE SCRIPT)

<!-- (A) LIST USERS HERE -->
<div id="demo"></div>

<!-- (B) FETCH USERS VIA AJAX -->
<script>
// NOTE: RUN WITH HTTP://, NOT FILE://
window.addEventListener("load", () => {
  fetch("1b-database.php", { method: "POST" })
  .then(res => res.text())
  .then(txt => document.getElementById("demo").innerHTML = txt);
});
</script>

Finally, all that’s left is to use Javascript on the webpage to make an AJAX fetch call to the server-side script.

P.S. AJAX stands for Asynchronous Javascript and XML. In simple terms, “load data without refreshing the entire page”.

 

WHY THE ROUNDABOUT METHOD?

So why this “dumb roundabout” way of using Javascript to call a server-side script, then to the database? Isn’t it simpler for Javascript to just directly connect to the database? Well, that is possible, but the key consideration here is security. For Javascript to make a direct connection to the database:

• The database will have to be directly exposed to the Internet to accept connections.
• The database user and password have to be “hardcoded” in Javascript.
• Being client-side in this case, the source code and user/password are fully visible to all users.

This is why keeping database connections behind the server-side script makes more sense. Users don’t get to see the user/password, and cannot directly mess with the database.

 

 

 

STEP 1) INSTALL THE 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:

• MSSQL
• MongoDB
• Postgres

 

STEP 2) THE SCRIPT

// (A) LOAD DB MODULE
const mysql = require("mysql");

// (B) CREATE CONNECTION - CHANGE 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");
});

// (C) QUERY
db.query("SELECT * FROM `users`", (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.

 

 

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

ALTERNATIVE) LOCAL STORAGE 

// (A) DATA OBJECT
var user = {
  name : "Jon Doe",
  email : "jon@doe.com"
};
 
// (B) STORE IN LOCAL STORAGE
// note: json encode
localStorage.setItem("User", JSON.stringify(user));
 
// (C) RETRIEVE
// note: json decode
user = localStorage.getItem("User");
user = JSON.parse(user);
console.log(user);

If you only need some simple temporary storage for your website, you don’t need a full database. Here’s how we can store some simple variables using the Web Storage API, there are only 4 operations:

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

 

LINKS & REFERENCES

• PHP PDO Database Connection
• ASP Database
• Python Database
• Ruby DBI
• Javascript Local Storage

 

INFOGRAPHIC CHEAT SHEET