Setup
- Accept this Github Classroom assignment and clone this repo that contains stencil code for this prelab.
- NOTE: If you are doing the MySQL portion of this prelab, you will have to connect to the Brown VPN (if you are not already on the Brown network). Instructions are listed here: Brown VPN
Introduction
In this prelab you will be getting familiar with MongoDB and MySQL. The prelab only requires you to solve the problems related to only one of the types of databases (i.e. only complete the tasks related to mongoDB OR SQL).
To interface with mongoDB you will be using mongoDB.js and for using SQL with node.js you'll be using mysql2
SQL
Structured Query Language is a language to speak to databases.
A
database
is a collection of
tables
. Each
table
has a number of
rows
and each
row
has a number of
columns
.
Rows
usually represent an entry, and
columns
are one aspect of that entry. For example, we might have a table named
people
with columns for
first name
,
last name
, and
age
. Columns usually have types like
text
or
integer
to indicate what sort of data they are.
Databases are complicated and you won't have to implement one -- but you will have to leverage one. There are many SQL databases such as SQLite, PostgreSQL, and what you'll be using today: MySQL.
In this prelab we will go over some SQL syntax and then how to connect to a MySQL database with Node. If you would like to follow along feel free to use the local setup guide for MySQL to create a MySQL database locally and use the MySQL console.
Creating Tables
CREATE TABLE
does exactly what it sounds like. It creates a new table in the database given a specification of the columns the table should contain. This specification is a
Schema
and a newly created table has 0 rows.
If we want to create a table storing people we can run the following command in your MySQL interpreter:
CREATE TABLE people(id INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, age INTEGER);
The database doesn't say much in response to this command unless there's an error. For example, asking the database to
CREATE TABLE
with an existing table name is an error!
Note how we specified the data types for each column when creating the table.
Also, note that we specified that every person has an
id
field, and that this id is a
Primary Key
. SQL tables have a notion of keys that are meant to uniquely define an entry and give a quick lookup to the entire entry. In this case every person would need to have a unique id field, which you can specify or MySQL can generate for you. For more information on MySQL data types you can look
here
.
Inserting into Tables
INSERT INTO
is also pretty straightforward: it is meant to insert a row into an existing table. You'll give it the name of the table as well as the values for each column and it creates a new row.
We could add a new person to people like this:
INSERT INTO people VALUES(1, 'Jackson', 'Owens', 20);
INSERT INTO people VALUES(2, 'Mr', 'Penguin', 21);
Again, the database doesn't say much in response to an insertion, unless there is an error.
Additionally, you can perform batch inserts , allowing you to put multiple values in a table at once.
INSERT INTO people VALUES(1, 'Jackson', 'Owens', 20),(2, 'Mr', 'Penguin', 21);
Selecting from Tables
SELECT
is a little trickier, but a good metaphor might be
find me rows
. It takes three components:
- a what (a comma separated list of columns, or * matching all of the columns)
- a table
-
an optional filter clause called
WHERE
It's clearer when it's written out:
SELECT lastname, firstname FROM people WHERE age = 20;
The database would return the last and first names of all the people in the table who have an age of 20. We could fetch the whole row instead:
SELECT * FROM people WHERE age = 20;
Unlike
CREATE TABLE
and
INSERT INTO
, the database's response to a
SELECT
is important! It will be the columns we asked for from the rows that matched our
SELECT
criteria.
Joins and All that Jazz
When using a SQL database you may have multiple tables and you may want to relate the contents of multiple tables. For example, suppose we wanted to record customer orders from a restaurant. A way to model this would be too have two tables customer and orders with the following schemas and example data:
CREATE TABLE customer(id INTEGER PRIMARY KEY, name TEXT, address TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, FOREIGN KEY (customer_id) REFERENCES customer(id));
INSERT INTO customer VALUES(1, "person1", "address1");
INSERT INTO customer VALUES(2, "person2", "address2");
INSERT INTO orders VALUES(1000, 1);
INSERT INTO orders VALUES(1001, 1);
INSERT INTO orders VALUES(1002, 2);
INSERT INTO orders VALUES(1003, 1);
Looking at this schema we can see that we have given every
customer
and every
order
a unique integer id as their
primary key
so we can uniquely identify them. Furthermore, we have specified that
customer_id
in the orders table is
foreign key
(a primary key of another table) and that it references the
customer
id
. So, every
order
has a reference to a
customer
.
If we wanted to see a specific users order history, we would need to then combine these tables. The way to do this is:
SELECT * FROM customers C, orders O WHERE C.id = O.customer_id;
Another way to do it, would be to use a JOIN
command as follows:
SELECT * FROM customer JOIN orders ON customer.id = orders.customer_id;
The way a
JOIN
command works is by specifying two tables to combine, in this case
customer
and
orders
, then what fields to
JOIN
them on. What this will do is essentially combine the rows of the customers table with the rows of the order table, only when they have the same customer id. So our output would look like:
// format of output: customer.id, customer.name, customer.address, orders.id, order.customer_id
1|person1|address 1|1000|1
1|person1|address 1|1001|1
2|person2|address 2|1002|2
1|person1|address 1|1003|1
Joins can be very difficult to understand and can quickly grow messy, but they are very useful when combining seperate data in SQL tables. For more information and better explanations of what is happening I would recommend reading more about JOINS in the links listed below.
Other SQL features
SQL has many other features and there may be other queries that you may want to ask about the database. However, we will not go over all these features in this pre-lab. If you are intrested in more features in mySQL, you can try reading the documentation to find information about Functions , Aggregate Functions , and the reference manual for more information.
For more comprehensive and in depth tutorials I would suggest visiting tutorialspoint.com which has extensive materials on SQL. Furthermore, if you are interested in the internals of relational databases you may want to consider taking CS1270: Database Mangement Systems .
MySQL and Node.js
Now that we’ve gone over some basic syntax we will go over how to interact with MySQL database in NodeJS. In the prelab stencil, you will find that we are using mysql2 to interact with the SQL database.
If you have any further questions about the usage of mysql2 node package, feel free to check out the documentation for mysql2
Connecting to the database
There are several methods to connect to a MySQL database using mysql2 module. The first method uses the
createConnection
const mysql = require("mysql2");
const conn = mysql.createConnection({
host: "example@host.com",
user: "username_here",
password: "password_here",
database: "database_name_here",
});
});
The above code creates a connection pool which manages handing out connections.
Creating a Connection/Pool
We can either create a connection or a pool to execute our queries on
The following piece of code creates a connection pool
const conn = mysql.createPool({
host: "example@host.com",
user: "username_here",
password: "password_here",
database: "database_name_here",
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
});
Executing a query
After establishing a connection with the database, it is simple to execute a MySQL query by using a connection from the pool to make queries using
pool.query(query,callback)
. In the prelab stencil, we use the connection to establish queries.
// For pool initialization, see above
pool.query("SELECT ...", [..], function(err, res, fields) {
// Connection is automatically released when query resolves
if (err) throw error;
// res is the rows returned from server, so you may want to work with that here
});
Mongo
MongoDB is an open-source document database that provides high performance, high availability, and automatic scaling. MongoDB removes the need for an Object Relational Mapping (ORM) to facilitate development.
Documents
A record in MongoDB is known as a document, which is a data structure composed of field and value pairs. MongoDB documents are similar to JSON objects . The values of fields may include other documents, arrays, and arrays of documents. The following example is one such document, with nested fields and various data types.
{
"_id" : ObjectId("54c955492b7c8eb21818bd09"),
"address" : {
"street" : "2 Avenue",
"zipcode" : "10075",
"building" : "1480",
"coord" : [ -73.9557413, 40.7720266 ]
},
"borough" : "Manhattan",
"cuisine" : "Italian",
"grades" : [
{
"date" : ISODate("2014-10-01T00:00:00Z"),
"grade" : "A",
"score" : 11
},
{
"date" : ISODate("2014-01-16T00:00:00Z"),
"grade" : "B",
"score" : 17
}
],
"name" : "Vella",
"restaurant_id" : "41704620"
}
Collections
MongoDB stores documents in collections. Collections are analogous to tables in relational databases. Unlike a table, however, a collection does not require its documents to have the same schema.
In MongoDB, documents stored in a collection must have a
unique_id
field that acts as a primary key.
MongoDB and Node.js
You'll be working with MongoDB through mongodb. MongoDB is a Node.js library that provides MongoDB support.
Object Relational Mapping (ORM), or Object Data Mapping (ODM) in the case of Mongoose, means that Mongoose translates data in the database to JavaScript objects for use in your application.
Let's take a look at creating and storing documents in a collection using MongoDB and Mongoose.
Connecting to MongoDB
We have created a database for you at mongodb://< cslogin >@bdognom-vw.cs.brown.edu/animals
This has already been filled in for you in the stencil code. Just replace cslogin with you CS department login. Sample code is listed for your reference below:
const { MongoClient } = require("mongodb");
// Replace the uri string with your MongoDB deployment's connection string.
const uri =
"mongodb+srv://:@?retryWrites=true&writeConcern=majority";
const client = new MongoClient(uri);
async function run() {
try {
await client.connect();
const database = client.db('sample_mflix');
const movies = database.collection('movies');
// Query for a movie that has the title 'Back to the Future'
const query = { title: 'Back to the Future' };
const movie = await movies.findOne(query);
console.log(movie);
} finally {
// Ensures that the client will close when you finish/error
await client.close();
}
}
run().catch(console.dir);
Insertions with MongoDB
We can either use the insertOne or the insertMany methods in order to perform the insertions. Sample code to insert many documents at once using mongoDB.js is listed below:
const database = client.db("sample_mflix");
const movies = database.collection("movies");
// create an array of documents to insert
const docs = [
{ name: "Red", town: "Kanto" },
{ name: "Blue", town: "Kanto" },
{ name: "Leon", town: "Galar" }
];
// this option prevents additional documents from being inserted if one fails
const options = { ordered: true };
const result = await movies.insertMany(docs, options);
Retrieval Operations with MongoDB
We can use the find
or the findOne
methods to retrieve data that is associated with any collection
In the following example, we use find to get the movies that were shot in Kanto
const query = {town: "Kanto"}
const cursor = movies.find(query, {
sort: {name: 1}, // additionally also sort the result by name of the movie (A-Z)
});
cursor.forEach((movie) => {
console.log(movie);
})
Tasks
Clone the stencil repository (Github Classroom link)
If you are going with mongoDB for this task, in mongo/mongodb.js
:
- Run
npm install
in the mongo/ directory - Modify
cslogin
with your CS department login andbannerid
with your Banner ID (the one with the B) - Create any new collection in the database
- Add 3 new document to this new collection
- Use the find method to get the
first
and the third elements in this new collection - You can run your program as such:
npm start
If you are going with SQL for this task, in sql/sql.js
:
- First connect to the Brown VPN for these tasks. Instructions here: Brown VPN
- Run
npm install
in the sql/ directory - Modify
cslogin
with your CS department login - Create 3 new animals and insert them into the animalTable (total of 4 including the provided example in the stencil)
- Then use a
SELECT
query to retrieve the 2nd and the 4th elements in this new table - You can run your code as such:
npm start
Side Note: MySQL requires some additional security parameters which is why we ask you to connect to Brown's VPN. You only need to do this for MySQL and not MongoDB
Last Note
This is the end of the prelab! We have only covered the basics for Databases so if you're confused about anything, feel free to look at official documentations, come to TA Hours!
Handin Instructions
main
branch.