I get the error "No suitable driver found for" when trying to execute a create table statement on an heroku postgres database. I have postgres, next.jdbc and h2 libraries in deps.edn

seancorfield/next.jdbc    {:mvn/version "1.1.569"}
  com.h2database/h2         {:mvn/version "1.4.200"}
  org.postgresql/postgresql {:mvn/version "42.2.16"}

ns practicalli.database-access
  (:require [next.jdbc :as jdbc])

(def data-source-postgres (jdbc/get-datasource (System/getenv "DATABASE_URL")))

(jdbc/execute!
    data-source-postgres
    ["create table account-holders(
     id int auto_increment primary key,
     name varchar(32),
     email varchar(255))"])

The h2 database works without any issues.

JDBC connection strings are supposed to start with jdbc: according to the spec.

Also, it may be that you need postgresql: rather than postgres: -- the :dbtype "postgres" is an alias for :dbtype "postgresql" and normally you need the full version, not the alias, for connection strings to work...

^ @jr0cket

next.jdbc does nothing to the connection string -- it passes it directly to DriverManager/getConnection so the error is coming directly from JDBC.

Okay, I fixed that to be "jdbc::password@host:5432/db-name" and have som progres, I get an java.netUnknownHostException instead 🙂

Is the connection using SSL by default, or is that something I need to set ?

I don't know. This is not a next.jdbc issue and I don't use PostgreSQL.

Your string above has host in the hostname position, so I would double-check that.

I just tried it locally and it seems like it doesn't accept the //username:password@ part, so I think you either need ?user=username&password=password in your URL, or you'll need to use the 3-arity of next.jdbc/get-connection to pass in username and password...

Or you could pass {:jdbcUrl "jdbc::port/db-name" :user username :password password} as a spec to next.jdbc/get-datasource

I tested both of those approaches locally and they seem to work (I have a PG Docker image running on 127.0.0.1:5432)

Yes, that sounds similar to what I found in stack overflow with java apps... It wont kill me to create a few more environment variables 🙂

This is why I tend to recommend using the db-spec format: {:dbtype ".." :dbname ".." :user ".." :password ".."} -- but I assume you're getting the DB URL directly from the Heroku environment?

(JDBC is such a pain!)

Yes, Heroku provides a DATABASE_URL environment variable, however, I can define others. It seems JDBC_DATABASE_URL is the defacto naming for the connection string with separate username and password variables.

(def db-specification-postgresql
    {:jdbcUrl  (System/getenv "JDBC_DATABASE_URL")
     :user     (System/getenv "JDBC_DATABASE_USERNAME")
     :password (System/getenv "JDBC_DATABASE_PASSWORD")})

  (def data-source-postgresql
    (jdbc/get-datasource db-specification-postgresql))

@jr0cket When I checked the Heroku docs, it sounded like JDBC_DATABASE_URL already includes ?user=...&password=... so you don't need that map format -- just use

(def db-specification-postgresql (System/getenv "JDBC_DATABASE_URL"))

In the example map I gave above, I was assuming the simpler URL format (no query string), which is why I suggested :user and :password along with :jdbcUrl.

Heroku might also rotate the database parameters (I believe). So it wouldn't be a good idea to redefine them

But I might be confusing that with something else

@seancorfield the JDBC_DATABASE_URL does not show in the heroku dashboard as an environment variable, only the DATABASE_URL , but the plan is to create suitable ones (now its working from my local machine). I'll write some code to use it without setting it and see if its just hidden @kevin.van.rooijen yes the connection details can be rotated, I did read that, though it was manual process, but will double check 🙂

The JDBC_DATABASE_URL isn't visible in the dashboard because it's only available for specific buildpacks

Try heroku run echo \$JDBC_DATABASE_URL

Ah, I did mean to look at the heroku Clojure build pack as I'd like to write one specifically for deps.edn projects (rather than the hack I currently use with the Leinginen build pack).

I see, I'm not sure if it'll be visible if you create your own buildpack

FWIW if JDBC_DATABASE_URL doesn't show up, there's also this for converting DATABSAE_URL to JDBC_DATABASE_URL: https://github.com/kwrooijen/clj-database-url

Yes, it worked, nice trick. That saves me creating the env var remotely. Back to re-learning SQL now. Thanks.

@jr0cket If you haven't figured out the SQL problem with your create table statement, I believe the issue is that table names are identifiers which cannot have - in them unless they're quoted. (See https://www.postgresql.org/docs/7.3/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS)