Create MySQL Database Pushed to GitHub Branch Before Class Tues - 01/19/21

[Kay0s]

To be completed by Saturday. Seed data included - sample user - someone who saved tacos

[Kay0s]

[Kay0s]

@DaronSchmit add seeds please

[Kay0s]

omdb api call: {"Title":"Cars","Year":"2006","Rated":"G","Released":"09 Jun 2006","Runtime":"117 min","Genre":"Animation, Comedy, Family, Sport","Director":"John Lasseter, Joe Ranft(co-director)","Writer":"John Lasseter (original story by), Joe Ranft (original story by), Jorgen Klubien (original story by), Dan Fogelman (screenplay by), John Lasseter (screenplay by), Joe Ranft (screenplay by), Kiel Murray (screenplay by), Phil Lorin (screenplay by), Jorgen Klubien (screenplay by)","Actors":"Owen Wilson, Paul Newman, Bonnie Hunt, Larry the Cable Guy","Plot":"A hot-shot race-car named Lightning McQueen gets waylaid in Radiator Springs, where he finds the true meaning of friendship and family.","Language":"English, Italian, Japanese, Yiddish","Country":"USA","Awards":"Nominated for 2 Oscars. Another 28 wins & 32 nominations.","Poster":"https://m.media-amazon.com/images/M/MV5BMTg5NzY0MzA2MV5BMl5BanBnXkFtZTYwNDc3NTc2._V1_SX300.jpg","Ratings":[{"Source":"Internet Movie Database","Value":"7.1/10"},{"Source":"Rotten Tomatoes","Value":"74%"},{"Source":"Metacritic","Value":"73/100"}],"Metascore":"73","imdbRating":"7.1","imdbVotes":"367,076","imdbID":"tt0317219","Type":"movie","DVD":"N/A","BoxOffice":"$244,082,982","Production":"Walt Disney Pictures, Pixar Animation Studios","Website":"N/A","Response":"True"}

[Kay0s]

the meal api call: by string name

"meals": [ { "idMeal": "52894", "strMeal": "Battenberg Cake", "strDrinkAlternate": null, "strCategory": "Dessert", "strArea": "British", "strInstructions": "Heat oven to 180C/160C fan/gas 4 and line the base and sides of a 20cm square tin with baking parchment (the easiest way is to cross 2 x 20cm-long strips over the base). To make the almond sponge, put the butter, sugar, flour, ground almonds, baking powder, eggs, vanilla and almond extract in a large bowl. Beat with an electric whisk until the mix comes together smoothly. Scrape into the tin, spreading to the corners, and bake for 25-30 mins – when you poke in a skewer, it should come out clean. Cool in the tin for 10 mins, then transfer to a wire rack to finish cooling while you make the second sponge.\r\nFor the pink sponge, line the tin as above. Mix all the ingredients together as above, but don’t add the almond extract. Fold in some pink food colouring. Then scrape it all into the tin and bake as before. Cool.\r\nTo assemble, heat the jam in a small pan until runny, then sieve. Barely trim two opposite edges from the almond sponge, then well trim a third edge. Roughly measure the height of the sponge, then cutting from the well-trimmed edge, use a ruler to help you cut 4 slices each the same width as the sponge height. Discard or nibble leftover sponge. Repeat with pink cake.\r\nTake 2 x almond slices and 2 x pink slices and trim so they are all the same length. Roll out one marzipan block on a surface lightly dusted with icing sugar to just over 20cm wide, then keep rolling lengthways until the marzipan is roughly 0.5cm thick. Brush with apricot jam, then lay a pink and an almond slice side by side at one end of the marzipan, brushing jam in between to stick sponges, and leaving 4cm clear marzipan at the end. Brush more jam on top of the sponges, then sandwich remaining 2 slices on top, alternating colours to give a checkerboard effect. Trim the marzipan to the length of the cakes.\r\nCarefully lift up the marzipan and smooth over the cake with your hands, but leave a small marzipan fold along the bottom edge before you stick it to the first side. Trim opposite side to match size of fold, then crimp edges using fingers and thumb (or, more simply, press with prongs of fork). If you like, mark the 10 slices using the prongs of a fork.\r\nAssemble second Battenberg and keep in an airtight box or well wrapped in cling film for up to 3 days. Can be frozen for up to a month.", "strMealThumb": "https://www.themealdb.com/images/media/meals/ywwrsp1511720277.jpg", "strTags": "Cake,Sweet", "strYoutube": "https://www.youtube.com/watch?v=aB41Q7kDZQ0", "strIngredient1": "Butter", "strIngredient2": "Caster Sugar", "strIngredient3": "Self-raising Flour", "strIngredient4": "Almonds", "strIngredient5": "Baking Powder", "strIngredient6": "Eggs", "strIngredient7": "Vanilla Extract", "strIngredient8": "Almond Extract", "strIngredient9": "Butter", "strIngredient10": "Caster Sugar", "strIngredient11": "Self-raising Flour", "strIngredient12": "Almonds", "strIngredient13": "Baking Powder", "strIngredient14": "Eggs", "strIngredient15": "Vanilla Extract", "strIngredient16": "Almond Extract", "strIngredient17": "Pink Food Colouring", "strIngredient18": "Apricot", "strIngredient19": "Marzipan", "strIngredient20": "Icing Sugar", "strMeasure1": "175g", "strMeasure2": "175g", "strMeasure3": "140g", "strMeasure4": "50g", "strMeasure5": "½ tsp", "strMeasure6": "3 Medium", "strMeasure7": "½ tsp", "strMeasure8": "¼ teaspoon", "strMeasure9": "175g", "strMeasure10": "175g", "strMeasure11": "140g", "strMeasure12": "50g", "strMeasure13": "½ tsp", "strMeasure14": "3 Medium", "strMeasure15": "½ tsp", "strMeasure16": "¼ teaspoon", "strMeasure17": "½ tsp", "strMeasure18": "200g", "strMeasure19": "1kg", "strMeasure20": "Dusting", "strSource": "https://www.bbcgoodfood.com/recipes/1120657/battenberg-cake", "dateModified": null } ] }

[Kay0s]

https://sequelize.org/master/manual/migrations.html Now we should edit this file to insert demo user to User table.

module.exports = { up: (queryInterface, Sequelize) => { return queryInterface.bulkInsert('Users', [{ firstName: 'John', lastName: 'Doe', email: 'example@example.com', createdAt: new Date(), updatedAt: new Date() }]); }, down: (queryInterface, Sequelize) => { return queryInterface.bulkDelete('Users', null, {}); } }; Running Seeds In last step you have create a seed file. It's still not committed to database. To do that we need to run a simple command.

npx sequelize-cli db:seed:all This will execute that seed file and you will have a demo user inserted into User table.

Note: Seeder execution history is not stored anywhere, unlike migrations, which use the SequelizeMeta table. If you wish to change this behavior, please read the Storage section.

[Kay0s]

https://sequelize.org/master/manual/assocs.html One-To-One relationships Philosophy Before digging into the aspects of using Sequelize, it is useful to take a step back to consider what happens with a One-To-One relationship.

Let's say we have two models, Foo and Bar. We want to establish a One-To-One relationship between Foo and Bar. We know that in a relational database, this will be done by establishing a foreign key in one of the tables. So in this case, a very relevant question is: in which table do we want this foreign key to be? In other words, do we want Foo to have a barId column, or should Bar have a fooId column instead?

In principle, both options are a valid way to establish a One-To-One relationship between Foo and Bar. However, when we say something like "there is a One-To-One relationship between Foo and Bar", it is unclear whether or not the relationship is mandatory or optional. In other words, can a Foo exist without a Bar? Can a Bar exist without a Foo? The answers to these questions helps figuring out where we want the foreign key column to be.

Goal For the rest of this example, let's assume that we have two models, Foo and Bar. We want to setup a One-To-One relationship between them such that Bar gets a fooId column.

Implementation The main setup to achieve the goal is as follows:

Foo.hasOne(Bar); Bar.belongsTo(Foo); Since no option was passed, Sequelize will infer what to do from the names of the models. In this case, Sequelize knows that a fooId column must be added to Bar.

This way, calling Bar.sync() after the above will yield the following SQL (on PostgreSQL, for example):

CREATE TABLE IF NOT EXISTS "foos" ( / ... / ); CREATE TABLE IF NOT EXISTS "bars" ( / ... / "fooId" INTEGER REFERENCES "foos" ("id") ON DELETE SET NULL ON UPDATE CASCADE / ... / ); Options Various options can be passed as a second parameter of the association call.

onDelete and onUpdate For example, to configure the ON DELETE and ON UPDATE behaviors, you can do:

Foo.hasOne(Bar, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT' }); Bar.belongsTo(Foo); The possible choices are RESTRICT, CASCADE, NO ACTION, SET DEFAULT and SET NULL.

The defaults for the One-To-One associations is SET NULL for ON DELETE and CASCADE for ON UPDATE.

Customizing the foreign key Both the hasOne and belongsTo calls shown above will infer that the foreign key to be created should be called fooId. To use a different name, such as myFooId:

// Option 1 Foo.hasOne(Bar, { foreignKey: 'myFooId' }); Bar.belongsTo(Foo);

// Option 2 Foo.hasOne(Bar, { foreignKey: { name: 'myFooId' } }); Bar.belongsTo(Foo);

// Option 3 Foo.hasOne(Bar); Bar.belongsTo(Foo, { foreignKey: 'myFooId' });

// Option 4 Foo.hasOne(Bar); Bar.belongsTo(Foo, { foreignKey: { name: 'myFooId' } }); As shown above, the foreignKey option accepts a string or an object. When receiving an object, this object will be used as the definition for the column just like it would do in a standard sequelize.define call. Therefore, specifying options such as type, allowNull, defaultValue, etc, just work.

For example, to use UUID as the foreign key data type instead of the default (INTEGER), you can simply do:

const { DataTypes } = require("Sequelize");

Foo.hasOne(Bar, { foreignKey: { // name: 'myFooId' type: DataTypes.UUID } }); Bar.belongsTo(Foo); Mandatory versus optional associations By default, the association is considered optional. In other words, in our example, the fooId is allowed to be null, meaning that one Bar can exist without a Foo. Changing this is just a matter of specifying allowNull: false in the foreign key options:

Foo.hasOne(Bar, { foreignKey: { allowNull: false } }); // "fooId" INTEGER NOT NULL REFERENCES "foos" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT

[Kay0s]

• Start MySQL command line tool and login: mysql -u root -p.
• With the mysql> command line tool running, enter the command source schema.sql. This will run your schema file and all of the queries in it -- in other words, you'll be creating your database.
• Now insert the entries you defined in seeds.sql by running the file: source seeds.sql.