Support CrateDB

[computer-8256]

https://cratedb.com/

https://cratedb.com/docs/crate/clients-tools/en/latest/

https://cratedb.com/docs/crate/reference/en/latest/interfaces/postgres.html

[drizk1]

CrateDB has Postgres Support - but I had to make one small additional change to get it to work so to use it you will first need to run since I have not released this version yet (if it works for you I will tho).

using Pkg; Pkg.add(url = "https://github.com/TidierOrg/TidierDB.jl", rev = "add-show_tables")

Then the following.

using TidierDB
using LibPQ

To connect, you can use TidierDB.connect as I did here. If you use LibPQ.connect to connect, you will need to run set_sql_mode(postgres()) after. otherwise, it proceeds as follows.

conn = connect(postgres(),  
               host="rose-raymus-more-words-east-1.aws.cratedb.net", 
               dbname="crate", 
               user="admin", 
               password="password here")

show_tables(conn)
5×1 DataFrame
 Row │ table_name           
     │ String?              
─────┼──────────────────────
   1 │ alembic_version
   2 │ jwt_refresh_token
   3 │ scheduled_jobs
   4 │ scheduled_jobs_log
   5 │ scheduled_jobs_state

@chain db_table(conn, "gc.alembic_version") begin
    @collect
end
1×1 DataFrame
 Row │ version_num  
     │ String?      
─────┼──────────────
   1 │ 5a8c37849dcd

I set up an account and the above was successful for me, so let me know if this works for you! If not, we will find away.

[drizk1]

@computer-8256 I tried connecting with duckdb

julia> connection_string = "dbname=crate user=admin host=rose-raymus-antilles.eks1.us-east-1.aws.cratedb.net 
"dbname=crate user=admin host=string password=string"
julia> DuckDB.query(con, "LOAD postgres;")
(Success = Bool[],)
julia> DuckDB.query(con, "ATTACH '$connection_string' AS postgres_db (TYPE POSTGRES);")
(Success = Bool[],)

but eventually run into the following error,

Execute of query "SELECT * FROM postgres_db.doc.mtcars LIMIT 3;" failed: Invalid Error: Failed to prepare COPY "
        COPY (SELECT "model", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb" FROM "doc"."mtcars" ) TO STDOUT (FORMAT binary);
        ": ERROR:  line 2:7: no viable alternative at input 'COPY ('
CONTEXT:  io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:211)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:200)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:188)
io.crate.protocols.postgres.PostgresWireProtocol.handleSimpleQuery(PostgresWireProtocol.java:789)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchMessage(PostgresWireProtocol.java:340)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchState(PostgresWireProtocol.java:330)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:298)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:282)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)

I think at this time, because CrateDB is not fully compatible with all PostgreSQL features or extensions it does not work with the DuckDB postgres support.

So the most straightforward way for the time to use TidierDB with CrateDB would be via LibPQ.

[computer-8256]

Yes, duckdb doesn't support cratedb. I created a feature request on their repo. https://github.com/duckdb/duckdb/discussions/13461

[computer-8256]

For now, I use it with LibPQ.

[computer-8256]

@drizk1 using add-show_tables worked.

[drizk1]

Since it is working for you and in the latest release I will close this. Feel free to re-open or start a new issue if other things come up

[computer-8256]

related issue: https://github.com/crate/crate/issues/16594

https://github.com/crate/crate/issues/12952

[drizk1]

Thanks for adding these. I'll keep an eye on them, but if movement happens and they close it it and duckdb becomes an option and I don't realize, do let me know so we can make sure duckdb and crate are supported here when possible