From SQL to NoSQL: A Gentle Introduction For Developers

When you need to model data, is your first instinct to start breaking it down into rows and columns? Mine used to be too. When you want to develop apps in a modern, agile way, NoSQL databases can be the best option. Come to this talk to learn how to take advantage of all that NoSQL databases have to offer and discover the benefits of changing your mindset from the legacy, tabular way of modeling data. We’ll compare and contrast the terms and concepts in SQL databases and MongoDB, explain the benefits of using MongoDB compared to SQL databases, and walk through data modeling basics so you feel confident as you begin using MongoDB.

Transcript

1. @JoeKarlsson1 From SQL to NoSQL: Changing your Mindset @JoeKarlsson1 Joe Karlsson
2. Did you know that the two main bottlenecks for web app performance are: @JoeKarlsson1 Source: https://medium.com/@addyosmani/the-cost-of-javascript-in-2018-7d8950fbb5d4 * Network speed * Database execution speed
3. We can only control one of these things @JoeKarlsson1 Source: https://medium.com/@addyosmani/the-cost-of-javascript-in-2018-7d8950fbb5d4
4. @JoeKarlsson1
5. { name: “Joe Karlsson”, company: “MongoDB”, title: [ “Developer Advocate”, “Software Engineer” ], } twitter: “@JoeKarlsson1”, twitch: “joe_karlsson”, tiktok: “joekarlsson”, website: “joekarlsson.com”, links: “http://bit.ly/fromSQLToNoSQL”
6. Before we get started… @JoeKarlsson1
7. Thank you so much for coming! @JoeKarlsson1
8. Who’s this talk for? @JoeKarlsson1
9. @JoeKarlsson1
10. A quick audience poll
11. SQL?
12. MongoDB?
13. Want to follow along? @JoeKarlsson1 http://bit.ly/fromSQLToNoSQL
14. @JoeKarlsson1
15. Agenda @JoeKarlsson1
17. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
18. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
19. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
20. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
21. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
22. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
23. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
24. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
25. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] }
26. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } Users
27. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } Professions Users
28. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } Cars Professions Users
29. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } Cars Professions Users
30. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", } Users Users
31. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", } Users Users
32. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", } Users Users
33. Flexible schema database
34. ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ... a: “b” ... }
35. ID a ... 1 b ... 2 c ... 3 ... ... { ... a: “b” ... } { ... a: “c” ... }
36. { ... } ... ... ... ... ... ... ... ... ... ... ... ... { ... } { ... }
38. But immediately leaves because they couldn’t find table.
40. { ... } { ... } { ... } { ... } { ... } { ... } { ... }
41. { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
42. { ... a: “b”, ... c: { d: “e” ... }, ... } ID a ... 1 b ... 2 ... ... 3 ... ... ... d ... 1 e ... ... ... ...
43. { ... } { ... } { ... } { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
44. @JoeKarlsson1
45. @JoeKarlsson1
46. Agenda @JoeKarlsson1
48. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], } Users Professions
49. import pymongo from pymongo import MongoClient # CONNECT TO THE DB client = MongoClient() client = pymongo.MongoClient("mongodb+srv:// username:password@cluster0nsdia. mongodb.net/test?retryWrites=true") db = client.fabapp # THE ID OF THE USER WHOSE PROFILE WE WILL BE RETRIEVING AND UPDATING userId = 1 import mysql.connector # CONNECT TO THE DB mydb = mysql.connector.connect( host="localhost", user=”username", passwd=”password", database=”fabapp” ) mycursor = mydb.cursor() # THE ID OF THE USER WHOSE PROFILE WE WILL BE RETRIEVING AND UPDATING userId = 1
50. # GET THE USER'S PROFILE INFORMATION ### Pull the info from the Users table & put it in the user dictionary sql = "Select * FROM Users WHERE Users.ID=%s” values = (userId,) mycursor.execute(sql, values) result = mycursor.fetchone() user = { "first_name": result[1], "surname": result[2], "cell": result[3], "city": result[4], "location_x": result[5], "location_y": result[6] } # GET THE USER'S PROFILE INFORMATION ## We can pull all of the info from the same document since we used embedding user = db['Users'].find_one({"_id": userId}) ### Pull the info from the Professions table & put it in the user dictionary sql = "Select * FROM Professions WHERE Professions.user_id=%s” values = (userId,) mycursor.execute(sql, values) results = mycursor.fetchall() professions = [] for result in results: professions.append(result[2]) user["professions"] = professions
51. # UPDATE THE USER DICTIONARY BASED ON USER INPUT IN THE APP ### We'll update the user dictionary manually for simplicity user = { "first_name": "NewFirst", "surname": "NewSurname", "cell": "123-456-7890", "city": "NewCity", "location": [40.762, -73.979], "professions": [”Manager", "Engineer"] } # UPDATE THE USER DICTIONARY BASED ON USER INPUT IN THE APP ### We'll update the user dictionary manually for simplicity user = { "first_name": "NewFirst", "surname": "NewSurname", "cell": "123-456-7890", "city": "NewCity", "location_x": 40.762, "location_y": 73.979, "professions": [”Manager", "Engineer"] }
52. # UPDATE THE USER'S PROFILE IN THE DATABASE ### First update what is stored in the Users table sql = "UPDATE Users SET first_name=%s, surname=%s, cell=%s, city=%s, location_x=%s, location_y=%s WHERE (ID=%s)" values = ( user["first_name"], user["surname"], user["cell"], user["city"], user["location_x"], user["location_y"], userId) mycursor.execute(sql, values) mydb.commit() # UPDATE THE USER'S PROFILE IN THE DATABASE ### Since the user's data is stored in a single document, we only have to make one update result = db['Users'].update_one( {"_id": userId}, {"$set": user}) ### Delete existing records in Professions table and add new ones sql = "DELETE FROM Professions WHERE user_id=%s” values = (userId,) mycursor.execute(sql, values) mydb.commit() if(len(user["professions"]) > 0): sql = "INSERT INTO Professions (user_id,profession) VALUES (%s, %s)” values = [] for profession in user["professions"]: values.append((userId, profession)) mycursor.executemany(sql,values) mydb.commit()
53. 30 lines of code 73 lines of code
54. { a: “b”, c: “one”, e: “f” } { a: “b”, c: 1, new: “no biggee” } ALTER TABLE `mydb`.`letters_table` DROP COLUMN `e`, ADD COLUMN `New` VARCHAR(45) NULL AFTER `C`, CHANGE COLUMN `C` `C` INT NULL DEFAULT NULL ;
55. ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ... a: “b”, ... c: { d: “e” ... }, ... }
57. @JoeKarlsson1
58. @JoeKarlsson1 # UPDATE THE USER'S PROFILE IN THE DATABASE ### First update what is stored in the Users table sql = "UPDATE Users SET first_name=%s, surname=%s, cell=%s, city=%s, location_x=%s, location_y=%s WHERE (ID=%s)" values = ( user["first_name"], user["surname"], user["cell"], user["city"], user["location_x"], user["location_y"], userId) mycursor.execute(sql, values) mydb.commit() # UPDATE THE USER'S PROFILE IN THE DATABASE ### Since the user's data is stored in a single document, we only have to make one update result = db['Users'].update_one( {"_id": userId}, {"$set": user})
59. @JoeKarlsson1
60. @JoeKarlsson1
61. @JoeKarlsson1
62. Agenda @JoeKarlsson1
64. { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", }
65. { _id: ”JoeKarlsson1", displayName: ”JoeKarlsson1”, numFollowers: 2095 followers: [ “jessicaewest”, “wesley83”, “GraceFr” ... ] } { _id: ”KimKardashian", displayName: ”Kim Kardashian West", numFollowers: 62104245 followers: [ “c_hotaling”, “IAmJerdog”, “ChloeCondon” ... ], has_extras: true } { _id: ”KimKardashian_1", twitter_id: “KimKardashian”, is_overflow: true, followers: [ “StephenAtHome”, “TheEllenShow”, “hulu” ... ] }
68. { a: “b”, c: { d: “e” ... }, f: [“g”, “h”, “i”], j: [ { k: “l” }, { m: “n” } ] }
69. { ... } { ... } { ... } { ... } { ... } { ... } { ... }
70. @JoeKarlsson1
71. @JoeKarlsson1
72. @JoeKarlsson1
73. @JoeKarlsson1 # UPDATE THE USER'S PROFILE IN THE DATABASE ### First update what is stored in the Users table sql = "UPDATE Users SET first_name=%s, surname=%s, cell=%s, city=%s, location_x=%s, location_y=%s WHERE (ID=%s)" values = ( user["first_name"], user["surname"], user["cell"], user["city"], user["location_x"], user["location_y"], userId) mycursor.execute(sql, values) mydb.commit() # UPDATE THE USER'S PROFILE IN THE DATABASE ### Since the user's data is stored in a single document, we only have to make one update result = db['Users'].update_one( {"_id": userId}, {"$set": user})
74. @JoeKarlsson1
75. @JoeKarlsson1
76. @JoeKarlsson1
77. @JoeKarlsson1
78. @JoeKarlsson1 { first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], cars: [ { model: "Bentley", year: 1973 }, { model: "Rolls Royce", year: 1965 } ] } { first_name: ”Joe", surname: ”Karlsson", cell: ”1235552222", city: ”Minneapolis”, profession: [”software engineer", ”developer advocate"], } { first_name: ”BMO", surname: ”Karlsson", city: ”Minneapolis", }
79. @JoeKarlsson1 { a: “b”, c: { d: “e” ... }, f: [“g”, “h”, “i”], j: [ { k: “l” }, { m: “n” } ] }
80. @JoeKarlsson1 { ... } { ... } { ... } { ... } { ... } { ... } { ... }
81. What’s next? @JoeKarlsson1
82. MongoDB Community • MongoDB University: @JoeKarlsson1
83. http://bit.ly/fromSQLToNoSQL
84. Want $200 in FREE MongoDB Atlas Credits? Use code JOEKATLAS200 @JoeKarlsson1 http://bit.ly/FreeAtlasCredits
85. Additional Resources • • • @JoeKarlsson1
86. @JoeKarlsson1 Thank you!
87. { name: “Joe Karlsson”, company: “MongoDB”, title: [ “Developer Advocate”, “Software Engineer” ], } twitter: “@JoeKarlsson1”, twitch: “joe_karlsson”, tiktok: “joekarlsson”, website: “joekarlsson.com”, opinions: “my own”, links: “bit.ly/fromSQLToNoSQL”