Design and Create Database Tables

The design for Banking On Clojure database contains three tables, account_holders, accounts and transaction_history.

account_holders contains a unique entry for every customer in the bank.

accounts contains every account created for the bank. Each account has an account holder, a current balance and a date of when the current balance was last updated.

transaction_history contains every transaction that takes place in the bank. Each transaction is related to a specific account. The current balance for an account is built from all the transactions for a specific account. The number of transactions used to calculate the current balance is reduced by using the last_updated value from accounts.

Organising the code

The SQL statements that create the database tables will be bound to a suitable name using def. The value will be a vector containing the string of the SQL statement.

The SQL statements are generated by the DBever database management tool. The tables are created in DBeaver and the DDL script is exported for each table and pasted in the Clojure code.

A create-tables helper function executes the given SQL statements on a specific data source, from within a transaction so that all table are either created or none are created.

db-specification-dev is a name bound to the database specification map for the development database. This should eventually end up as an aero configuration along with the production database specification.

(def db-specification-dev {:dbtype "h2" :dbname "banking-on-clojure"})

Define ACCOUNT-HOLDERS table

Define the SQL statement to create a table to hold all the ACCOUNT-HOLDERS.

The design includes all the customer details plus from the Banking on Clojure specifications and the account_holder_id that uniquely identifies the customer.

(def schema-account-holders-table
  ["CREATE TABLE PUBLIC.ACCOUNT_HOLDERS(
     ACCOUNT_HOLDER_ID UUID(16) NOT NULL,
     FIRST_NAME VARCHAR(32),
     LAST_NAME VARCHAR(32),
     EMAIL_ADDRESS VARCHAR(32),
     RESIDENTIAL_ADDRESS VARCHAR(255),
     SOCIAL_SECURITY_NUMBER VARCHAR(32),
     CONSTRAINT CONSTRAINT_3 PRIMARY KEY (ACCOUNT_HOLDER_ID))"])

UUID inefficient for large data sets

Using random data, like uuid, for indexes can be inefficient especially for larger data sets. There may be data types for each specific database that provide more efficient ways of managing unique ids. For the scope of this project, using a UUID is acceptable.

Define ACCOUNTS table

Define the SQL statement to create the ACCOUNTS

Each account is associated with an ACCOUNT_HOLDER_ID so that all the accounts belonging to a customer can be easily found.

The current balance holds the value of credit in an account at the time of the last updated date. The current_balance is calculated from the values in the transaction_history and updates to the current_balance will update the last_updated value. This provides a very simplistic mechanism for quickly presenting the value of an account.

(def schema-accounts-table
  ["CREATE TABLE PUBLIC.ACCOUNTS(
     ACCOUNT_ID UUID(16) NOT NULL,
     ACCOUNT_NUMBER INTEGER NOT NULL AUTO_INCREMENT,
     ACCOUNT_SORT_CODE VARCHAR(6),
     ACCOUNT_NAME VARCHAR(32),
     CURRENT_BALANCE VARCHAR(255),
     LAST_UPDATED DATE,
     ACCOUNT_HOLDER_ID VARCHAR(100) NOT NULL,
     CONSTRAINT ACCOUNTS_PK PRIMARY KEY (ACCOUNT_ID))"] )

Define TRANSACTION_HISTORY table

Define the SQL statement to create the ACCOUNTS

All transactions include a value of the transaction, a reference to explain the purpose of the transaction, a date the transaction occurred, the account the transaction comes from and the account the transaction goes to.

(def schema-transaction-history-table
  ["CREATE TABLE PUBLIC.TRANSACTION_HISTORY(
     TRANSACTION_ID UUID(16) NOT NULL,
     TRANSACTION_VALUE INTEGER NOT NULL,
     TRANSACTION_REFERENCE VARCHAR(32),
     TRANSACTION_DATE DATE,
     ACCOUNT_FROM INTEGER,
     ACCOUNT_TO INTEGER,
     CONSTRAINT TRANSACTION_HISTORY_PK PRIMARY KEY (TRANSACTION_ID))"])

Constraint Naming

Constraints are used to add Primary Keys to database tables. Each constraint needs an identifier which is included in error reporting when there are issues. It is recommended to use meaningful names for identifiers to trace the source of errors and also support maintenance of the overall database design.

Execute Table schema in the development database

Define a function to use the table creation SQL statements and execute them on the given database.

The function uses with-open to create and manage a connection, closing that connection when the function has completed.

(defn create-table
  "Establish a connection to the data source and create a table within a transaction.
  Close the database connection.
  Arguments:
  - table-schemas: a vector containing an sql statements to create a table"
  [table-schema data-spec]

  (with-open [connection (jdbc/get-connection data-spec)]
    (jdbc/execute! connection  table-schemas)))

with-open - managing resources

with-open ensures that resources get closed and clearly defines the scope of the using the resource.

This helps the developer avoid using Clojure’s lazy sequences in a with-open block. Within the scope of the with-open expression it is important to make sure that the result is eagerly evaluated to avoid accessing the resource after it’s closed, or fall foul of the "ResultSet closed" or "transaction closed" errors.

Refactor the function to execute all the SQL statements in a transaction, so either all the databases are created or none are.

Using transactions can help prevent databases becoming in an inconsistent state due to only partial completion of a set of SQL statements.

(defn create-tables
  "Establish a connection to the data source and create all tables within a transaction.
  Close the database connection.
  Arguments:
  - table-schemas: a vector of sql statements, each creating a table"
  [table-schemas data-spec]

  (with-open [connection (jdbc/get-connection data-spec)]
    (jdbc/with-transaction [transaction connection]
      (doseq [sql-statement table-schemas]
        (jdbc/execute! transaction sql-statement) ))))

Refactor for connection pools

with-open function can be removed from the create-tables function when using a connection pool, passing the existing connection from the pool to the jdbc/with-transaction function.

Calling the create-tables function will create the database tables in the development database.

The H2 database writes the tables to disk in the banking-on-clojure.mv.db file. Unless the table is dropped, there is no need to evaluate this function again.

Viewing tables in the database

(defn information-tables
  [data-source]
  (jdbc/execute!
    data-source
    ["select * from information_schema.tables"])

Create a helper function to show the schema of any particular table. The function takes a table name and using str to combine table name with the rest of the SQL statement.

(defn show-schema
  [table-name]
  (jdbc/execute!
    data-source
    [(str "show columns from " table-name)]))

A specific schema can be viewed by calling the show-schema function

(show-schema "accounts")

Refactor the show-schema function to take the data source as an argument as well as the table name. The function is then usable for development, staging and production data sources (although its not generally advisable to update production database from a REPL in the development environment once its live)

(defn show-schema
  [data-source table-name]
  (jdbc/execute! data-source [(str "show columns from " table-name)]))

The connection is not manged though, so refactor again and add the with-open command to ensure the connection is closed once the function has finished.

(defn show-schema
  [db-spec table-name]
  (with-open [connection (jdbc/get-connection db-spec)]
    (jdbc/execute! connection [(str "SHOW COLUMNS FROM " table-name)])))

Removing (dropping) database tables

A helper function for removing tables from the database, dropping the data and the schema of the table.

drop-table function only differs from the show-schema function in the specific SQL statement it uses.

(defn drop-table
  [db-spec table-name]
  (with-open [connection (jdbc/get-datasource db-spec)]
    (jdbc/execute! connection [(str "DROP TABLE " table-name)])))

A generic helper function could be used if the SQL statement were also an argument.

Managing database tables from the REPL

When designing the database schema it can be useful to iterate quickly around the design. Using a Rich Comment Block to hold expressions to create show and drop tables is an effective way to manage the database schema quickly.

(comment  ;; Managing Schemas

  ;; Create all tables in the development database
  (create-tables [schema-account-holders-table schema-accounts-table schema-transaction-history-table]
                 db-specification-dev)

  ;; View application table schema in development database
  (show-schema data-source-dev "PUBLIC.ACCOUNT_HOLDERS")
  (show-schema data-source-dev "PUBLIC.ACCOUNTS")
  (show-schema data-source-dev "PUBLIC.TRANSACTION_HISTORY")

  ;; View database system schema in development database
  (show-schema data-source-dev "INFORMATION_SCHEMA.TABLES")

  ;; Remove tables from the development database
  (drop-table data-source-dev "PUBLIC.ACCOUNT_HOLDERS")
  (drop-table data-source-dev "PUBLIC.ACCOUNTS")
  (drop-table data-source-dev "PUBLIC.TRANSACTION_HISTORY"))

Manage database schema with Migratus

Migratus provides an elegant approach to evolving database schema

Common errors

Syntax error in SQL statement can occur if the SQL statement is not correct, the most common cause is a missing comma.

Databases do not always support exactly the same SQL syntax, especially around types and more advanced features. SQL statements may not work exactly the same for each database. Using tools like DBever will generated SQL expressions for specific databases.