In Chapter 7, Getting Started with the Database, we covered a lot of ground: We created a database schema and granted our application access, we were introduced to Migratus and started using it to create our first table, and we were introduced to YeSQL for inserting data into the DB without the use of any ORM sorcery. In this chapter, we will continue our example by fetching data from the database. We will be:
- Creating two new tables: albums and artists
- Seeding our new tables with example data
- Creating a new page that displays the recently added albums
- Creating a new artist page
- Linking the Recently Added page to the artist page
Much of this chapter should appear obvious after everything we've done so far. So let's get to work!
At this point, we want to lay the foundation of allowing our user to catalog and view their record collection. We will create a simple Recently Added album page, and a simple Show me all the albums for this artist page (which I'll just call "the artist page"). The first thing to do in order to facilitate this incredible functionality is to create the artists table and the albums table.
We will use Migratus to create the artists table. As such, we need to create another SQL migration file. So let's do the following:
- In the
src/migrationsfolder, create a new migration script called00000000000200-artists.up.sql. - For our hipstr app, the artists table is going to be extremely simple. We'll have an
artist_idthat uniquely identifies the artist, aname(go figure), and two timestamps,created_atandupdated_at, for keeping track of when the artist was created and last updated. Add the following SQL:CREATE TABLE artists -- #1 ( artist_id SERIAL NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'utc'), updated_at TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'utc'), CONSTRAINT artist_name UNIQUE(name)); --;; -- create an update trigger which updates our updated_at -- column by calling the above function CREATE TRIGGER update_artist_updated_at BEFORE UPDATE --#2 ON artists FOR EACH ROW EXECUTE PROCEDURE update_updated_at();
In the artists migrations script, we wrote a CREATE TABLE statement (#1), which should look familiar (it's very similar to our users table). Similar to the user's table, we put a BEFORE UPDATE trigger (#2), which allows our database to manage the updated_at field and not rely on us to maintain it every time we update the record. The update_updated_at function is created as part of the 00000000000100-users.up.sql migration.
We can ensure that the previous script works by first using Migratus, and then use psql to ensure that the table was created. Do the following:
- From the command line, migrate the database to include the artists table by using migratus:
# lein migratus migrate - Connect to the database using the
psqltool:# psql -U hipstr -d postgres -h localhost - Use the
dt commandto list all the tables available to the current role:postgres=> dt
If everything succeeds as it should, you will see something similar to the following:
Of course, we can't have an up migration without a down migration, as that's just not going to fly when we're developing and we want to reset the universe. So let's create the down migration for the artists table by doing the following:
- In the
src/migrationsfolder, create a new migration script called00000000000200-artists.down.sql. - Write an SQL statement that destroys the artists table:
DROP TABLE artists;
(Again, we can ensure that our script works by rolling back just the artists table using lein migratus down, and then psql'ing to the database and running the same dt command):
- From the command line, roll back the artists table:
# lein migratus down 200 - Connect to the database using
psql:# psql -U hipstr -d localhost -h localhost - Use the
psqlcommand to list all the tables, which will magically no longer list ourartiststable:postgres=> dt
Again, if everything succeeds, we will see something similar to the following:
However, since this chapter is mostly focusing on reading data and not adding data (because I'm a crazy Canuck like that), we are going to need some data to work with.
Migratus is a dumb migration tool in that it doesn't know what it's executing; it just knows it's executing something. So there's nothing stopping us from seeding our artists table with data. This might seem like a weird thing to do. However, in a team environment – or even in your own development environment – this is a great way to easily seed the database. Let's add the following few insert statements to the bottom of our artists migration script so that we can seed it:
--;;
INSERT INTO artists (name) VALUES ('The Arthur Digby Sellers Band')
--;;
INSERT INTO artists (name) VALUES ('Fort Knox Harrington')
--;;
INSERT INTO artists (name) VALUES ('Hungus')
--;;
INSERT INTO artists (name) VALUES ('Smokey Fouler')
--;;
INSERT INTO artists (name) VALUES ('Brant')All we need to create a new artist in our artists table at this point is the artist name, because all the other fields are calculated.
You can run lein migratus migrate to re-create the artists table and insert the seed data. If you'd like to ensure the table was properly seeded, you can use psql to run a query and ensure that the output is what we expect, as shown as follows:
Now that we have the artists table up and running, it's time we create the albums table.
For the sake of simplicity, this section will assume that an album can have a single artist. There's nothing stopping you from normalizing the database, such that you can create an artists_albums table that allows for a many artists to many albums approach. For this example, we'll assume a single album has a single artist, though an artist will have many albums.
As with users and artists, we will create up and down migration scripts. We'll be doing both of these as follows:
- Create a new file in
src/migrationslocation called00000000000210-albums.up.sql. - Add the following SQL to the albums migration file:
CREATE TABLE albums (album_id SERIAL NOT NULL PRIMARY KEY, artist_id BIGINT NOT NULL REFERENCES artists (artist_id), name VARCHAR(255) NOT NULL, release_date DATE NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'utc'), updated_at TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'utc'), CONSTRAINT arist_album_name UNIQUE (artist_id, name)); --;; -- create an update trigger which updates our update_date column by calling the above function CREATE TRIGGER update_album_updated_at BEFORE UPDATE ON albums FOR EACH ROW EXECUTE PROCEDURE update_updated_at(); --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'My Iron Lung', '1978-11-24' FROM artists a WHERE a.name = 'The Arthur Digby Sellers Band' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'American History Fail', '2000-04-18' FROM artists a WHERE a.name = 'The Arthur Digby Sellers Band' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'Giggles and Mustaches', '1992-11-29' FROM artists a WHERE a.name = 'Fort Knox Harrington' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, '20 Tons of Video Gold', '1990-10-09' FROM artists a WHERE a.name = 'Fort Knox Harrington' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'Fixing the Cable', '1989-06-02' FROM artists a WHERE a.name = 'Hungus' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'Over the Line', '1998-08-08' FROM artists a WHERE a.name = 'Smokey Fouler' --;; INSERT INTO albums (artist_id, name, release_date) SELECT a.artist_id, 'Petulant Suckup', '1995-05-21' FROM artists a WHERE a.name = 'Brant'
Similar to previous up migration scripts, we are creating a new table and employing the use of a BEFORE UPDATE trigger to manage the updated_at field.
We also seed the table with some example data using INSERT SELECT statements. INSERT SELECT statements allow us to populate the artist_id field without hardcoding the actual value of the artist_id field. Instead, we fetch the artist_id value just in time.
As with the artists table, we can create our albums table by running lein migratus migrate, and then use psql to verify that our albums table was properly created and seeded with data. You should see something similar to the screenshot shown as follows:
Also, let's not forget the down script. We can create it by following the same pattern as the artists.down script:
- In the src/migrations folder, create a new migration script called
00000000000210-albums.down.sql. - Write a SQL statement that destroys the albums table:
DROP TABLE albums;
Ensure the script works by rolling back just the albums table using lein migratus down, and then psql'ing to the database and running the dt command
- From the command line, roll back the artists table:
# lein migratus down 210 - Connect to the database using
psql:# psql -U hipstr -d localhost -h localhost - Use the dt command to list all the tables that will no longer list the
albumstable:postgres=> dt
If everything succeeds, you will see something similar to the following:
Before moving on, run lein migratus migrate to re-create and reseed the albums table, because now we're going to make use of it.