Lecture 5 NoSQL database PDF

Lecture 5 NoSQL database EIE4432 WEB SYSTEMS AND TECHNOLOGIES Dr. Pauli Lai 1 RDBMS ▪ Stands for “Relational Database Management System” ▪ Enforced structured data model based on tables with predefined schemas ▪ Each table in a database schema has a unique name and defines the structure of the d...

Lecture 5 NoSQL database EIE4432 WEB SYSTEMS AND TECHNOLOGIES Dr. Pauli Lai 1 RDBMS ▪ Stands for “Relational Database Management System” ▪ Enforced structured data model based on tables with predefined schemas ▪ Each table in a database schema has a unique name and defines the structure of the data it stores, including: ▪ Data types of the data columns ▪ Relationships between tables ▪ Constraints ▪ Primary keys ▪ Foreign keys 2 RDBMS This is a many-to-many relationship between Student and Book! Consider two tables named Student and BookBorrow, Reference to Book Table Foreign key relationships Row # student_id name age sex … # student_id book_id borrow_time … 1 xxxx0001 Alice 18 F … 1 xxxx0001 yyyy0080 20230305T10:15 … 2 xxxx0004 Bob 19 M … 2 xxxx0001 yyyy0081 20230505T16:10 … 3 xxxx0003 Janice 17 F … 3 xxxx0001 yyyy0082 20230710T16:05 … … … … … … … … … … … … Column Student Table BookBorrow Table 3 What is the problem of RDBMS? ▪ Fixed schema ▪ Need to alter the table structure if you changed the schema → complex and timeconsuming ▪ Cannot have dynamic fields on demand, i.e., add a field for specific records. ▪ Rely on vertical scaling ▪ Need to scale up the single server when data size grows ▪ Not designed to be distributed ▪ Use centralized architecture Scale up (Vertical scaling) • Add more resource to a single server • Expensive Scale out (Horizontal scaling) • Add more servers • Distribute the workload to different servers 4 NoSQL ▪ Often interpreted as “Not Only SQL” ▪ A class of schema-less database system ▪ Allows dynamic data structures ▪ Horizontal scalability ▪ Distribute data across multiple servers to handle large volumes of data ▪Support different types of data models 5 NoSQL Types Key-value stores Graph database Document-oriented Column family Document Object Example Note that books is an array Add any numbers of borrowed books Add any kind of borrowed items Add any new fields on demand BookBorrow Table Student Table A simple document object represents a student # student_id name age sex … 1 xxxx0001 Alice 18 F … 2 xxxx0004 Bob 19 M … 3 xxxx0003 Janice 17 F … … … … … … … # student_id book_id borrow_time … 1 xxxx0001 yyyy0080 20230305T10:15 … 2 xxxx0001 yyyy0081 20230505T16:10 … 3 xxxx0001 yyyy0082 20230710T16:05 … … … … … … 7 SQL vs NoSQL ✓ NoSQL database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases. SQL NoSQL Relational Database Management System (RDBMS) Non-relational or distributed database system. These databases have fixed or static or predefined schema They have dynamic schema These databases are best suited for complex queries These databases are not so good for complex queries Vertically Scalable Horizontally scalable MongoDB References: https://www.tutorialspoint.com/mongodb/index.htm https://www.w3schools.com/mongodb/index.php 9 What is MongoDB? ✓ MongoDB is an open source, document-oriented database designed with both scalability and developer agility in mind. ✓ Instead of storing your data in tables and rows as you would with a relational database, in MongoDB you store JSON-like documents with dynamic schemas (schema-free, schema-less). { "_id" : ObjectId("5114e0bd42…"), “FirstName" : "John", “LastName" : “Chan", “Age" : 39, “Interests" : [ "Reading", "Mountain Biking ] “Favorites": { "color": "Blue", "sport": "Soccer“ } } MongoDB is Easy to Use Knowledge-check exercise: Please pick a person, other than Paul Miller, from the relationship DB and write down the MongoDB Document for this person. You may ignore the location attribute. Scheme-Free RDBMS vs MongoDB MongoDB does not need any pre-defined data schema Every document could have different data! {name: “will”, eyes: “blue”, birthplace: “NY”, aliases: [“bill”, “ben”], loc: [32.7, 63.4], boss: ”ben”} {name: “jeff”, eyes: “blue”, loc: [40.7, 73.4], boss: “ben”} {name: “ben”, age:25} {name: “brendan”, boss: “will”} {name: “matt”, weight:60, height: 72, loc: [44.6, 71.3]} RDBMS MongoDB Database Database Table Collection Row/Tuple/Record Document (JSON, BSON) Column Field Index Index Table Join Embedded Document Primary Key Primary Key (_id, can be auto-generated or custom) Features Of MongoDB ▪ Document-oriented database ▪ Built-in horizontal scalability ▪ High performance and indexing ▪ Rich queries and data aggregations ▪ Integration with vary application frameworks, i.e., MongoDB Node Drivers ▪Large ecosystem and community 13 Why MongoDB? https://db-engines.com/en/ranking 14 Local vs Cloud Database MongoDB can be installed locally, which will allow you to host your own MongoDB server on your hardware. This requires you to manage your server, upgrades, and any other maintenance. You can download and use the MongoDB open source Community Server on your hardware for free. However, for this course we are going to use MongoDB Atlas, a cloud database platform. This is much easier than hosting your own local database. To be able to experiment with the code examples, you will need access to a MongoDB database. Sign up for a free MongoDB Atlas account to get started. 15 Creating a Cluster to Deploy Database To deploy your database, set up a free "Shared Cluster" then choose your preferred cloud provider and region. By default, MongoDB Atlas is completely locked down and has no external access. You will need to set up a user and add your IP address to the list of allowed IP addresses. Under "Database Access", create a new user and keep track of the username and password. Next, under "Network Access", add your current IP address to allow access from your computer. Or you may set up a user and add your IP address to the list of allowed IP addresses during creation of a database. 16 Deploy your database I N T HE M ON G ODB AT L A S DA S HBOA R D, CL I CK " DATABASE“ - > “BUI LD A DATA BA SE”. 17 Install MongoDB Shell (mongosh) There are many ways to connect to your MongoDB database. We will start by using the MongoDB Shell, mongosh. Use the official instructions to install mongosh on your operating system. https://www.mongodb.com/docs/mongodb-shell/install/ https://www.mongodb.com/try/download/shell To verify that it has been installed properly, open your terminal and type: (You may need to add the mongosh binary to your PATH environment variable first) You should see that the latest version is installed. The version used in this tutorial is v2.0.1. 18 Add the mongosh binary to your PATH environment variable 19 Connect to the database To connect to your database, you will need your database-specific connection string. In the MongoDB Atlas dashboard, under "Database", click the "Connect" button for your Cluster. Next, choose "Connect with the MongoDB Shell". Copy your connection string. 20 Connect to the database Paste your connection string into your terminal and press enter. You will be prompted to enter your database user password that you created earlier. You are now connected to the database! 21 MongoDB CRUD Operations CRUD operations refer to create, read, update, and delete documents. ▪ Create ▪ Create or insert operations add new documents to a collection ▪ Read ▪ Retrieve documents from a collection ▪ Update ▪ Modify existing documents in a collection ▪ Delete ▪ Remove documents from a collection ⓘ Reminder Document is the basic unit of data, and each write operations apply to a collection atomically. 22 MongoDB mongosh Create Database See which database you are using After connecting to your database using mongosh, you can see which database you are using by typing db in your terminal. If you have used the connection string provided from the MongoDB Atlas dashboard, you should be connected to the test database. Show all databases To see all available databases, in your terminal type show dbs. Notice that test is not listed. This is because the database is empty. An empty database is essentially nonexistant. 23 MongoDB mongosh Create Database Change or Create a Database You can change or create a new database by typing use then the name of the database. We are now in the blog database. Remember: In MongoDB, a database is not actually created until it gets content! 24 MongoDB mongosh Create Collection There are 2 ways to create a collection. Method 1 You can create a collection using the createCollection() database method. Remember: In MongoDB, a collection is not actually created until it gets content! Method 2 You can also create a collection during the insert process. Example We are here assuming object is a valid JavaScript object containing post data: db.posts.insertOne(object) This will create the "posts" collection if it does not already exist. 25 Create documents ▪ insertOne() and insertMany() method is used to insert a new document and multiple documents at once respectively ▪ Each document is a JSON object ▪ MongoDB will add the "_id" field to the document if not specified ▪ If the "_id" is specified, it must be unique within the collection Add a new student db. students.insertOne({ "_id": "xxxx0001", "name": "Alice", "age": 18, … }) Add multiple students db. students.insertMany([{ "_id": "xxxx0004", "name": "Bob", Note that it is an array "age": 19, … }, …]) 26 MongoDB mongosh Insert documents There are 2 methods to insert documents into a MongoDB database: insertOne() and insertMany() insertOne() To insert a single document, use the insertOne() method. This method inserts a single object into the database. Note: When typing in the shell, after opening an object with curly braces "{" you can press enter to start a new line in the editor without executing the command. The command will execute when you press enter after closing the braces. db.posts.insertOne({ title: "Post Title 1", body: "Body of post.", category: "News", likes: 1, tags: ["news", "events"], date: Date() }) Note: If you try to insert documents into a collection that does not exist, MongoDB will create the collection automatically. 27 MongoDB mongosh Insert db.posts.insertMany([ { title: "Post Title 2", body: "Body of post.", category: "Event", likes: 2, To insert multiple documents at once, tags: ["news", "events"], use the insertMany() method. date: Date() This method inserts an array of objects }, into the database. { title: "Post Title 3", body: "Body of post.", category: "Technology", likes: 3, tags: ["news", "events"], date: Date() }, { title: "Post Title 4", body: "Body of post.", category: "Event", likes: 4, tags: ["news", "events"], date: Date() } ]) insertMany() Note that it is an array 28 MongoDB mongosh Find Find Data There are 2 methods to find and select data from a MongoDB collection, find() and findOne(). find() To select data from a collection in MongoDB, we can use the find() method. This method accepts a query object. If left empty, all documents will be returned. 29 MongoDB mongosh Find findOne() To select only one document, we can use the findOne() method. This method accepts a query object. If left empty, it will return the first document it finds. Note: This method only returns the first match it finds. 30 MongoDB mongosh Find Querying Data To query, or filter, data we can include a query in our find() or findOne() methods. 31 MongoDB mongosh Find Projection Both find methods accept a second parameter called projection. This parameter is an object that describes which fields to include in the results. Note: This parameter is optional. If omitted, all fields will be included in the results. This example will only display the title and date fields in the results. Notice that the _id field is also included. This field is always included unless specifically excluded. 32 MongoDB mongosh Find We use a 1 to include a field and 0 to exclude a field. This time, let's exclude the _id field. Note: You cannot use both 0 and 1 in the same object. The only exception is the _id field. You should either specify the fields you would like to include or the fields you would like to exclude. 33 MongoDB mongosh Find Let's exclude the category field. All other fields will be included in the results. 34 MongoDB mongosh Find We will get an error if we try to specify both 0 and 1 in the same object. Note: You cannot use both 0 and 1 in the same object. The only exception is the _id field. You should either specify the fields you would like to include or the fields you would like to exclude. 35 MongoDB mongosh Update Update Document To update an existing document we can use the updateOne() or updateMany() methods. The first parameter is a query object to define which document or documents should be updated. The second parameter is an object defining the updated data. updateOne() The updateOne() method will update the first document that is found matching the provided query. Let's update the "likes" on this post to 2. To do this, we need to use the $set operator. 36 MongoDB mongosh Update Insert if not found If you would like to insert the document if it is not found, you can use the upsert option. Update the document, but if not found insert it: 37 MongoDB mongosh Update updateMany() The updateMany() method will update all documents that match the provided query. Increment likes on all documents by 1. For this we will use the $inc (increment) operator: Use db.posts.find() to check the likes in all of the documents before and after the update. You will see that they have all been incremented by 1. 38 MongoDB mongosh Delete Delete Documents We can delete documents by using the methods deleteOne() or deleteMany(). These methods accept a query object. The matching documents will be deleted. deleteOne() The deleteOne() method will delete the first document that matches the query provided. 39 MongoDB mongosh Delete deleteMany() The deleteMany() method will delete all documents that match the query provided. 40 MongoDB mongosh Drop drop() The drop() method will remove a collection or view from the database. The following operation drops the posts2 collection in the current database. 41 Basic query ▪ MongoDB Query Language (MQL) is used for querying and manipulating data ▪ Used to specify query filters or criteria to identify the documents to return or modify ▪ find() and findOne() method can be used to query a collection ▪ findOne will only return the first document that matches the query criteria Select students collection Example query db. students.find({ Query criteria → age > 18 AND sex == "F" age and sex are the fields of the documents "age": {"$gt": 18}, "sex": "F" in the collection. }) ⓘ Reminder Call find() or findOne() without argument implies matching all $gt is query selector matching value greater than specific value, i.e., age > 18 42 Basic query ▪ MQL is based on a JSON-like syntax, so it provides a familiar and readable way to express these queries as a Javascript object. ▪ Valid query expression formats ▪ { "key": value, …} where value is an exact value ▪ E.g. {"sex": "F“} ▪ { "key": { "$selector": expression, …}, …} ▪ E.g. {"age": {"$gt": 18}, "sex": "F"} Surrounded by braces { … } ▪ { "$selector": expression, … } ▪ E.g. {"$not": {"age": {"$gt": 12}}} ▪ { "$selector": [expression, …], … } Some selectors requires an array […] ▪ e.g. {"$in": ["Alice", "Bob", "Joey"]}} ▪ Empty {} means matching all documents ▪ Each key-value pair in the query must be satisfied 43 Basic query ▪ MQL support a variety of query selectors ▪ Comparison ($lt, $ge, $eq, $in, …) ⓘ Note Selectors that need multiple values input use array […], like $in, $and, $or. ▪ Example: {"age": {"$gt": 18 }} → age is greater than 18 ▪ Example: {"name": {"$in": ["Alice", "Bob", "Joey"]}} → name is either Alice, Bob or Joey ▪ Logical ($or, $and, …) ▪ Example: {"$or": [{"age": {"$gt": 12}}, {granted: true}]} → age greater than 12 OR is granted ▪ Example: {"$not": {"age": {"$gt": 12}}} → IS NOT age greater than 12 ▪ Array ($all, $elemMatch) ▪ Element ($exist) ▪ and more …. https://www.mongodb.com/docs/v7.0/reference/operator/query 44 Basic query { "key": { "$selector": expression, …}, …} ▪ A single expression can contain multiple conditions, which implies the AND operation ▪ All conditions in the query must be satisfied ▪ A common example like { "x": { "$gt": 0, "$lt: 10}} ▪ Matches field x with a value greater than 0 AND less than 10 ▪ This characteristic only applies to query on values. ▪Query on array has a different way of handling 45 MongoDB Query Operators There are many query operators that can be used to compare and reference document fields. https://www.mongodb.com/docs/v7.0/reference/operator/query/ Comparison Logical The following operators can be used in queries to compare values: •$eq: Values are equal •$ne: Values are not equal •$gt: Value is greater than another value •$gte: Value is greater than or equal to another value •$lt: Value is less than another value •$lte: Value is less than or equal to another value •$in: Value is matched within an array Evaluation The following operators can logically compare multiple queries. •$and: Returns documents where both queries match •$or: Returns documents where either query matches •$nor: Returns documents where both queries fail to match •$not: Returns documents where the query does not match The following operators assist in evaluating documents. •$regex: Allows the use of regular expressions when evaluating field values •$text: Performs a text search •$where: Uses a JavaScript expression to match documents 46 MongoDB Update Operators There are many update operators that can be used during document updates. Fields The following operators can be used to update fields: •$currentDate: Sets the field value to the current date •$inc: Increments the field value •$rename: Renames the field •$set: Sets the value of a field •$unset: Removes the field from the document Array The following operators assist with updating arrays. •$addToSet: Adds distinct elements to an array •$pop: Removes the first or last element of an array •$pull: Removes all elements from an array that match the query •$push: Adds an element to an array 47 MongoDB Drivers The MongoDB Shell (mongosh) is great, but generally you will need to use MongoDB in your application. To do this, MongoDB has many language drivers. The language drivers allow you to interact with your MongoDB database using the methods you've learned so far in `mongosh` but directly in your application. These are the current officially supported drivers: •C •C++ •C# •Go •Java •Node.js •PHP •Python •Ruby •Rust •Scala •Swift 48 MongoDB Node.js Database Interaction Sample Data For this tutorial, we will use a MongoDB Atlas database. We will load sample data into our database. From the MongoDB Atlas dashboard, go to Database. Click the ellipsis “…” and select "Load Sample Dataset". This will load several sample datasets into your database. (It might take several minutes. Please be patient.) We will use the "sample_mflix" database loaded from our sample data. 49 MongoDB Node.js Database Interaction We will also use the "sample_mflix" database loaded from our sample data. 50 MongoDB Node.js Driver Installation To use MongoDB with Node.js, you will need to install the mongodb package in your Node.js project. Use the following command in your terminal to install the mongodb package: We can now use this package to connect to a MongoDB database. Create an index.js file in your project directory. import { MongoClient } from 'mongodb'; 51 Connection String In order to connect to our MongoDB Atlas database, we'll need to get our connection string from the Atlas dashboard. Go to Database then click the CONNECT button on your Cluster. Choose MongoDB for VS Code then copy your connection string. Example: mongodb+srv://<username>:<password>@<cluster.string>.mongodb.net/ You will need to replace the <username>, <password>, and <cluster.string> with your MongoDB Atlas username, password, and cluster string. 52 Connecting to MongoDB import { MongoClient } from 'mongodb'; const uri = "mongodb+srv://<username>:<password>@<cluster.string>.mongodb.net/"; Let's add to our index.js file. const client = new MongoClient(uri); async function run() { try { await client.connect(); const db = client.db('sample_mflix'); const collection = db.collection('movies'); // Find the first document in the collection const first = await collection.findOne(); console.log(first); } finally { // Close the database connection when finished or an error occurs await client.close(); } } run().catch(console.error); 53 Result Run this file in your terminal. > node index.js You should see the first document logged to the console. 54 CRUD Just as we did using mongosh, we can use the MongoDB Node.js language driver to create, read, update, and delete in the database. Expanding on the previous example, we can replace the collection.findOne() with find(), insertOne(), insertMany(), updat eOne(), updateMany(), deleteOne(), or deleteMany(). Give some of those a try. 55