We've got a request to make an application to track favorite Jazz artists and songs for a local fan club. One of the members is a hobby developer, so they've provided some starter code for us, but they aren't sure how to write the code to work with a database, so we'll need to add that part.
- Set up a database based on requirements from an ERD
- Connect to the database from an Express server using
pg
- Write basic SQL statements to read (SELECT), sort (ORDER BY) and create (INSERT) data.
- Fork & clone the starter code
- Install dependencies (
npm install
) - Start the app (
npm start
) - Check out the demo app at http://localhost:5000
- Look through the code that has been provided to you!
The fan club does have an idea of what data they want to store, as you can see in the demo app they provided. They also gave us a simple ERD to reference.
An ERD is an Entity Relationship Diagram. Basically it's an illustration of the tables and data that should be in a database, and any relationships between them.
From this ERD, we can see that we'll need to setup 2 tables: artist & song. We can also see what columns they expect those tables to have, as well as what data types we should use for each.
Using the ERD above:
- Create a new database called
jazzy_sql
using Postico. - Write the SQL to create both of the tables (Note: Make sure to use the table names show.)
- Write INSERT statements to add the sample data provided in the
server.js
file. - Create the tables and insert the test data using Postico.
- Add the SQL to the
database.sql
file. This file will help others interested in this project, get it setup correctly if they pick it up from GitHub.
- Update the
\artist
GET route to get data from the database. Sort the data by birthdate, youngest to oldest. - Update the
\artist
POST route to store data in the database. - Update the
\song
GET route to get data from the database. Sort the data by title. - Update the
\song
POST route to store data in the database.
NOTE: You should only need to modify server side code if you've named things correctly. You can continue to the existing client code for testing.
- Move the
pool
configuration out ofserver.js
and into apool.js
module file. - Move the
\artist
routes to an artist router file. - Move the
\song
routes to a song router file.