Blog Post Table Assignment
In this problem set, you will establish a table for your blog's posts, populate your blog content via MySQL Workbench, and then generate your blog's content from your database.
Backing up your Blog Content
Your blog content is safely in your repository history in the event of an issue. However, part of this problem set will involve replacing your blog's hard-coded content with a template that renders data from the database. As such, you may just want to make a copy of your template file that contains your blog post content.
Setting up the Blog Post Table
In MySQL Worbench, connect to your database.
In your database schemas tab, right click on Tables and select Create Table.
Create a table with at least the following columns. You can add other columns, if needed:
Table Name: posts
Columns:
Column Title | Type | Options |
---|---|---|
id | INT(11) | Primary Key, Non-Null, Auto-Increment |
title | TEXT | Non-Null |
body | TEXT | Non-Null |
publishAt | DATETIME | Non-Null |
Apply the changes to create the posts
table to store your blog's content.
Adding Blog Post Content
Next, you'll want to migrate your blog post's HTML content (as best you can) from your template to MySQL. To do so, you should select all rows of you posts
table and then begin editing a new row.
There are a couple of caveats when adding content through MySQL Workbench.
First, entering HTML can be difficult and there are two strategies.
Check to see if your version of MySQL workbench has a "Form Editor" tab. If so, select it.
If you do not have a "Form Editor" tab, after entering your post's title, right click on the
body
area and look for an option to "Open Value in Editor". Be sure you're in Text mode and not Binary mode. Then you can paste HTML into this area and continue editing.
Second, date time formatting in MySQL (and in most programming environments!) requires precise formatting. When dating a blog post, use the format, 2019-MM-DD, should you want time, as well, the format is YYYY-MM-DD HH:MM:SS, and for good measure you should assume the timezone is +0000 Greenwhich Mean Time.
Apply your changes after your first post to be sure your input will work. Then continue on to add your other posts (you can also come back to this step).
Querying Blog Posts from the Database
In the route of your website that displays blog posts, before rendering the view template, add a query to your database to select all columns and rows from your posts
table. Your query will look like the one for todos
, but you may want to add an additional sorting criterion to guarantee your posts are sorted by publishAt
date in descending order no matter what order you added them to the database:
SELECT * FROM posts ORDER BY publishAt DESC
Update your blog post template to display posts from this database content as opposed to hard-coded into your HTML.
If the date formating of publishAt
is not to your liking, you may want to map
your rows
before rendering your view
and making use of the moment.js
library to handle your formatting. To get started with moment, you'll want to install it:
$ npm install --save moment
Read more about moment's API for parsing and formatting here: https://momentjs.com/docs/#/parsing/
Once you've got your blog posts showing up and pulled from your database, commit, push to server-side
, and try $ npm run deploy
from your local machine. If your site isn't fully operational, don't fret, it's likely an issue with the .env
file on your server. You can try diagnosing by logging into your server (ssh blog
), navigating into your travel-notes directory, and opening the .env
file in your text editor of choice (nano
or vim
). The values should be:
NODE_ENV=production
PORT=80
MYSQL_HOST=127.0.0.1
MYSQL_USER=blog_app
MYSQL_DB=blog
MYSQL_PASSWORD=<the password you setup>
Once your .env
file is properly set, try doing a sudo service blog_app restart
to try restarting your app and giving it a minute to build and start up. If you're still out of order, don't fret, we can fix production in the morning.
Gradescope
Please submit on Gradescope once complete!