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.
CREATE TABLE IF NOT EXISTS cats (
id SERIAL PRIMARY KEY,
name TEXT,
type TEXT,
weight INTEGER
);
Run the SQL against your database:
psql -d recipes -f init_tables.sql
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

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

dropdb

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

Database workflow cheatsheet

Create your db:
createdb my_db_name
Create the tables:
psql -d my_db_name -f init_tables.sql
Create the seed data:
psql -d my_db_name -f seed.sql
(make some changes to the tables and/or seed data)
Delete the db so that you can start at the beginning:
dropdb my_db_name
Copy link
On this page
Introduction
init_tables.sql
seed.sql
dropdb
Database workflow cheatsheet