Tweet
Tweet

Slide 1

Slide 1 text

@JoeKarlsson1 From SQL to NoSQL: Changing your Mindset @JoeKarlsson1 Joe Karlsson

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

We can only control one of these things @JoeKarlsson1 Source: https://medium.com/@addyosmani/the-cost-of-javascript-in-2018-7d8950fbb5d4

Slide 4

Slide 4 text

@JoeKarlsson1

Slide 5

Slide 5 text

{ 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”

Slide 6

Slide 6 text

Before we get started… @JoeKarlsson1

Slide 7

Slide 7 text

Thank you so much for coming! @JoeKarlsson1

Slide 8

Slide 8 text

Who’s this talk for? @JoeKarlsson1

Slide 9

Slide 9 text

@JoeKarlsson1

Slide 10

Slide 10 text

A quick audience poll

Slide 11

Slide 11 text

SQL?

Slide 12

Slide 12 text

MongoDB?

Slide 13

Slide 13 text

Want to follow along? @JoeKarlsson1 http://bit.ly/fromSQLToNoSQL

Slide 14

Slide 14 text

@JoeKarlsson1

Slide 15

Slide 15 text

Agenda @JoeKarlsson1

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

{ 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 } ] }

Slide 18

Slide 18 text

{ 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 } ] }

Slide 19

Slide 19 text

{ 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 } ] }

Slide 20

Slide 20 text

{ 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 } ] }

Slide 21

Slide 21 text

{ 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 } ] }

Slide 22

Slide 22 text

{ 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 } ] }

Slide 23

Slide 23 text

{ 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 } ] }

Slide 24

Slide 24 text

{ 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 } ] }

Slide 25

Slide 25 text

{ 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 } ] }

Slide 26

Slide 26 text

{ 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

Slide 27

Slide 27 text

{ 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

Slide 28

Slide 28 text

{ 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

Slide 29

Slide 29 text

{ 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

Slide 30

Slide 30 text

{ 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

Slide 31

Slide 31 text

{ 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

Slide 32

Slide 32 text

{ 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

Slide 33

Slide 33 text

Flexible schema database

Slide 34

Slide 34 text

ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ... a: “b” ... }

Slide 35

Slide 35 text

ID a ... 1 b ... 2 c ... 3 ... ... { ... a: “b” ... } { ... a: “c” ... }

Slide 36

Slide 36 text

{ ... } ... ... ... ... ... ... ... ... ... ... ... ... { ... } { ... }

Slide 37

Slide 37 text

No content

Slide 38

Slide 38 text

But immediately leaves because they couldn’t find table.

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

{ ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 41

Slide 41 text

{ ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Slide 42

Slide 42 text

{ ... a: “b”, ... c: { d: “e” ... }, ... } ID a ... 1 b ... 2 ... ... 3 ... ... ... d ... 1 e ... ... ... ...

Slide 43

Slide 43 text

{ ... } { ... } { ... } { ... } { ... } { ... } { ... } ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

Slide 44

Slide 44 text

@JoeKarlsson1

Slide 45

Slide 45 text

@JoeKarlsson1

Slide 46

Slide 46 text

Agenda @JoeKarlsson1

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

{ first_name: "Paul", surname: "Miller", cell: "447557505611", city: "London", location: [45.123,47.232], profession: ["banking", "finance", "trader"], } Users Professions

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

# 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

Slide 51

Slide 51 text

# 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"] }

Slide 52

Slide 52 text

# 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()

Slide 53

Slide 53 text

30 lines of code 73 lines of code

Slide 54

Slide 54 text

{ 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 ;

Slide 55

Slide 55 text

ID a ... 1 b ... 2 ... ... 3 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... { ... a: “b”, ... c: { d: “e” ... }, ... }

Slide 56

Slide 56 text

No content

Slide 57

Slide 57 text

@JoeKarlsson1

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

@JoeKarlsson1

Slide 60

Slide 60 text

@JoeKarlsson1

Slide 61

Slide 61 text

@JoeKarlsson1

Slide 62

Slide 62 text

Agenda @JoeKarlsson1

Slide 63

Slide 63 text

No content

Slide 64

Slide 64 text

{ 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", }

Slide 65

Slide 65 text

{ _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” ... ] }

Slide 66

Slide 66 text

No content

Slide 67

Slide 67 text

No content

Slide 68

Slide 68 text

{ a: “b”, c: { d: “e” ... }, f: [“g”, “h”, “i”], j: [ { k: “l” }, { m: “n” } ] }

Slide 69

Slide 69 text

{ ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 70

Slide 70 text

@JoeKarlsson1

Slide 71

Slide 71 text

@JoeKarlsson1

Slide 72

Slide 72 text

@JoeKarlsson1

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

@JoeKarlsson1

Slide 75

Slide 75 text

@JoeKarlsson1

Slide 76

Slide 76 text

@JoeKarlsson1

Slide 77

Slide 77 text

@JoeKarlsson1

Slide 78

Slide 78 text

@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", }

Slide 79

Slide 79 text

@JoeKarlsson1 { a: “b”, c: { d: “e” ... }, f: [“g”, “h”, “i”], j: [ { k: “l” }, { m: “n” } ] }

Slide 80

Slide 80 text

@JoeKarlsson1 { ... } { ... } { ... } { ... } { ... } { ... } { ... }

Slide 81

Slide 81 text

What’s next? @JoeKarlsson1

Slide 82

Slide 82 text

MongoDB Community • MongoDB University: @JoeKarlsson1

Slide 83

Slide 83 text

http://bit.ly/fromSQLToNoSQL

Slide 84

Slide 84 text

Want $200 in FREE MongoDB Atlas Credits? Use code JOEKATLAS200 @JoeKarlsson1 http://bit.ly/FreeAtlasCredits

Slide 85

Slide 85 text

Additional Resources • • • @JoeKarlsson1

Slide 86

Slide 86 text

@JoeKarlsson1 Thank you!

Slide 87

Slide 87 text

{ 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”

Slide 88

Slide 88 text

No content