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;
                    

                    
// 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

const mysql = require("mysql2");

const conn = mysql.createConnection({
    host: "example@host.com",
    user: "username_here",
    password: "password_here",
    database: "database_name_here",
    });
});

                    

                            
const conn = mysql.createPool({
    host: "example@host.com",
    user: "username_here",
    password: "password_here",
    database: "database_name_here",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
    });
});
                            
                        

                            
// 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
});
                    

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);
})