Running Console with a SQL database


#1

I can’t connect the Console with a (local) SQL-backed database. The Console starts and shows the ‘hello’ database but after selecting it I’m getting a “Could not find hello//hello in catalog” exception in the Console log.

Any ideas on what should I be doing differently?

Thanks a lot!


To reproduce:

Install and start Postgres, create user and database:

CREATE ROLE sqluser LOGIN PASSWORD 'sqlpwd';
CREATE DATABASE sqldb WITH OWNER = sqluser TEMPLATE template0 ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

Prepare the ‘sqldb’ database for Datomic:

CREATE TABLE datomic_kvs (id text NOT NULL, rev integer, map text, val bytea, CONSTRAINT pk_id PRIMARY KEY (id)) WITH (OIDS = FALSE);
ALTER TABLE datomic_kvs OWNER TO sqluser; GRANT ALL ON TABLE datomic_kvs TO sqluser; GRANT ALL ON TABLE datomic_kvs TO public;

Prepare the transactor configuration:

protocol=sql
host=localhost
port=4334
license-key=....
sql-url=jdbc:postgresql://localhost:5432/sqldb
sql-user=sqluser
sql-password=sqlpwd
sql-driver-class=org.postgresql.Driver

Start the transactor:

transactor -Xmx4g $(pwd)/transactor.properties

Create the ‘hello’ database using repl:

(require '[datomic.api :as d])
(def db-uri "datomic:sql://hello?jdbc:postgresql://localhost:5432/sqldb")
(d/create-database db-uri)

Start the console

console -p 8080 dev datomic:sql://hello?jdbc:postgresql://localhost:5432/sqldb?user=sqluser\&password=sqlpwd
    Console started on port: 8080
    dev = datomic:sql://hello?jdbc:postgresql://localhost:5432/sqldb
    Open http://localhost:8080/browse in your browser (Chrome recommended)

Select the ‘hello’ database in the browser:

java.lang.RuntimeException: Could not find hello//hello in catalog
    at datomic.peer$get_connection$fn__10699.invoke(peer.clj:681)
    at datomic.peer$get_connection.invokeStatic(peer.clj:669)
    at datomic.peer$get_connection.invoke(peer.clj:666)
    at datomic.peer$connect_uri.invokeStatic(peer.clj:751)
    at datomic.peer$connect_uri.invoke(peer.clj:743)
    at clojure.lang.Var.invoke(Var.java:381)
    at datomic.Peer.connect(Peer.java:106)
    at datomic.api$connect.invokeStatic(api.clj:15)
    at datomic.api$connect.invoke(api.clj:13)
    at datomic.console$db.invokeStatic(console.clj:51)
    at datomic.console$db.invoke(console.clj:49)
    at datomic.console.BrowseUI$create_main_layout$db__1449.invoke(BrowseUI.clj:185)
    at datomic.console.BrowseUI$create_main_layout$fn__1509.invoke(BrowseUI.clj:337)
    at datomic.console.uihelp$add_vc$reify__587.valueChange(uihelp.clj:31)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:508)
    at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:198)
    at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
    at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:969)
    at com.vaadin.ui.AbstractField.fireValueChange(AbstractField.java:1127)
    at com.vaadin.ui.AbstractField.setValue(AbstractField.java:542)
    at com.vaadin.ui.AbstractSelect.setValue(AbstractSelect.java:702)
    at com.vaadin.ui.ComboBox.changeVariables(ComboBox.java:664)
    at com.vaadin.server.communication.ServerRpcHandler.changeVariables(ServerRpcHandler.java:396)
    at com.vaadin.server.communication.ServerRpcHandler.handleBurst(ServerRpcHandler.java:221)
    at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:111)
    at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:91)
    at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:37)
    at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1382)
    at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:238)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:835)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1158)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1090)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:119)
    at org.eclipse.jetty.server.Server.handle(Server.java:517)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:242)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
    at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
    at java.lang.Thread.run(Thread.java:748)

#2

Hi @infinitary What version of Datomic Pro are you using here? After you create the DB at the REPL are you able to connect and transact using that URI?

(def conn (d/connect db-uri))

(def first-schema [{:db/ident :test/title
                           :db/valueType :db.type/string
                           :db/cardinality :db.cardinality/one
                           :db/doc "The title of a test"}])

@(d/transact conn first-schema)

Thanks,
Jaret


#3

Hi Jaret, thanks for the response. And fyi, I’m new to Datomic :slight_smile:

My version is datomic-pro-0.9.5786.

I can connect to the database and transact with the Java shell using the URI “datomic:sql://hello?jdbc:postgresql://localhost:5432/sqldb?user=sqluser&password=sqlpwd”.

Another issue seems to be that I fail to connect from repl (creating worked, see above), when opening the URI (with or without ?user…) I get: ArityException Wrong number of args (1) passed to: api/eval229/fn–230/G–216–237 clojure.lang.AFn.throwArity (AFn.java:429).

Still, the database is up and I can transact/query it from the Java shell and also from my own Java app with the peer library.


#4

Console should be started with a storage URI, not a specific database URI:
https://docs.datomic.com/on-prem/console.html#starting-the-console

(bin/console -p port alias transactor-uri-without-db [alias transactor-uri-without-db]+)

You are including your database name when you start console, thus the hello//hello

-Marshall


#5

Thank you Marshall, sorry for not reading the doc properly.

However, starting the Console with

console -p 8080 sql datomic:sql://localhost:4334/

I get a “:db.error/invalid-sql-connection Must supply jdbc url in uri, or DataSource or Callable in protocolObject arg to Peer.connect trying to connect to datomic:sql://localhost:4334, make sure transactor is running” when trying to load the Console in the browser. The ‘sql’ word shows in Storage but the DB dropdown does not have any entries.


#6

You still need to use a datomic URI, just leave off the db name.


#7

Wow, cool, that could be one step closer…

console -p 8080 sql datomic:sql://jdbc:postgresql://localhost:5432/sqldb?user=sqluser\&password=sqlpwd

gives: “No suitable driver trying to connect to datomic:sql://jdbc:postgresql://localhost:5432/sqldb?user=sqluser&password=sqlpwd, make sure transactor is running”

PS: also happens if I manually add a postgres jar to the class path in the console launcher shell script
PPS: same for all working combinations of “datomic:sql://localhost:4334/jdbc:postgresql://localhost:5432/sqldb?user=sqluser&password=sqlpwd”


#8

I believe you still need the ? in the URI (which separates db name from the storage address):

datomic:sql://?jdbc:postgresql://localhost:5432/sqldb?user=sqluser&password=sqlpwd


#9

Thank you Marshall, indeed, that fixed it, Console runs against the local transactor that way :+1:

Just one more thing, to connect the Console with a foreign installation I’d still need to add the transactor location into the URI - testing on the same configuration, on the localhost, extending the above URI to:

datomic:sql://localhost:4334/?jdbc:postgresql://localhost:5432/sqldb?user=sqluser&password=sqlpwd

the Console loads but selecting the ‘hello’ database gives a “Could not find transactor:4334/hello in catalog” exception in the Console log. I can get by with the above already, but if I could make this URI work as well, life would be perfect again :smile:


#10

The database URI does not contain the transactor address. It is only the address to storage.
The peer discovers the address of the active transactor by reading it from storage (https://docs.datomic.com/on-prem/deployment.html#getting-connected).

The URI you use to launch console should just be the remote storage URI (without a db name), regardless of where your transactor is running.


#11

Thank you soooo much, also for your patience :angel:

Replacing localhost with the remote database host makes the Console connect to the remote installation. However, I had to make my remote transactor hostname resolve on my local machine via /etc/hosts so that the local Console could also reach it (when selecting the actual database in the user interface), but then it worked fine!

Thanks again!