DEV Community: Grant

PL/SQL Basics

Grant — Mon, 21 Sep 2020 13:50:24 +0000

PL/SQL stands for "Procedural Language for SQL". The Oracle Corporation created PL/SQL in the 1980's for its own Oracle database to supplement basic SQL with procedures, functions, packages, and triggers. Below are some of the major differences between SQL and PL/SQL:

SQL	PL/SQL
Executes one operation at a time	Executes multiple operation at a time
Declarative language	Procedural language
Data-oriented language	Application-oriented language
No variables	Variables
Interacts with database server	Does not interact with database server
No `for` loop or `if` statement	`for` loop, `while` loop, and `if` statements

As the above table shows, PL/SQL fills in many of the functionalities of typical high level programming languages. Now to take a look at some examples, we'll start with the anonymous block. The anonymous block is comprised of three parts, separated by four keywords, as such:
DECLARE
Declaration Section
BEGIN
Execution Section
EXCEPTION
Exception Section
END;

The declaration section is for the declaration of variables/constants. Note the use of :=, which is PL/SQL's assignment operator. The execution section is where the behavior is coded, and the exception section is where errors are handled. While the declaration and exception sections are optional, the execution section is required (the label is also optional).

<<label>>
DECLARE
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);
END;

Functions are formatted similarly, with declarations at the top that include input and return type, a declaration (the "business", and an exception block:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] <IS|AS>   -- heading part
amount number;                              -- declaration block
BEGIN                                       -- executable part
    <PL/SQL block with return statement>
        RETURN <return_value>;
[Exception
    none]
        RETURN <return_value>;
END;

Pipeline functions return collection but are performance-optimized so that each row that is created in the return dataset is returned as it's created—as opposed to waiting for the entire collection to be finished. Notice the use of keyword PIPELINED and the call of PIPE ROW that delineates the structure for each individual row:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>
    [declaration block]
BEGIN
    <PL/SQL block with return statement>
        PIPE ROW(<return type>);
        RETURN;
[Exception
    exception block]
        PIPE ROW <return type>;
        RETURN;
END;

Procedures are like functions but vary is two key ways: functions should only return a single value/collection, whereas procedures can return multiple, and while procedures can't be used in SQL statements, functions actually can.

CREATE PROCEDURE create_email_address (   -- Procedure heading
    name1 VARCHAR2,
    name2 VARCHAR2,
    company VARCHAR2,
    email OUT VARCHAR2
)                                         -- Procedure
AS
                                          -- Declarative (optional)
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN                                     -- Executable (required)
    email := name1 || '.' || name2 || '@' || company;
EXCEPTION                                 -- Exception (optional)
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(error_message);
END create_email_address;

Conditionals

Conditionals are fairly straight-forward as they read well in English, but note the need for an END IF statement. END statements are a common theme.

IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSE
   sequence_of_statements_N;
END IF;

CASE statements are much like if statements but potentially simpler to write:

CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   ELSE sequence_of_statements_N;
END CASE;

Loops are a huge benefit of PL/SQL. Here's a very basic outline of a FOR loop, which will log the numbers between 0 and 10, as indicated by the range operator ("..") between the two numbers, and the "DBMS_OUTPUT.PUT_LINE()" method.

DECLARE
    var NUMBER;
BEGIN
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;
END;

I hope you've enjoyed this brief intro to PL/SQL as much as I enjoyed looking into it! I'm definitely excited to continue my journey into database topics by diving further into Oracle Database, the most popular database system in the world!

source

Comparing Data Handling in MySQL and PostgreSQL: the RETURNING clause and CTE's

Grant — Mon, 14 Sep 2020 23:14:21 +0000

As a follow-up to my last post titled "Comparing Data in MySQL and PostgreSQL", I'd like to discuss some differences between these two databases when it comes to actually working with data. And like my previous post, many of these observations were enlightening as to the benefits PostgreSQL can offer.

One major inconvenience of working with MySQL is the need to run an additional query in order to get a row that was just inserted. At best, MySQL only returns the id of the most recently inserted row, but no additional columns (the id is critical since it was auto-incremented and the user would presumably not know what it is). If using MySQL in node.js, for example, an INSERT query returns a json message such as this, where the id of the inserted row is 6:

{
    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 6,
    "serverStatus": 3,
    "warningCount": 0,
    "message": "",
    "protocol41": true,
    "changedRows": 0
}

In command-line MySQL, however, one would have to use the LAST_INSERT_ID() function to run a query to get said id:

mysql> SELECT LAST_INSERT_ID();
       +------------------+
       | LAST_INSERT_ID() |
       +------------------+
       |                6 |
       +------------------+
       1 row in set (0.03 sec)

...and then you would still have to perform another query to get any other columns from the row.

PostgreSQL, however, has the unbelievably sensible RETURNING clause, which can't be lived without after knowing about it (guilty). RETURNING is essentially a bridge from an INSERT query directly into a SELECT query in which you decide what parts of the inserted row are returned and how. Here's an example in a PostgreSQL database sports, where a sport is inserted into a table sports, and its id is returned, along with the name (aliased as "sportName"), but NOT the season:

sports=# INSERT INTO sports(name, season)
         VALUES('football', 'fall')
         RETURNING sports.id, sports.name AS "sportName";

         id | sportName 
         ----+-----------
           3 | football
         (1 row)

         INSERT 0 1

RETURNING is a not-so-subtle difference in functionality between these two databases that's hard to ignore. But its functionality has more ramifications when considering Common Table Expressions. CTE's are essentially a means of aliasing subqueries that present the user with a very readable code block. Both MySQL and Postgres have CTE's, but when combined with Postgres's RETURNING clause, a CTE can accomplish streamlined sequential insertions without the need for multiple transactions. In the example below, a CTE is used to get the id of the sport we added previously, then a new league 'NFL' is inserted with the id of the selected sport as foreign key id_sport, and once the league exists, a team is inserted using the league's id and sport's id.

sports=# WITH sport AS (
           SELECT id
           FROM sports
           WHERE name = 'football'
         ), league AS (
           INSERT INTO leagues (name, id_sport, level)
           VALUES ('NFL', (SELECT sport.id FROM sport), 'professional')
           RETURNING id, id_sport
         )
         INSERT INTO teams (designation, nickname, id_sport, id_league)
         SELECT 'New Orleans', 'Saints', sport.id, league.id
         FROM sport
         JOIN league ON TRUE
         RETURNING * \gx

         -[ RECORD 1 ]------------
         id          | 7
         designation | New Orleans
         nickname    | Saints
         id_sport    | 3
         id_league   | 2

         INSERT 0 1

Accomplishing the same thing in MySQL might look something like the following example, where the league is inserted, then in a new query, the id of the just-inserted league is selected to use as the foreign key to insert the team. And if any information beyond the id is needed for the team, another query will certainly have to be made of course.

mysql> INSERT INTO leagues (name, id_sport, level)
    -> VALUES ('NFL', (
    ->     SELECT sports.id
    ->     FROM sports
    ->     WHERE name = 'football'
    ->   ), 'professional');
       Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO teams (designation, nickname, id_sport, id_league)
    -> SELECT 'New Orleans', 'Saints', (
    ->     SELECT sports.id
    ->     FROM sports
    ->     WHERE name = 'football'
    ->   ), (
    ->     SELECT LAST_INSERT_ID()
    ->   );
       Query OK, 1 row affected (0.00 sec)
       Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from teams \G
       *************************** 1. row ***************************
                id: 7
       designation: New Orleans
          nickname: Saints
          id_sport: 3
         id_league: 2
       1 row in set (0.00 sec)

The difference here may seem trivial, but Postgres's singular query capability could have a significant impact when writing queries away from the command line and within an application. This is, in fact, something I personally have been inconvenienced by when using MySQL before I knew any different. It wasn't until writing these comparisons that I came to realize some of the things I thought a database should logically be able to do were, indeed, possible—with another database. This is not to say that MySQL is without merit, of course (it is the most popular open-source database after all—over twice as popular as Postgres, in fact), but that sometimes, branching out into unfamiliar territory can produce solutions you may not have known existed. Fittingly, other database users appear to be making some of the same conclusions, as PostgreSQL and others continue to make gains in evening out the playing field of relational databases.
source: db-engines.com/

Comparing Data in MySQL and PostgreSQL

Grant — Mon, 07 Sep 2020 14:24:42 +0000

MySQL and PostgreSQL are very commonly used databases that share a lot of characteristics, while maintaining some distinct traits. As for basic similarities, both use MySQL and PostgreSQL use SQL, or Structured Query Language. Both are also relational databases that have tabular data (data in tables), and they have similar CLI's (Command Line Interface). This is only the surface, however, and as you look deeper, the differences may seem more drastic. In this post, we'll take a closer look at the differences between PostgreSQL and MySQL's treatment of data types.

First off, the concept of a schema is fundamentally different between these two databases. MySQL essentially considers a schema to be the same as a database (or it is at least the basic format that theoretically constitutes a database). PostgreSQL, on the other hand, considers a schema to be a configuration of settings to combine and use various elements of a single database without the need for multiple databases. In the pictures below, the red represents a MySQL schema (top) and a possible PostgreSQL schema (bottom).

As for basic data types, these two databases tend to sort commonly-used types into different numbers of categories based on size. MySQL, for example, has a large range of sizes for integers, such as TINYINT, SMALLINT, MEDIUMINT, INTEGER, and BIGINT, whereas PostgreSQL only uses three, SMALLINT, INTEGER, and BIGINT. Likewise, MySQL has more options for text-based VARCHAR field sizes than PostgreSQL.

PostgreSQL and MySQL also have different data types to handle binary (1, 0) data: BYTEA for PostgreSQL and BLOB for MySQL. And PostgreSQL can actually generate and store UUID's (Universally Unique Identifiers), a sort of random, encryption-style text. MySQL currently has no equivalent to PostgreSQL's UUID.

Booloeans are drastically different between the two databases. Whereas PostgreSQL handles booleans as any proper programming language, MySQL stores them only as binaries which are actually one-digit TINYINTs, such that the value of a boolean could theoretically be many other numbers as well. Take a look at the example below as an illustration:

mysql> CREATE TABLE test (
    -> id INT AUTO_INCREMENT,
    -> bool BOOLEAN,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 row affected (0.02 sec)

mysql> INSERT INTO test (bool) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (bool) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (bool) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bool \G
*************************** 1. row ***************************
  id: 1
bool: 0
*************************** 2. row ***************************
  id: 2
bool: 1
*************************** 3. row ***************************
  id: 3
bool: 2
3 rows in set (0.00 sec)

Another perk of PostgreSQL's is its flexible foreign data wrapper, which makes a "foreign table" (like a foreign key) and can represent a variety of configurations outside of a PostgreSQL table. Similarly useful are Postgres's Materialized Views, which save the result of a query as concrete data for later use, providing a time-efficient means of performing intensive queries when convenient. A separate issue is also MySQL's lack of the CHECK constraint, which is a flexible form of data validation. Likewise, PostgreSQL supports arrays and provides means of customizing your own data types with a variety of constraints, both of which are unavailable features in MySQL.

This post pains me a bit to write, actually. As I churn through these differences, consistently checking off reasons PostgreSQL is better than MySQL, I feel like I'm turning my back on my roots. MySQL is like home to me. It was MySQL that brought me my first database encounters—just a few short weeks ago—when I first discovered who I really am: a database weirdo. It's also just plain simpler, which is quite apparent considering the bevy of features it lacks compared to Postgres. But in databases, as in life, we must continue to evolve, while remembering where we came from—right? Even though this post has been bittersweet, I'm looking forward to sharing even more of Postgres's superior features in next week's post comparing Postgres's data handling to MySQL's. Until then, happy databasing!

Pool Party! MySQL Pool Connections in Node.js 💦🔌

Grant — Mon, 31 Aug 2020 13:12:04 +0000

Using a MySQL database in your application naturally requires a connection to that database. For small-scale development or experimenting, simply creating a single connection may work fine. More extensive operations like production or complex queries, however, may require more flexibility. In order to see the difference between a single connection and a connection pool, let's start with a simple example of the former:

const express = require('express');
const app = express();
const mysql = require('mysql');

const db_connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
});

db_connection.connect((err) => {
  if (err) console.error(err);
  console.log('MySQL Connection Established.');
});

const getUsers = () => new Promise((resolve, reject) => {
  db_connection.query('SELECT * FROM USERS', (err, results) => {
    if (err) console.error(err);
    console.log('User Query Results: ', results);
    resolve(results);
    db_connection.end(err => { if (err) console.error(err) });
  });
});

app.get('/', (req, res) => {
  getUsers()
    .then(users => res.send(users))
    .catch(err => console.error(err));
});

app.listen(8080, () => {
  console.log('Server listening on port 8080.');
});

This simple server will establish a database connection and query the database named "test" (previously created) for all rows from the table "users" (which was preloaded with one entry). After starting the server with node app.js, a Postman GET call to http://localhost:8080 sends back the expected response:

[
    {
        "id": 1,
        "username": "gdup",
        "location": "New Orleans"
    }
]

And in the terminal:

Server listening on port 8080.
MySQL Connection Established.
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]

BUT—a subsequent Postman call produces an empty response and kills the server with errors:

User Query Results:  undefined
Error: Cannot enqueue Quit after invoking quit.
    at Protocol._validateEnqueue (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:215:16)
    at Protocol._enqueue (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:138:13)
    at Protocol.quit (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:91:23)
    at Connection.end (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:239:18)
    at Query.<anonymous> (/Users/Work/immersion/database-test/app.js:22:19)
    at Query.<anonymous> (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:488:16)
    at Query.Sequence.end (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at /Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:236:14
    at processTicksAndRejections (internal/process/task_queues.js:79:11) {
  code: 'PROTOCOL_ENQUEUE_AFTER_QUIT',
  fatal: false
}

The problem here is the db_connection.end() part of our database connection. Once the connection is ended, it can't be restarted and used again. "Well why don't you just not end the connection?" you may be asking yourself. Well, that's all fine and good as long as nothing goes wrong with the single lifeline you have to the database. But beyond that, there are other reasons you want more flexibility. For one, you will likely eventually need to be able to have multiple connections for multiple simultaneous users. Likewise, you may eventually want to implement queries in which you need to have more than one connection. Keep in mind that all queries performed through a connection are sequential. If you have 10 queries that each take one second, the total execution time would be 10 seconds—unless they were run in parallel, of course.

Let's set up one more part of the demonstration by installing siege, which will allow us to blast our server with concurrent get requests. In a benchmark.js file, the following code will allow us to do just that by running node benchmark.js:

const siege = require('siege');

siege()
  .on(8080)
  .concurrent(10)
  .get('/')
  .attack();

Starting up the server and running node benchmark.js kills the server as expected and produces the same errors as Postman did above. Now let's refactor our connection:

const express = require('express');
const app = express();
const mysql = require('mysql');

const db_connection = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
});

const getUsers = () => new Promise((resolve, reject) => {
  db_connection.getConnection((err, connection) => {
    if (err) console.error(err);
    console.log('MySQL Connection Established: ', connection.threadId);
    connection.query('SELECT * FROM USERS', (err, results) => {
      if (err) console.error(err);
      console.log('User Query Results: ', results);
      resolve(results);
      connection.release(err => { if (err) console.error(err) });
    });
  });
});

app.get('/', (req, res) => {
  getUsers()
  .then(users => res.send(users))
    .catch(err => console.error(err));
});

app.listen(8080, () => {
  console.log('Server listening on port 8080.');
});

Now, instead of createConnection, we've used createPool, which makes a pool of connections ready to be borrowed. When the time comes, we use getConnection with connection as a parameter of the callback function. That connection, the one the pool gives us, is the one we'll use for the query. Now, running the siege test produces several sets of query results without breaking the server. Here are a few:

Server listening on port 8080.
MySQL Connection Established:  124
MySQL Connection Established:  123
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  123
MySQL Connection Established:  124
MySQL Connection Established:  125
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  126
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]

Notice how the console.log of the connection.threadId, which shows a distinct number for each connection. shows a range of connections used. We didn't actually specify how many to create, but default is 100. If we set the limit to 1 in our connection parameters:

const db_connection = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test',
  connectionLimit: 1,
});

...and run the siege attack again, we get the same single pool connection being used over and over:

Server listening on port 8080.
MySQL Connection Established:  131
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  131
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  131
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  131
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established:  131
User Query Results:  [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]

Take note of the connection.release() at the end of the query. This is an important part of the equation in which the connection is given back to the pool to be reused—much like a library book is borrowed from a library and subsequently returned. And as before, all connections can be closed (simultaneously) with db_connection.end() if needed.

I hope this tutorial on MySQL/Node.js pool connections has been helpful. Pool connections can seem mysterious at first, and how they work "under the hood" is not immediately apparent. With the experiment above, however, I hope you've also gained some concrete understanding of their functionality and can now agree that implementing pool connections is not only easy, but also quite beneficial!

Hosting Your Google Cloud App Engine's Database with Cloud SQL

Grant — Mon, 24 Aug 2020 08:09:27 +0000

Cloud SQL is the convenient in-house way to host your database when deploying an app via Google Cloud's App Engine. Here's a brief rundown of how to get your SQL database up and running:

Create a Cloud SQL Instance

In the Google Cloud Console for your project, go to Storage -> SQL -> Create Instance.

Options such as Instance ID, region/zone, storage capacity are up to the user. Once these configurations are made, you'll need your instance's IP address and Connection Name as shown below:

Your user account will need to have "Cloud SQL Admin" permissions, which are located in the "IAM" portion of the "IAM & Admin" section:

The Cloud Admin API must be enabled, along with the Cloud SQL API. These can be found in the "API's and Services" menu section. You will also need to have Google Cloud SDK installed, which allows you to control your Google Cloud applications through gcloud command line tools (among others)—note that you will have to run gcloud init the first time you use Google Cloud SDK. Lastly, in order for you to shell into Google Cloud SDK to maintain your database instance locally, you will have to list your ip address as an authorized network in the "connections" area ("add network") for your instance:

Accessing Your Cloud SQL Instance

To connect using the cloud shell, run gcloud sql connect [INSTANCE_NAME] --user=root and enter your password. To connect to the instance without encryption, you can shell into MySQL with mysql --host=[INSTANCE_IP] --user=root --password (where INSTANCE_IP is 0.00.000.000 in the above example) and enter the password you created for your instance. Note: this method is not secure, and you should pursue an SSL connection if managing sensitive information.

Connecting Your App to Your Cloud SQL Instance: app.yaml

App Engine apps use an app.yaml file for configurations and environmental variables. If you don't already have one, create an app.yaml file in your root directory. Note: the app.yaml file must be in the root directory of your project. It should contain the following information (at a minimum):

runtime: nodejs10
env_variables:
  DB_USER: "root"
  DB_NAME: "<database name>"
  DB_PASS: "<instance password>"
  DB_INSTANCE_CONNECTION_NAME: "<instance connection name>"

The host (the ip address) would only be used in production, but the instance connection name is what gets you connected in deployment. For production, you would want this information in your .env file:

  DB_USER: "root"
  DB_NAME: "<database name>"
  DB_PASS: "<instance password>"
  DB_HOST: "<instance ip>"

In production, a connection configuration would look something like this (where the configurations after the host property can be adjusted):

  const connection = mysql.createPool({
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    host: process.env.DB_HOST,
    connectTimeout: 10000,
    acquireTimeout: 10000,
    waitForConnections: true,
    queueLimit: 0,
  });

Meanwhile, in deployment, you'll need to connect your app to your database via socket, so let's establish a socketpath for the database (again, the configurations aren't fast-and-true):

  poolConnection = mysql.createPool({
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    socketPath: `/cloudsql/${process.env.DB_INSTANCE_CONNECTION_NAME}`,
    connectTimeout: 10000,
    acquireTimeout: 10000,
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 20,
  });

Notice the createPool used above. Pool connections are a critical part of database connection practices that you'll want to implement in your database operations. More to come on that topic in another blog post! But for now, we've learned how to create a Cloud SQL database instance, access it for maintenance, and connect our app for production and deployment modes. I hope this tutorial has been helpful!

Database Types Explained

Grant — Mon, 27 Jul 2020 13:16:18 +0000

SQL Databases

SQL databases are built around SQL, or Structured Query Language. Most SQL databases are relational database management systems (RDBMS), which store data that is primarily structured. Structured Query Language has been in existence since the 1970's, and is standardized for use across many RDBMS systems.

SQL Databases are tabular in that they store data in tables with rows and columns. The tables are interconnected through columns shared in common between tables. In the above image, the arrows show the columns that connect the data in various tables. SQL Databases are particularly well suited for systems dealing with transactions, such as customer relations management tools and accounting software. They are not, however, structured in such a manner as to "play well" with object-oriented programming languages, necessitating object-relational mapping tools such as Sequelize. Commonly used SQL Databases include Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Microsoft Access, and SQLite.

NoSQL Databases

NoSQL Databases, as the name implies, generally do not rely on SQL, though some may use SQL-like languages. Some, in fact, say NoSQL stands for "Not only SQL." NoSQL Databases are generally less structured, more diverse, and newer overall than their SQL counterparts. Some common examples are listed below.

Wide Column Store

Wide Column Stores are similar to SQL Databases in that they use tables of rows and columns. The columns, however, can change with each row, creating a 2-dimensional effect. Popular Wide Column Stores include Bigtable, Apache Cassandra, and Azure Tables.

Key-Value Databases

Key-Value Databases are essentially hash tables, both of which can be compared to a dictionary, in which one key (a word entry) corresponds to many underlying fields of information. The underlying fields for the keys in Key-Value Databases can vary from key to key, lending the system a great deal of flexibility in the data it can store. The key-value-pair system matches well with object-oriented programming, and since a key's values are not predetermined with placeholders as in a table, databases of this type perform very well in certain circumstances. Notable Key-Value Databases are Redis, Oracle NoSQL Database, Amazon DynamoDB, Infinity DB, and Oracle Berkeley DB.

Graph Databases

Graph Databases are suited for data that is highly interconnected, because of the "relationships" (also known as "edges") it maintains between nodes. As a result, complex data lookup can perform very highly because of the existing connections between data. Since SQL databases implicitly compare tables of highly ordered data, SQL is poorly suited to the nature of Graphs, in which the relationships are a concrete part of the data structure. Graphs, in fact, do not have a standardized language, and usually rely on a proprietary system. Common Graph Databases are Neo4j, Orient DB, Titan DB, and Apache Giraph.

Document-Oriented Databases

Document-Oriented Databases are one of the most prominent types of non-relational databases, and are actually a variety of Key-Value Database. They differ mainly in the way the database engine processes the data, rather than their structure. Documents are very closely related to objects in programming languages, and documents are very flexible. They can have any number of fields and fields can vary between documents. Documents themselves each have a unique key used to retrieve the documents itself, and they keys often have an index to hasten lookup. Prominent Document-Oriented Databases include MongoDB, Amazon DynamoDB, Google Cloud Firestore, ArangoDB, and Couchbase Server.

Sequelize: The Taming of the SQL

Grant — Mon, 20 Jul 2020 13:30:54 +0000

According to its source documentation, Sequelize is a 'promise-based ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server." ORM is short for object-relational mapping, which Wikipedia calls "a programming technique for converting data between incompatible type systems using object-oriented programming languages." In short, an ORM allows the user to package data in objects for database interaction, (mostly) circumventing the need for structured query language (SQL) commands. ORM's like Sequelize are essentially a translation tool between two likely very different computer programming languages. The "promise-based" part of Sequelize's description refers to javascript's Promise object, which allows for sequential, or "asynchronous" operations to be performed by returning a placeholder for a value that will eventually be determined.

Sequelize use begins with the creation of a new Seqeulize instance:

const { Sequelize } = require('sequelize');

...and a connection to a database, which can take one of two forms:
Passing parameters to the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

...or passing a connection URI:

const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

Once the plumbing is hooked up, models can be created for tables and their entries. The documentation defines a model as "a class that extends Sequelize.Model," and takes attributes and options. Models, like connections, can be one of two forms:
Using sequelize.define:

const Order = sequelize.define('order', {
  firstName: {                    // * attributes *
    Sequelize.STRING
  },
  lastName: {
    Sequelize.STRING
  }
  orderNumber: {
    type: Sequelize.INTEGER,
    primaryKey: true
  }
}, {
  language: 'en',                 // * options *
});

...or by creating a class that extends Model and calling init:

const Model = Sequelize.Model;
class Order extends Model { }
Order.init({
  firstName: {
    type: Sequelize.STRING,       // * attributes *
  },
  lastName: {
    type: Sequelize.STRING
  },
  orderNumber: {
    type: Sequelize.INTEGER,
    primaryKey: true
  }
}, {
  sequelize,                      // * options *
  modelName: 'order',
  language: 'en'
});

Once the initial work of creating a model is complete, insertions can be relatively simple:

const order17 = Order.create({ firstName: 'Bob', lastName: 'Johnson', orderNumber: 17 });

Notice the use of an arguments object as the parameter.

Meanwhile, a simple lookup query to retrieve an entire table can be as simple as:

const orders = Order.findAll();

...or as complex as any query written in SQL. Let's take a look at a few more examples:

To select only certain columns, attributes can be defined in an array:

Order.findAll({
  attributes: ['firstName', 'lastName']
});

And a similar pattern is available for excluding specified attributes:

Order.findAll({
  attributes: { exclude: ['lastName'] }
});

And among the most fundamental of database techniques is the where statement, used for filtering returns:

Order.findAll({
  where: {
    orderNumber: 51
  }
});

A critical component of the concept of applying where is the operator. In SQL, many operators are mathematical symbols–i.e. =, >, +=, etc., while others are strings–all, any, in, or, and more. Since Sequelize is object-based, operators are defined as properties of the Op object and are all strings. In fact, the example above actually had an implicit operator, assumed to be "equals", as such:

Order.findAll({
  where: {
    orderNumber: {
      [Op.eq]: 51
    }
  }
});

Here are some common operator examples in context:

[Op.and]: {a: 3}           // AND (a = 3)
[Op.or]: [{a: 7}, {a: 7}]  // (a = 7 OR a = 8)
[Op.gt]: 4,                // > 4
[Op.gte]: 4,               // >= 4
[Op.lt]: 10,               // < 10
[Op.lte]: 10,              // <= 10
[Op.ne]: 5,               // != 5
[Op.eq]: 3,                // = 3
[Op.not]: true,            // IS NOT TRUE
[Op.between]: [4, 8],     // BETWEEN 4 AND 8
[Op.notBetween]: [8, 10], // NOT BETWEEN 8 AND 10
[Op.in]: [1, 2],           // IN [1, 2]
[Op.notIn]: [1, 2],        // NOT IN [1, 2]

While this brief introduction leaves much to be learned, I hope your interest in database management has been piqued–as was mine! Arming yourself with a basic understanding of Sequelize's functionality such as this could go a long way to broadening your understanding of database management techniques.

"Structural Basics of ReactJS," or "PLEASE NO, NOT ANOTHER REACTJS PRIMER"

Grant — Mon, 13 Jul 2020 13:44:25 +0000

Yes, it's true. This is another ReactJS primer. But maybe, just maybe, this will be the one that makes it sink in for you. (It was for me, after all.)

Components

Components are the building blocks in ReactJS. At the most basic level, they take in data and return a React element, or, the thing that should appear on the screen. What's convenient is the data the function takes in is always called the same thing: props. Here's a very basic example:

const Greet = props => {
  return <h1>Hello from {props.location}</h1>;
}

Alternately, an ES6 class can accomplish the same thing:

class Greet extends React.Component {
  render() {
    return <h1>Hello from {this.props.location}</h1>;
  }
}

Notice the use of capitalization for the component name Greet. The reason for this convention is to distinguish it from a typical DOM tag, such as the <h1> above. These must be distinguished because a component can also be referred to as an element:

const Greet = (props) => {
  return <h1>Hello from {props.location}</h1>;
}

const element = <Greet location="New Orleans" />;

ReactDOM.render(
  element,
  document.getElementById('root')
)

In the example above, element Greet has a property location, which is passed to the component Greet through the props object, the one and only vehicle for data in any relationship of this nature in React. The Greet component formulates the data it receives through props into an html-y element, which ReactDOM renders to the DOM. (The html-y business is thanks to JSX, JavaScript XML, a syntax extension to JavaScript. It's helpful, but required.) This example renders to the page "Hello from New Orleans."

Components can also be referred to by other components, allowing for further abstracting away of the structure:

const Greet = props => {
  return <h1>Hello from {props.location}</h1>;
}

const App = () => {
  return (
    <div>
      <Greet location="Mid-city, New Orleans" />
      <Greet location="Uptown, New Orleans" />
      <Greet location="French Quarter, New Orleans" />
    </div>
  );
}

ReactDOM.render(
  <App />,
  document.getElementById('root')
);

In the example above, component App—a common moniker for the highest level component containing all other components—returns three instances of component Greet, each with a different value. Notice in component App the use of parentheses, another common pattern in ReactJS. The parentheses "collect" all the components to be returned so JavaScript will return multiple lines.

State

So far, all the examples we've looked at have been stateless. This means they have not had to keep track of any data changes or re-render. Let's practice that now by refactoring Greet to initialize state:

class Greet extends React.Component {  
    constructor(props){  
        super(props);  
        this.state = {  
            location: "the West Bank"  
        };  
        this.updateLocation = this.updateLocation.bind(this);   
   }

   updateLocation() {  
        this.setState({  
            location: "the East Bank"  
        });  
    }

    render() {  
        return <h1>Hello from {this.state.location}</h1>;  
    }  
}

Component Greet is now a class with a constructor (notice the call to super like any proper subclass!) which sets state using keyword this. Likewise, the method, updateLocation, is bound with keyword this within the constructor. Inside the method itself is the crucial part that often goes wrong. Here, the setState react method must be used, not the logical presumption of this.state.location = "new location". Finally, the return element is moved inside of the render method, and instead of using props.location, uses this.state.location. (Side note: notice the use of curly braces around this.state.location. These allow the contained code to be interpreted as javascript instead of React, another commonly used tool.)

Finally, let's add a button to click to update the location:

render() {  
  return (  
     <div>  
       <h1>Hello from {this.state.location}</h1>  
       <button onClick={this.updateLocation}>Change location</button>  
     </div>     
  )  
}

And now to tie it together:

class Greet extends React.Component {   
    constructor(props){  
        super(props);  
        this.state = {  
            location: "the West Bank"  
        };  
        this.updateLocation = this.updateLocation.bind(this);   
   }

   updateLocation() {  
        this.setState({  
            location: "the East Bank"  
        });  
    }

  render() {
    return (
      <div>
        <h1>Hello from {this.state.location}</h1>
        <button onClick={this.updateLocation}>Change location</button>
      </div>
    )
  }
}

const App = () => {
  return (
    <div>
      <Greet />
    </div>
  );
}

ReactDOM.render(
  <App />,
  document.getElementById('root')
);

A summary:

Component Greet's location property resides in its state, which can be changed when the button element calls the updateLocation method. (Props are still passed down here from App but are no longer used since the location property now lives in state.)
The current value of location is rendered with the text in the <h1> element.
Component App renders component Greet.
ReactDOM renders App to the page.

Hopefully, breaking this into small components (see what I did there) helps to "see" the patterns and where the dots connect. To be fair, it's never this easy to picture in reality. References are obscured by being located across numerous files and keeping track of them can become dizzying. But the more these structures become second nature, the faster you'll be comfortable diving into a full scale program. Keep at it!

Javascript ES6: "Super" and "Extends"

Grant — Mon, 06 Jul 2020 13:17:20 +0000

Keywords super and extends are integral parts of Javascript's ES6 subclass syntax, but super's role can seem mysterious. To get a better understanding of how they work, let's start with an example of inheritance in pseudoclassical ES6 pattern:

class Bicycle {
  constructor(brand, color) {
    this.brand = brand;
    this.color = color;
  }

  describe() {
    return (`This bicycle is a ${this.color} ${this.brand}.`);
  }

  gearing(chainRings, cogs) {
    this.chainRings = chainRings;
    this.cogs = cogs;
    this.speeds = this.cogs * this.chainRings;
    if (this.speeds > 1) {
      return (`This ${this.brand} is a ${this.speeds}-speed.`);
    }
    if (this.speeds === 1) {
      return (`This ${this.brand} is a single-speed.`);
    }
  }
}

const pug = new Bicycle("Peugeot", "red");
console.log(pug.describe()); /* This bicycle is a 
red Peugeot. */

In the example above, the Bicycle class has a constructor function to create a Bicycle, and a method, gearing, to report its gearing. Note the class keyword, and capitalization of the class name, Bicycle, both of which are important components of this instantiation pattern. A new instance of a Bicycle is created with the keyword new.

Now, on to the subclass business. Let's create a subclass Fixie that takes on Bicycle's properties.

class Fixie extends Bicycle {
    constructor(brand, color) {
      this.chainRings = 1;
      this.cogs = 1;
      this.isFixedGear = true;
    }

    gearing() {
      if (this.isFixedGear === true) {
        return (`This ${this.brand} is a fixed-gear.`);
      }
    }
}

It's easy to see that extends directs subclass Fixie to take on class Bicycle's properties (much like the underscore library's extend method extends an object(s) properties into another). Fixie, however, has its own constructor that adds properties, and it overrides one of its inherited methods, gearing.

What's less apparent here is that this code won't work as written. Attempting to create a new instance of Fixie will throw a ReferenceError Specifically, mine says Must call super constructor in derived class before accessing 'this' or returning from derived constructor—if only all errors were this helpful! We'll need to user super somehow to get things up and running—but how? And why?

According to javascript.info, "In JavaScript, there’s a distinction between a constructor function of an inheriting class (so-called “derived constructor”) and other functions." The problem is a derived constructor called with keyword new does not create an empty object assigned to this, because it "expects the parent constructor to do this job." So all we have to do is call the constructor in super (the parent class)—fair enough. Here's another go at it:


class Fixie extends Bicycle {
    constructor(brand, color) {
      super(brand, color);
      this.chainRings = 1;
      this.cogs = 1;
      this.isFixedGear = true;
    }

    gearing() {
      if (this.isFixedGear === true) {
        return (`This ${this.brand} is a fixed-gear.`);
      }
    }
}


const moto = new Fixie("Motobecane", "blue");
console.log(moto.gearing()); /* This Motobecane is a
fixed-gear. */

The important thing to note is that super must be called before any use of keyword this, or there will be no this to refer to, and an error will be thrown. Now let's look at one more use of super:

class Fixie extends Bicycle {
    constructor(brand, color) {
      super(brand, color);
      this.chainRings = 1;
      this.cogs = 1;
      this.isFixedGear = true;
    }

    gearing() {
      if (this.isFixedGear === true) {
        return (`This ${this.brand} is a fixed-gear.`);
      }
    }

    describe() {
      return `${super.describe()} This bike is a fixie.`
    }
}

console.log(moto.describe()) /* This bicycle is a blue
Motobecane. This bike is a fixie. */

Now we've used a call of the super's describe method while actually overriding describe within the subclass. The new method will return the value of describe's message from the Bicycle class along with more information describing the bike as a fixie.

Keyword super doesn't have to be mysterious! Just remember that it refers to the parent class (thinking "superclass" may help), and that it must be called in the subclass's constructor function to start up a new empty object for keyword this.
Super!

Javascript Function Binding

Grant — Mon, 29 Jun 2020 07:04:58 +0000

Function binding is important because it defines the object on which a function will be called. Consider the example below:

   const whereYat = "Outside";

   const mainObj = {
      whereYat: "Inside",
      find: function() {
         return whereYat;
      }
   };

   const otherObj = {
      whereYat: "Ain’t dere no more."
   };

   mainObj.find(); // “Outside”

There are three locations where something is named whereYat: a variable (const) declared on the global scope, a property of the object named mainObj, and a property of the object named otherObj. The function inside mainObj, when called, will return one of these whereYat values. As written, it will return Outside, since the function call is made on the global scope, where the variable whereYat lives.

Intuitively, the purpose of this code snippet would appear to be different than its current result, but how does one take control of what value function find returns? The first thing our code needs would be keyword this:

  const whereYat = "Outside";

   const mainObj = {
      whereYat: "Inside",
      find: function() {
         return this.whereYat;
      }
   };

   const otherObj = {
      whereYat: "Ain’t dere no more."
   };

   mainObj.find(); // “Inside”

Keyword this can lead to much confusion, but its use is necessary to prescribe exactly which object a function will operate on. The updated example is called “method invocation”, where this refers to the object on which the function (method) is called. A helpful tool is to look at what’s to the left of the dot at call time. This code returns the value of whereYat from inside mainObj. But what if we wanted the function to return the value of whereYat inside otherObj? Passing otherObj as an argument doesn't work because this is still bound to the object on which the method is called (the object to the left of the dot). Therefore, mainObj.find(otherObj) still returns Inside.

The keywords .call(), .apply(), and .bind() are helpful here, as they allow the function call to be applied to a passed-in object:

  const whereYat = "Outside";

   const mainObj = {
      whereYat: "Inside",
      find: function() {
         return this.whereYat;
      }
   };

   const otherObj = {
      whereYat: "Ain’t dere no more."
   };

   mainObj.find.call(otherObj); // "Ain’t dere no more."

In the updated function call, the .call() method links keyword this to the argument object, otherObj. Using .apply() would give the same result here. But what happens if the function call is made with .bind()?

mainObj.find.bind(otherObj); // returns the actual find [function]

The string at otherObj's whereYat key isn't returned because .bind() doesn't actually call the function like .call() (and .apply()). To return the value we're looking for, we would have to call the .bind() method immediately:

mainObj.find.bind(otherObj)(); // "Ain’t dere no more."

This difference may make .bind() appear to be less useful. .bind(), howver, has a special capability .call() and .apply() don't have, which is to permanently "bind" the function to the argument object.

  const whereYat = "Outside";

   const mainObj = {
      whereYat: "Inside",
      find: function() {
         return this.whereYat;
      }
   };

   const otherObj = {
      whereYat: "Ain’t dere no more."
   };

   const bindToOther = mainObj.find.bind(otherObj); 
   bindToOther(); // "Ain’t dere no more."

By setting the .bind() method call to a variable (bindToOther), we've saved the connection between otherObj and the mainObj.find method. We can call this configuration anytime we want using the bindToOther variable name. For example, let's say we use .call() again to call the function on mainObj, returning Inside:

   mainObj.find.call(mainObj); // “Inside”

We can go right back to the bindToOther() variable to once again return the result of calling the find method on otherObj:

   bindToOther(); // "Ain’t dere no more."

This method of remembering specific function calls is useful not only in reducing code repetition, but also in reducing bugs that result from unintended function binding.