Fork me on GitHub
#honeysql
<
2021-03-04
>
Scott Starkey21:03:29

Hi all - I have set up a postgresql server with my web host provider (Bluehost) and I’m trying to figure out the steps to basic database access. Is there a “Hello world!” guide to how to get set up with HoneySQL (or any other SQL)? I’ve set up a database and a user, but I’m a little lost on how to connect to it.

seancorfield21:03:07

HoneySQL just turns Clojure data into SQL. It doesn't have anything to do with actually connecting to a database.

seancorfield21:03:39

You need to use next.jdbc to actually run any SQL against a database.

seancorfield21:03:44

You would generally define a hash map that describes your database setup: {:dbtype "postgres" :dbname "whatever" :user "dbauser" :password "secret" :host "ip-or-hostname"}

seancorfield21:03:35

Then you can run SQL against the DB via (next.jdbc/execute! db-spec (sql/format {:your :honeysql :data :here}))

Scott Starkey21:03:16

Hi Sean and others. I’m starting to take a hack at this, and I appreciate any help you could provide. I’ve done the basics at the link above, and got the following error message:

db-hello-world.core> (def ds (jdbc/get-datasource testmap))
;; => #'db-hello-world.core/ds
db-hello-world.core> (jdbc/execute! ds ["
create table address (
  id int auto_increment primary key,
  name varchar(32),
  email varchar(255)
)"])

Execution error (SQLException) at java.sql.DriverManager/getConnection (DriverManager.java:689).
No suitable driver found for ***My IP Address***:***My port number***/***My db  name***
I’m not sure whether this is an error with my host provider giving me the wrong IP/port info, or if this is a problem with something else. “No suitable driver” makes it sound like I’m missing a driver! Can you help?

seancorfield21:03:47

What is your testmap?

seancorfield21:03:27

That error generally means you have not specified a valid :dbtype

seancorfield21:03:23

@scotto You're using PostgreSQL? Hosted somewhere non-local?

Scott Starkey21:03:48

Yeah, I want to host it at my hosting provider (bluehost). Eventually I would like it to be a web app.

seancorfield21:03:27

So I would expect something like this in your testmap: {:dbtype "postgres" :dbname "..." :user "..." :password "..." :host "..."} (assuming a default port for PostgreSQL which is 5432)

Scott Starkey21:03:56

My testmap is

{:dbtype "postgres",
 :dbname "", ; redacted
 :user "", ; redacted
 :password "", ; redacted 
 :host "162.241.216.221", ; The ip number that Bluehost told me.
 :port 3306} ; The port number that Bluehost told me.

seancorfield21:03:17

3306 is normally MySQL, not PostgreSQL.

Scott Starkey21:03:35

They might have given me wrong info.

seancorfield21:03:43

In your project setup, do you have the appropriate database driver added as a dependency?

seancorfield21:03:11

Per the Getting Started docs: "In addition, you will need to add dependencies for the JDBC drivers you wish to use for whatever databases you are using."

Scott Starkey21:03:37

:dependencies [[org.clojure/clojure "1.10.3"]
                 [seancorfield/next.jdbc "1.1.613"]]
I think so… ^

seancorfield21:03:51

No, you have no JDBC drivers there.

seancorfield21:03:33

The docs link to this part of the next.jdbc project https://github.com/seancorfield/next-jdbc/blob/develop/deps.edn#L10-L27 which shows a list of JDBC driver dependencies.

Scott Starkey21:03:36

Oh… sorry. Newbie…

seancorfield21:03:25

As you said “No suitable driver” makes it sound like I’m missing a driver! Can you help? -- so you were correct that you are missing a driver.

Scott Starkey21:03:33

When this is fixed, I will buy you the beverage of your choice. 🙂 :thumbsup:

seancorfield21:03:48

PostgreSQL: org.postgresql/postgresql {:mvn/version "42.2.10"}

seancorfield21:03:59

MySQL: mysql/mysql-connector-java {:mvn/version "8.0.19"}

seancorfield21:03:26

Those aren't quite the latest versions but they should be recent enough for you.

seancorfield21:03:27

If Bluehost is really offering you MySQL instead of PostgreSQL, you want :dbtype "mysql" instead of :dbtype "postgres" (and you can omit :port 3306 since that is the default)

Scott Starkey21:03:32

I’m using Leiningen, so I assume I need: [org.postgresql/postgresql "42.2.18.jre7"]

seancorfield21:03:10

Are you using Java 7 or a more recent JVM?

Scott Starkey21:03:59

java version "1.8.0_201"

seancorfield21:03:32

Scroll down and you'll see the Leiningen dep on the right hand side: [org.postgresql/postgresql "42.2.19"]

Scott Starkey21:03:49

Again. Thank you so much!

seancorfield21:03:50

The .jre7 and .jre6 versions are for older JVMs.

seancorfield21:03:21

If you actually are connecting to MySQL, this is the latest version I recommend: https://search.maven.org/artifact/mysql/mysql-connector-java/8.0.22/jar (there's an 8.0.23 but it has a number of changes that can be a bit problematic, depending on your setup so I'd say stick to 8.0.22 for now).

Scott Starkey21:03:18

Thank you, kindly!