Create table

We have our database model for tasks, so lets create write some code that will create a database table in Postgres, assuming that table is not there already.

First add a dependency for Clojure.java.jdbc

[clojure.java.jdbc :as sql]

You items.clj should look like

(ns todo-list.items
  (:require [clojure.java.jdbc :as sql]))

We only want to create the database if it does not already exist, so we can check if the table is already part of the schema

(defn db-schema-migrated?
  "Check if the schema has been migrated to the database"
  []
  (-> (sql/query postgres
                 [(str "select count(*) from information_schema.tables "
                       "where table_name='tasks'")])
      first :count pos?))

Then add a condition to check if the table exists and if not then create the database table

(defn apply-schema-migration
  "Apply the schema to the database"
  []
  (when (not (db-schema-migrated?))
    (sql/db-do-commands postgres
                        (sql/create-table-ddl
                         :tasks
                         [:id :serial "PRIMARY KEY"]
                         [:body :varchar "NOT NULL"]
                         [:created_at :timestamp
                          "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"]))))

What Heroku does when you create a database

Heroku Postgres users are granted all non-superuser permissions on their database. These include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

Heroku runs the SQL below to create a user and database for you.

You cannot create or modify databases and roles on Heroku Postgres. The SQL below is for reference only.

CREATE ROLE user_name;
ALTER ROLE user_name WITH LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE database_name OWNER user_name;
REVOKE ALL ON DATABASE database_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO database_user;
GRANT ALL ON DATABASE database_name TO database_user;