Bootcamp
Search…
3.4.9: Database Setup Files

Introduction

We will formalize a set of commands and files you can use to work with the database.
All the dummy data you need to properly setup and populate your app should be included in these files.
Remember that since your database itself is not in the repo, you need to be able to recreate the minimal setup your app needs to run in a set of repository-controlled files.

init_tables.sql

init_tables.sql will contain all of the CREATE TABLE statements needed to setup your app.
1
CREATE TABLE IF NOT EXISTS cats (
2
id SERIAL PRIMARY KEY,
3
name TEXT,
4
type TEXT,
5
weight INTEGER
6
);
Copied!
Run the SQL against your database:
1
psql -d recipes -f init_tables.sql
Copied!
Adding the CREATE TABLE IF NOT EXISTS syntax means that you can run the above command in psql as many times as you want and it will not cause any errors.

seed.sql

1
INSERT INTO cats (name, type, weight) VALUES ('Mr. Snuggles', 'Calico', 327);
2
INSERT INTO cats (name, type, weight) VALUES ('Jake AlPurrrrtsen', 'Bambino', 424);
3
INSERT INTO cats (name, type, weight) VALUES ('Furry Mc Furmeister', 'Persian', 512);
4
INSERT INTO cats (name, type, weight) VALUES ('Kai', 'LaPerm', 387);
Copied!
Run the SQL against your database:
1
psql -d recipes -f seed.sql
Copied!

dropdb

Start from scratch again by dropping the entire database.
1
dropdb recipes -U kai
Copied!

Database workflow cheatsheet

Create your db:
1
createdb my_db_name
Copied!
Create the tables:
1
psql -d my_db_name -f init_tables.sql
Copied!
Create the seed data:
1
psql -d my_db_name -f seed.sql
Copied!
(make some changes to the tables and/or seed data)
Delete the db so that you can start at the beginning:
1
dropdb my_db_name
Copied!