Node.js MySQL (MariaDB). Web server in PC. Insert. Update. Delete. Select

Juan_Antonio · August 4, 2021, 5:10pm

Hello friends,

in this topic I wrote about the installation ("Portable") of Node.js on our PC, it is very easy, download the .zip file, unzip it, open the Windows CMD Shell and go:

In this other topic I wrote about App Inventor and MySQL.

Now we are going to play with App Inventor, Node.js and MySQL.

We will take part of the codes of this great tutorial:
Node.js MySQL

Here this tutorial in Spanish:
Node.js y MySQL (MariaDB) App Inventor.

Juan_Antonio · August 4, 2021, 5:11pm

Juan_Antonio · August 4, 2021, 5:24pm

1.- We install MariaDB.

MariaDB is a "fork" of MySQL, very very similar, it uses the same codes.

I downloaded the version mariadb-10.4.20-winx64.msi (58 MB), because it is the one that works on my Windows 7.
It will run as a Service:
-
node28800×163 72.4 KB
HeidiSQL is like phpMyAdmin, we can create databases, tables, manage the database,... in "graphical mode".

node29900×296 125 KB

2.- Install driver MySQL in Node.js

npm install mysql

Juan_Antonio · August 4, 2021, 5:35pm

3.- We continue with the tutorials from w3schools. com/nodejs/nodejs_mysql.asp

- CREATE DATABASE:

create_db.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query("CREATE DATABASE mydb", function (err, result) {
    if (err) throw err;
    console.log("Database created");
  });
});

- CREATE TABLE:

create_table.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
  database: "mydb" // Data Base name.
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table created");
  });
});

- INSERT:

insert_db.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
  database: "mydb" // Data Base name.
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

- INSERT A LOT OF DATA:

insertlot_db.js

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "1234",
  database: "mydb" // Data Base name.
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO customers (name, address) VALUES ?";
  var values = [
    ['Peter', 'Lowstreet 4'],
    ['Amy', 'Apple st 652'],
    ['Hannah', 'Mountain 21'],
    ['Michael', 'Valley 345'],
    ['Sandy', 'Ocean blvd 2'],
    ['Betty', 'Green Grass 1']
  ];
  con.query(sql, [values], function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

Now we are going to join those codes into a single file...

Juan_Antonio · August 4, 2021, 5:36pm

4.- Server + MySQL codes.

We create the server.js file with this content:

Watch out for the 'single quote' and the "double quote" and '''both together'''.

// Juan A. Villalpando
// http://kio4.com/arduino/117_Wemos_NodeJs_MySQL.htm

//-------------------------------------------------------------------------------------
var url = require('url');
var http = require('http');
var querystring = require('querystring');
var mysql = require('mysql');
//-------------------------------------------------------------------------------------
function requestHandler(request, response) {
    var uriData = url.parse(request.url);
    var pathname = uriData.pathname; 
    var query = uriData.query;
    var queryData = querystring.parse(query);
	
	var con = mysql.createConnection({
	  host: "localhost",
	  user: "root",
	  password: "1234",
	  database: "mydb" // Data Base name.
	});
    //--- INSERT ------------------------------------------------------------------------------
    if (pathname == '/insertar') {	
var name = queryData.name;
var address = queryData.address;
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO customers (name, address) VALUES ('"+name+"',' "+address+"')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(name);
	response.end("Datos agregados a la base.");
  });
  con.end();
});
    //--- DELETE ------------------------------------------------------------------------------
  } else if (pathname == '/borrar') {
var name = queryData.name;
con.connect(function(err) {
  if (err) throw err;
   var sql = "DELETE FROM customers WHERE name ='"+name+"'";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Number of records deleted: " + result.affectedRows);
	response.end("Datos borrados.");
  });
   con.end();
});	
    //--- UPDATE ------------------------------------------------------------------------------
  } else if (pathname == '/actualizar') {
var name = queryData.name;
var address = queryData.address;
con.connect(function(err) {
  if (err) throw err;
   var sql = "UPDATE customers SET address = ' "+address+" ' WHERE name ='"+name+"'";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Number of records updated: " + result.affectedRows);
	response.end("Datos modificados.");
  });
   con.end();
});	
    //---- SELECT WHERE -----------------------------------------------------------------------
  } else if (pathname == '/buscar') {
var name = queryData.name;
var address = queryData.address;
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers WHERE name = '"+name+"'", function (err, result) {
    if (err) throw err;
    console.log(result);
    response.end(JSON.stringify(result));
  });
   con.end();
});		
    //--- SELECT ------------------------------------------------------------------------------
  } else if (pathname == '/ver') {
var name = queryData.name;
var address = queryData.address;
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers ", function (err, result) {
    if (err) throw err;
    console.log(result);
    response.end(JSON.stringify(result));
  });
      con.end();
});		
    //---------------------------------------------------------------------------------
    } else { 
            response.end("Wrong order.");
    }
    //---------------------------------------------------------------------------------
}
var server = http.createServer(requestHandler);
server.listen(8000); 
console.log('Server listening on port 8000');

- App Inventor:

p340Bi_mysql_node_get.aia (2.8 KB)

It is a simple code but it can be a first step to learn MySQL with App Inventor.
For more protection (SQL injections) see Escaping Query Values,
https://www.w3schools.com/nodejs/nodejs_mysql_where.asp

Juan_Antonio · August 4, 2021, 5:51pm

ABG · August 4, 2021, 9:08pm

(added to FAQ)