Skip to main content

Prelab 6: Databases

due March 8th at 10:00am ET



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


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 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 = O.customer_id;

Another way to do it, would be to use a JOIN command as follows:

SELECT * FROM customer JOIN orders ON = 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.address,, 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 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: "",
    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: "",
    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


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.


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"


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 >

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 =
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);
    } finally {
    // Ensures that the client will close when you finish/error
    await client.close();


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) => {


Clone the stencil repository (Github Classroom link)

If you are going with mongoDB for this task, in mongo/mongodb.js:

If you are going with SQL for this task, in sql/sql.js:

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

  • To hand in your code for prelab 6, commit and push your changes to your cloned GitHub Classroom repository's main branch.
  • Submit it to Gradescope via GitHub upload. You must submit your prelab to Gradescope or we'll not be able to grade your submission.