Connect to SQL Server, Oracle or Postgres from Clojure using JDBC drivers

Introduction

Connecting to databases is one of the common things that developers want to do from any language. In this tutorial, we will walk you through how you can connect to SQL Server from Clojure using the clojure.java.jdbc library. Note that this tutorial can be used with databases like Oracle, Postgres, DB2 or in fact for any source you have a JDBC driver. We will be showing you all CRUD operations in this tutorial for you to get started.


Prerequisite Setup

  1. Download Leiningen script from official website
  2. Place it on your $PATH where your shell can find it (eg. ~/bin)
  3. Make it executable by running the following command
    chmod a+x ~/bin/lein
  4. Execute lein, and it will download the self-install package
    $ lein

Download Progress DataDirect JDBC SQL Server driver

 
  1. Download DataDirect SQL Server JDBC driver.
  2. Install SQL Server JDBC driver by running the following command
    java -jar PROGRESS_DATADIRECT_JDBC_SQLSERVER_ALL.jar
  3. Follow through the prompts of Installer and install the driver in default path or custom path.

Add JDBC Driver to Clojure classpath

  1. To add the JDBC driver to Clojure classpath, you need to add it to the Maven repository by running the following command.
    mvn install:install-file "-DgroupId=com.ddtek.jdbc" "-DartifactId=sqlserver" "-Dversion=1.0.0" "-Dpackaging=jar" "-Dfile=/path/Progress/DataDirect/Connect_for_JDBC_51/lib/sqlserver.jar"
  2. Now, when you go to the maven repository, you should see the DataDirect SQL Server JDBC driver there as shown below.Maven Repo

Create and configure Project

  1. Go to your workspace or any desired folder where you want to have the project and run the following command to create a new project
    lein new app <app-name>


  2. This will create a new folder with templates as shown below.Project Folder

     

     

     

  3. Open project.clj in the newly created project and add following dependencies:

     

    :dependencies [[org.clojure/clojure "1.8.0"]
              [org.clojure/java.jdbc "0.7.5"]
              [com.ddtek.jdbc/sqlserver "1.0.0"]]

     

  4. Also add the gorilla plugin to the file

     

    :plugins [[lein-gorilla "0.4.0"]]


     

  5. Save Project.clj and exit.
  6. Now navigate to the project folder and run the following command to start nREPL server

     

    lein gorilla :ip 0.0.0.0


    Gorilla Server Start

     

     

  7. Copy the URL generated by gorilla and open it in your browser. You should see a worksheet as shown belowGorilla Worksheet

Let’s Connect and Query

  1. Set a namespace and do imports of required libraries by evaluating the code below. We'll need the clojure.java.jdbc library and clojure.string

     

    (ns sqlserver-connect
      (:require [clojure.string :as str]
             [clojure.java.jdbc :as j]))

     

  2. Press Shift + Enter to evaluate, and you should see a response as shown below.

    import libraries

     

     

  3. Configure the database connection as shown in the below code and evaluate

     

    (def db {:classname "com.ddtek.jdbc.sqlserver.SQLServerDriver"
                   :subprotocol "datadirect:sqlserver"
                   :subname "//<host>:<port>;databaseName=<db>;user=<user>;password=<user>"})

     

    create database connection

     

     

  4. You can print the configuration, if you would like to by evaluating the following command

     

    println db


    Print Connection

     

     

  5. Let’s create a Table in SQL Server. Using the code below to create a DDL for creating a Pokémon Table with Pokedexid as Primary key and evaluate it


    (def pokemon-sql (j/create-table-ddl :pokemon [
                    [:pokedexid "int" "PRIMARY KEY"]
                    [:pokemon_name "VARCHAR(30)"]
                    [:type1 "VARCHAR(15)"]
                    [:type2 "VARCHAR(15)"]]))

     

    create table

     

  6. If you want to see the CREATE DDL Statement that got generated, you can print it and verify, by evaluating the following command

     

    println pokemon-sql


     

  7. Now execute the statement, to create the table in SQL Server using the above statement.

     

    (j/execute! db [pokemon-sql])


    execute create table

     

     

  8. Now, let’s insert some data in to the created table by evaluating the following command.


    (j/insert-multi! db :pokemon [
    {:pokedexid 1 :pokemon_name "Bulbasaur" :type1 "grass" :type2 "poison"}
    {:pokedexid 2 :pokemon_name "Ivysaur" :type1 "grass" :type2 "poison"}
    {:pokedexid 3 :pokemon_name "Venusaur" :type1 "grass" :type2 "poison"}
    {:pokedexid 4 :pokemon_name "Charmander" :type1 "fire" :type2 nil}
    {:pokedexid 5 :pokemon_name "Charmeleon" :type1 "fire" :type2 nil}
    {:pokedexid 6 :pokemon_name "Charizard" :type1 "fire" :type2 nil}])



     

    Insert Data

     

     

  9. You should now find these rows in SQL Server table. You can query it to check the data using following commands.

     

    (j/query db ["SELECT * FROM pokemon"])
     
    (j/query db ["SELECT * FROM pokemon"] {:row-fn :pokemon_name})

     

    Select Query

     

  10. You can delete a row using the following command. Here I am deleting a row where Pokémon Name is Charizard

     

    (j/delete! db :pokemon ["pokemon_name = ?" "Charizard"])


     

    delete row

     

  11. Now if you verify the data using a SELECT, you should see the row got deleted.
(j/query db ["SELECT * FROM pokemon"] {:row-fn :pokemon_name})


verify delete

 

We hope this tutorial helped you to get started with using Progress DataDirect JDBC drivers with Clojure. Feel free to contact us in case of any questions or issues, we will be happy to help.

JDBC TUTORIAL

Connect to SQL Server, Oracle or Postgres from Clojure using JDBC drivers

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers