2023-08-03 sql | Clojure Slack Archive

sql

Andrew Carlile 2023-08-03T18:14:25.238399Z

hello, wizards! I'm having some confusion related to setting up a table with postgis. I'm using the postgis/postgis docker image thus:

docker network create ${POSTGRES_NETWORK}
docker run --name ${POSTGRES_DBNAME} --network ${POSTGRES_NETWORK} -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -d postgis/postgis
docker run -it --rm --network ${POSTGRES_NETWORK} postgis/postgis psql -h ${POSTGRES_DBNAME} -U postgres

(the values POSTGRES_NETWORK, POSTGRES_DBNAME, and POSTGRES_PASSWORD are all defined in a .env file and duplicated in a .edn file so that I can read in the env at runtime. right now I just host the clj server locally and have been testing the connection with lein repl . I've made sure to stop/rm any other containers (I'm not doing anything else at the moment besides setting up this db/server) when I attempt to query my database with clojure.java.jdbc I apparently am not communicating with the db I set up:

(def db-spec {:dbtype "postgresql", :dbname "my-db", :user "postgres", :password "password"})
(db-do-commands
 db-spec 
 (create-table-ddl
  :foo [[:id :int "generated always as identity"]
        [:name :varchar]])) ;; => (0)
(db-do-commands
 db-spec (create-table-ddl
          :bar [[:id :int "generated always as identity"]
                  [:name "varchar(32)" :not :null]
                  [:description :text]
                  [:location "geometry(Point, 4326)"]])) ;; =>Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2440).
; ERROR: type "geometry" does not exist

and when I try the queries in psql cli:

select * from foo;
ERROR:  relation "foo" does not exist

create table bar(id int generated always as identity, name varchar, geo geometry(Point, 4326));
insert into bar(name, geo) values('hello', ST_GeomFromText('POINT(41.870581453671896 -87.81999167672964)', 4326));
INSERT 0 1

but then I try to get that point in my repl:

(jdbc/with-db-connection [conn db-spec]
  (query db-spec ["Select * from bar"])
) ;; => ()

It seems pretty clear to me that my REPL may be talking to some postgresql db, but not the one I stipulated when setting up my docker image. What am I missing to make sure the right connection is made? P.S. any tips on making my workflow more idiomatic appreciated

valtteri 2023-08-04T07:08:53.272489Z

You can mount initialization scripts (`.sql` or .sh ) under /docker-entrypoint-initdb.d directory. Those will be executed when the container starts. Here’s an example bash script https://github.com/lipas-liikuntapaikat/lipas/blob/master/db/init-db.sh

valtteri 2023-08-04T07:11:48.558749Z

Same can be achieved also programmatically from Clojure. This is how I setup a new postgis database for tests https://github.com/lipas-liikuntapaikat/lipas/blob/master/webapp/test/clj/lipas/backend/handler_test.clj#L74-L95

Andrew Carlile 2023-08-04T17:04:18.792449Z

another solution was to amend the container generation script thus:

docker run --name ${POSTGRES_DBNAME} -p ${POSTGRES_PORT}:5432 --network ${POSTGRES_NETWORK} -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -e POSTGRES_DB=${POSTGRES_DBNAME} -d postgis/postgis

the addition of -e POSTGRES_DB=${POSTGRES_DBNAME} added the steps:

CREATE DATABASE ${POSTGRES_DBNAME};
\c ${POSTGRES_DBNAME};
CREATE EXTENSION postgis;

👍 1

valtteri 2023-08-03T19:21:09.818799Z

Hi, you are not specifying host in your db-spec. Usually you map some port from the container to your localhost so you can access the db in the container via localhost:<someport>

valtteri 2023-08-03T19:22:19.139059Z

e.g. docker run -p 54321:5432 … would map port 54321 on your localhost to postgres default port (5432) in the container

valtteri 2023-08-03T19:22:40.054219Z

Then in your REPL you could access the db with host localhost and port 54321

valtteri 2023-08-03T19:24:15.603409Z

I know you can get all fancy with docker + networking, but this is the least convoluted way and usable for local dev. 🙂

Andrew Carlile 2023-08-03T20:56:57.797479Z

thanks for the reply. I ran docker stop/rm on my test container and started it again with the updated command:

docker run --name ${POSTGRES_DBNAME} -p ${POSTGRES_PORT}:5432 -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -d postgis/postgis

I checked the ports docker is using:

docker port ${POSTGRES_DBNAME}
5432/tcp -> 0.0.0.0:54321
5432/tcp -> [::]:54321

then attempted to connect with the updated db-spec :

{:dbtype "postgresql", :dbname "streats", :user "postgres", :port 1337, :host "localhost", :password "password"}

and am still getting the same behavior: can connect to a postgres instance, and can read/write, but it is not really linked to the postgis instance i set up above, and cannot work with "geometry" types in the repl.

Andrew Carlile 2023-08-03T21:40:35.550479Z

I was able to connect to the psql instance with the following command:

psql -h localhost -p ${POSTGRES_PORT} -U postgres -d ${POSTGRES_DBNAME} -W

when I queried this db select * from bar I got the expected output from the work i did in the repl. however, i also get this:

postgres=# SELECT postgis_version();
ERROR:  function postgis_version() does not exist

it looks like you have to create the postgis extension even if you are building a container off of the postgis image. it looks like postgis may be enabled for the whole psql instance:

postgres=# create extension postgis;
ERROR:  extension "postgis" already exists

but not specifically for my db:

my-db=# create extension postgis
CREATE EXTENSION

so I guess my question now is: how do I ensure my working database is also postgis-enabled, preferably on startup?

Clojurians Log v2

sql