PostgreSQL

DB scripts the hard (dumb?) way

  • Since I’m completely new to this, I’m learning by writing DB scripts from scratch. The three basic scripts I have when bootstrapping apps: reset.sh, schema.sh and seed.sh
# reset.sh
printf '===Reset DB===\n'

psql -U DB_USERNAME -d DB_NAME << EOF
  DROP TABLE IF EXISTS table_1 table_2;
EOF

printf '===DB Reset===\n'

# schema.sh
psql -U DB_USERNAME -d DB_NAME << EOF
  CREATE TABLE users (
    user_id serial PRIMARY KEY,
    user_first_name text DEFAULT '',
    user_last_name text DEFAULT '',
    user_username text UNIQUE DEFAULT '',
    user_photo_url text DEFAULT '',
    user_auth_date text DEFAULT '',
    session_id text DEFAULT ''
  );
EOF

printf '===Loading Schema===\n'

# seed.sh
printf '===Seeding Data: USERS===\n'

psql -U DB_USERNAME -d DB_NAME << EOF
  INSERT INTO 
    users (user_first_name, user_last_name, user_username)
  VALUES 
    ('Edison', 'Chee', 'edisonchee');
EOF

printf '===Done Seeding TG_USERS===\n\n'

References