CRUD - 4 SQL Commands, their Javascript equivalents and MORE

CRUD is a popular acronym for SQL and relational databases as it describes the basic operations performed on a table.

Create = insert a row into our table
Read = search thru our table
Update = modify an existing record in a table
Delete = remove record from a table

Above is the conventional representation of CRUD found everywhere. But what about the Javascript equivalents of CRUD? How do we represent these basic operations without using SQL, just in plain javascript. We use an Array in Javascript vs a table in SQL. We use array methods to manipulate our arrays in JS.

Create.. JS Equivalent = array.push, this inserts a new row
example..items = [{'id': 1}, {'id': 2}, {'id': 3}, {'id': 4}]; //original array
newArray = items.push({'id':5})

vs in SQL we have.. insert into table_name
example - INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

Read.. JS Equivalent can be accomplished in several ways but mostly we can use either array.forEach or just put our conditions into a recursive function, a self iterative method usually accompanied by an if clause or for loop. Reading in JS means iterating thru our array

example..
const array1 = ['a', 'b', 'c'];
array1.forEach(element => console.log(element));

vs in SQL...we use the SELECT statement
example..SELECT column1, column2, ...
FROM table_name;

update.. JS Equivalent..we use an IF condition to determine when during array iteration a condition is met then update our value

example...
var jsonObj = [{'Id':'1','Username':'Ray','FatherName':'Thompson'}, {'Id':'2','Username':'Steve','FatherName':'Johnson'}, {'Id':'3','Username':'Albert','FatherName':'Einstein'}];
for (var i=0; i<jsonObj.length; i++) {
if (jsonObj[i].Id == 3) { //when id is 3
jsonObj[i].Username = "Thomas"; //update user name
break;
}
}
alert("New Username: " + jsonObj[2].Username);

vs in SQL we use a where clause to denote which value to update
example
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

delete JS Equivalent = array.splice
example..
for (var i = 0; i < array.length;) {
if(array[i].key == what u want to delete) {
array.splice(i,1); //deletes whole row

} else {
++i; //increment only when line not removed
}
}

vs in SQL we do this..DELETE FROM table_name WHERE condition;
or drop table command..DROP TABLE table_name;

Part 2
But there is MORE than basic data operations to be aware of on the Javascript side.

1. Create means something else in SQL in addition to insert a record. In SQL we create the database or schema and then we need to create a table. In Javascript we represent our table with a json array. So to create a javascript table we merely convert csv into json, then we can insert records. See below for some convenient JS to convert. In order to use this tool start with some data in a google sheet then download to csv, then convert csv to json. Then we need to save the json somewhere. In SQL we have databases like mysql and oracle to save our data. In Javascript I like to save my data in an AWS S3 bucket and access it using the fetch api.

2. In Javascript we can also Read remote data using the Fetch API.
   example fetch array stored in AWS s3 bucket:
   const response=fetch('https://xxxxx.s3.us-...aws.com/file.json');
   const data = await response.json();
   console.log(data);

3. When inserting a row in JS there is no append function so we need to overwrite the original array in order to save our insert. we do this in lambda or in a function we create the interim array or resultant array then operate on it as if it were the final array.

4. we need to apply CRUD to data analytics (data visualization) with grouping and summing. In Javascript we can use array.reduce for summing and array.indexOf for grouping. An example of this would be a bar chart where we measure results over time. Thus we group our data into months then sum categories for each month. Click here for a cool use case of a stacked bar chart..... https://dev.to/rickdelpo1/how-to-populate-a-stacked-bar-chart-in-plain-javascript-12p9

Conclusion
JSON Arrays are the Javascript equivalent to the SQL table. We iterate over arrays in JS like we select in SQL. But arrays in JS come with some quirks and can be a bit tricky at times. SQL seems to be most stable and robust. However, both SQL and NoSQL JS are a must learn for the beginner to broaden general knowledge and skills.

below we convert csv to json, copy and paste into notepad

<!DOCTYPE html>
<html>
<!--this file is extended to change various columns to int format-->

  <head>
    <title>convert CSV to JSON</title>
  </head>

  <body>
    <center><h2>Upload a CSV file to convert in JSON</h2>

    <!-- Input element to upload an csv file -->
    <input type="file" id="file_upload" />
    <button onclick="upload()">Upload</button>  
    <br>
    <br>
    <!-- container to display the csv data -->
    <div id="display_csv_data"></div>
    </center>

<script>

               // Method to upload a valid csv file called from inut button
      function upload() {
        var files = document.getElementById('file_upload').files;
        if(files.length==0){
          alert("Please choose any file...");
          return;
        }
        var filename = files[0].name;
        var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
        if (extension == '.CSV') {
                 //Here calling another method to read CSV file into json
            csvFileToJSON(files[0]);
        }else{
            alert("Please select a valid csv file.");
        }
      }

               //Method to read csv file and convert it into JSON 
      function csvFileToJSON(file){
          try {
            var reader = new FileReader();
            reader.readAsBinaryString(file);
            reader.onload = function(e) {
                var jsonData = []; //starts as empty array, this is the resultant file that we further extend to gets ints where needed
                var headers = [];
                var rows = e.target.result.split("\r\n");               
                for (var i = 0; i < rows.length; i++) {
                    var cells = rows[i].split(",");
                    var rowData = {}; //rows are bracketed and comma separated, this is an empty row
                    for(var j=0;j<cells.length;j++){
                        if(i==0){
                            var headerName = cells[j].trim();
                            headers.push(headerName);
                        }else{
                            var key = headers[j];
                            if(key){
                                rowData[key] = cells[j].trim();
                            }
                        }
                    }
                       //skip the first row (header) data
                    if(i!=0){
                        jsonData.push(rowData); //append to empty array
                    }
                }

                     //displaying the json result in string format
                document.getElementById("display_csv_data").innerHTML=JSON.stringify(jsonData); //all strings

                   //to change some columns to int format do below
       //first read jsonData as new array called arr, include cols to be modified like below then PRESS ctrl,shift,J TO VIEW AND COPY our arr result
          //by default, conversion to json results in all strings 
          //sometimes we need to modify the array to include ints in certain fields, here is how to do this
          //note, need to modify 4 parse items below as needed based on the input in csv 
                         //the following is optional if u have no cols to transform to int format
var arr = jsonData.map(item => { 
   return {
      ...item, //copies all items first...
      dollar_amt: parseInt(item.dollar_amt), //...then overwrites dollar_amt // for each pass if key is dollar_amt then parse this val to int....note replace my keys with your own keys that need displaying in int format
      product1:  parseInt(item.product1),    //parse others as needed
      product2:  parseInt(item.product2),
      product3:  parseInt(item.product3)                                     
   }
}
)
console.log(arr);
console.log(JSON.stringify(arr)); //print readable version, viewable in ctrl, shift, j mode
                }
            }catch(e){
                console.error(e);
            }
      }

    </script>

  </body>
</html>