程序代写代做代考 database SQL Microsoft Word – Document5
Microsoft Word – Document5
Exercise 5
In exercise 4, you hopefully realized how cumbersome it was
writing your own raw SQL queries to interact with your data!
For this exercise, you’ll move away from writing SQL queries
directly, and instead interact with your database via an ORM.
Learning Outcome
By the end of this exercise, you should be very familiar with
how to interact with a database programmatically using an
ORM in node.js.
Prerequisites
In this assignment, you’ll be using the Sequelize.js (V3) library
to create and interact with your DB (still reading/writing to an
SQLite database).
To get familiar with how to programmatically interact with the
Sequelize ORM, check out the demo code from class, and/or
checkout this tutorial.
The Sequelize library makes heavy use of Promises (the
solution to callback hell). If you’re unfamiliar with Promises, I
suggest running through a tutorial such as this or this.
Getting started
You should build on top of what you have for exercise 3 (if you
haven’t completed exercise 3, you’ll need to do enough of it to
satisfy this exercises requirements, but does not need to satisfy
the requirements of exercise 4 or any prior exercise).
The reason why you’re building off exercise 3 and not 4 is
because you’re repeating what you did in exercise 4, except in
a different way.
You will need to load the external node.js dependencies for
Sequelize. Run the following in your terminal / command
prompt:
npm install –save sequelize
(the –save flag will automatically add sequelize as a
dependency in your package.json file).
Task
Your objective will be for all of your existing GET APIs to
interact with your database via the Sequelize ORM, rather than
the SQLite library from the previous exercise. I highly
recommend getting started by using the demo code from class,
or by following this example code.
When you update your GET requests to work with the
database, your Music App should continue to work as normal
any parts of the UI that use these APIs.
Creating your models
You will be setting up your models to match the same schema
you had from exercise 4.
In the demo code from class, there was just a model for Songs.
You’ll have to create a Playlist model.
Note: you should not need to define your id – a model is given
an id as it’s primary key by default in Sequelize.
You’ll also have to setup associations with your models. For a
ManyToMany relationship (which is the case here because
songs can belong to many playlists, and playlists can contain
many songs), you’ll want to use the .belongsToMany method
to setup your associations.
Note: you do *not* need to manually create a SongsPlaylists
model (unlike last assignment where you had to manually
associate the data with a table yourself). It will automatically
get created for you as long as you setup your belongsToMany
associations. (Hint: you’ll want to do this similarly to how it
was done in this example except using belongsToMany
instead of belongsTo. The class demo is based off this linked
example).
To further understand associations in Sequelize, read though
this.
Creating your database
In your previous assignment, your database was created by
running the CREATE TABLE SQL query. This time, you won’t
interact with your database directly – the database and it’s
tables will be created for you automatically based on the
models you define.
When your models are all setup, running node
populateDb.js should create a SQLite databased saved
music.db database with your Song, Playlist, and SongsPlaylists
table, and the data should be populated by what is in
songs.json and playlists.json. (in the sample code, the database
and database file used by Sequelize is specified in
models/index.js).
You’ll need to create your songs and playlists via
models.Song.create and models.Playlist.create. You’ll
need to use playlistInstance.addSong to add songs to
your playlists.
Note: you do *not* have to use your database from exercise 4.
You’ll be starting fresh with creating a new database using the
Sequelize ORM. Getting an ORM working with an existing
database is a bit of a pain. But if you are interested in
understanding how to do it, see this tutorial.
Updating your APIs
GET /API/SONGS
Instead of loading the JSON from disk, you should be crafting
the response yourself by pulling the data from the database
using your ORM and creating a JSON object from it that
matches the formal from the previous assignment.
This should return the exact same JSON response as the one
from exercise 3.
GET /API/PLAYLISTS
Same as above – this should return the exact same JSON
response as the one from exercise 3.
Remember: the Playlist model itself does not contain the
lists of songs. For that, you’ll need to use
playlistInstance.getSongs() (or another ORM method
for getting songs – just make sure to use the ORM and not
direct SQL calls!).
POST /API/PLAYLISTS
Don’t worry about updating the POST request in this exercise.
This will come in a future exercise.
Yes, that means your Music App will not be able to add new
playlists, or add new songs to existing playlists because your
POSTs will not interact with your database. We will get to that
in a later exercise.
Requirements Checklist
node populateDb.js creates a SQLite database called
music.db, populated by the data from songs.json and
playlists.json from exercise 3, using only ORM methods to
create the entries in the databases (no raw SQL queries
allowed). (3 marks)
Your GET /api/playlists pulls data from the database
via ORM method calls (no raw SQL queries allowed), and
returns the same data as it did in exercise 3 (matching the
content in playlists.json). (3 marks)
Your GET /api/songs pulls data from the database via
ORM method calls (no raw SQL queries allowed), and
returns the same data as it did in exercise 3 (matching the
content in songs.json). (3 marks)
Your Music App works with these updated GET APIs
(meaning songs, playlists, and songs in playlists are
successfully loaded into your UI matching the mocks from
the previous exercises). (1 mark)
Total: 10 marks.
Important notes
Your node.js app must start by running “npm install
&& node populateDb.js && npm start”. Using
different filenames will result in a loss of 3 marks. If this
command does not run your application successfully, we
will not attempt to figure out why, which will result in a
grade of zero. Make sure you declare your node.js
dependencies properly in your package.json file! Your
program must also work with Node.js 6.X. Specify exactly
what version of node you used in a README.md file.
You may use express.js (node.js backend framework) in
this exercise. Using express.js can help clean up your
codebase significantly, so I’d highly recommend
incorporating it into your music app!
You do not have to worry about response efficiency. By
that, I mean don’t worry if your not using streams to
respond to requests – sending buffered responses is fine
for now (although I highly encourage you to check out
streams!).
The use of raw SQL queries in this exercise will
result you in a grade of 0. The goal of this exercise is to learn
about ORMs!
