Chapter 7. Getting Started with the Database

In my first year of computer science education, I tried to avoid databases as much as possible. I naively said, "Why do I need to learn about databases? I have no desire to be a DBA!" It didn't take long until I realized you can't do anything interesting and ongoing if you don't know how to interact with a database. So far, we've created a fair bit of plumbing in our hipstr application, but we have yet to actually save anything to the database. This chapter is going to change all of this. In this chapter, we will cover the following topics:

• Set up the database schema using PostgreSQL

• Learn how to maintain and migrate database schemas using the Migratus Leiningen plugin

• Insert data from the Sign Up form (built in Chapter 5, Handling Form Input) into the database using the brilliantly simple SQL library YeSQL

This, and the following three chapters, will cover basic database interactions. This chapter provides the foundation of the meat of our application...

Creating the database schema

Before we interact with a database, we'll need to actually create a database schema, go figure, as you can't harvest eggs without the chicken. In this section, we're going set up the database schema for our hipstr application, using PostgreSQL.

Note

This section assumes you already have the PostgreSQL database server installed on your machine, and thus, will not guide you through that process. You can download PostgreSQL at http://www.postgresql.org/download/. This book was written using PostgreSQL 9.3.5 OS X application on Mac OS X Yosemite.

You may be asking, "Why a database schema instead of a new database?" You can do either, really. The advantage of creating a new database schema over a database, however, comes in the form of role and user management, which for the most part, we won't get into except to create the user for the hipstr application. Let's do this now.

Perform the following steps as either a super user (if you already have a PostgreSQL super user...

Maintaining the database schema

A pain point common in nearly every team-based project I've ever worked on is how to manage the database. More often than not, teams decide to have a central development database, and all the developers use that central development database as their backing DB while making changes. This invariably results in somebody breaking somebody else's application under development because the database schema gets changed in some way, or test data gets removed or modified, and so on. Having a single monolithic development database can be real nasty, and is something that I try to avoid.

I'm a firm believer that a developer should be able to check out the source code, and within a few keystrokes, be able to successfully build and run the application. This is difficult, if not impossible, to do without having some kind of automated tool to build a database on the developer's own local machine. Thankfully, some kind souls on the Internet share my ideals and have created...

Adding data to the database

There are a thousand and one ways to get data into, and out of, a database. I confess that I am not a fan of ORMs such as Hibernate because over the long term, I think they're far more costly than writing your own SQL. ORMs are convenient during development, and they keep the code consistent, but they abstract SQL so far away that it can be difficult to diagnose what's happening when your data access isn't performing how you expect it to (whether it be performance, incorrect data retrieval, or something more sinister). Of course, ORMs have their value in the sense that they're (mostly) database agnostic, but never once in my career have I worked on anything wherein the backend database was a variable.

This being said, I also loath SQL strings in my code. They're ugly, they're hard to read, they remove much of the built-in SQL highlighting and support of many modern IDEs, they force you to create weird classes with nothing but SQL templates in them, and they just...

Summary

We came a long way in this chapter. We shared a few laughs, and we learned some valuable life lessons along the way. You learned how you can sanely manage your database schema using Migratus, as well as how you can use SQL without cluttering your Clojure code using YeSQL. The Migratus/YeSQL combination allows you to quickly develop and maintain your database without giving up the power, flexibility, and visibility of raw SQL, something we typically sacrifice when using an ORM. In the next chapter, we'll continue using YeSQL as we create the login form for our application.