JDBC support

[penberg]

No description provided.

[manakbisht]

Hi, I have looked into this. Ideally, we would want to have something like https://github.com/xerial/sqlite-jdbc for libsql. I tried using the same jar with some modifications to support libsql, but I don't think that is viable, especially because libsql also runs in standalone mode.
I would be interested in writing a driver. I think we should first start with a POC for the sqld server, and figure out an embedded version later. I am experienced with Java and SQL databases, however, not with database internals, so would require some help on that front.

[haaawk]

Thanks @manakbisht. If you want you can start with the http only libsql jdbc driver. It would translate JDBC calls into http requests to sqld.

Here's the sqld http api specification:
https://github.com/libsql/sqld/blob/main/docs/HTTP_V2_SPEC.md
https://github.com/libsql/sqld/blob/main/docs/HTTP_V1_SPEC.md
https://github.com/libsql/sqld/blob/main/docs/HRANA_3_SPEC.md
https://github.com/libsql/sqld/blob/main/docs/HRANA_2_SPEC.md
https://github.com/libsql/sqld/blob/main/docs/HRANA_1_SPEC.md

Please let me know if you have any questions.

[manakbisht]

@haaawk sqld supports both HTTP/1.1 & /2. However, the server timeouts when I try to use HTTP/2. Here's an example snippet -

HttpRequest request = HttpRequest.newBuilder()
        .uri(new URI("http://localhost:8080/v2"))
        .timeout(Duration.of(10, ChronoUnit.SECONDS))
        .GET()
        .version(HttpClient.Version.HTTP_1_1) // replacing with HTTP_2 causes an HttpTimeoutException
        .build();

HttpResponse<String> response = HttpClient.newHttpClient()
        .send(request, HttpResponse.BodyHandlers.ofString());

System.out.println(response.statusCode());
System.out.println(response.body());

This is the sqld server log -
ERROR sqld::h2c: http2 connection error: http2 error: connection error detected: unspecific protocol error detected

Took me a while to figure out because 2 is the default. Does this have to do with Java or sqld? Am I missing some config?

[haaawk]

v1 and v2 in the path are not versions of HTTP protocol but versions of HRANA protocol. It's an aplication level protocol that we use.

[haaawk]

Regarding support for HTTP/2. I believe sqld should support it so it's worth investigating why it does not work for you.

[haaawk]

This must be something specific to your local setup. I tried curl to the managed instance and it works using HTTP/2:

╰──> curl -v https://tests-haaawk.turso.io/v2
*   Trying 66.241.124.14:443...
* Connected to tests-haaawk.turso.io (66.241.124.14) port 443 (#0)
* ALPN: offers h2,http/1.1
* (304) (OUT), TLS handshake, Client hello (1):
*  CAfile: /etc/ssl/cert.pem
*  CApath: none
* (304) (IN), TLS handshake, Server hello (2):
* (304) (IN), TLS handshake, Unknown (8):
* (304) (IN), TLS handshake, Certificate (11):
* (304) (IN), TLS handshake, CERT verify (15):
* (304) (IN), TLS handshake, Finished (20):
* (304) (OUT), TLS handshake, Finished (20):
* SSL connection using TLSv1.3 / AEAD-AES256-GCM-SHA384
* ALPN: server accepted h2
* Server certificate:
*  subject: CN=tests-haaawk.turso.io
*  start date: Aug 29 11:42:48 2023 GMT
*  expire date: Nov 27 11:42:47 2023 GMT
*  subjectAltName: host "tests-haaawk.turso.io" matched cert's "tests-haaawk.turso.io"
*  issuer: C=US; O=Let's Encrypt; CN=R3
*  SSL certificate verify ok.
* using HTTP/2
* h2 [:method: GET]
* h2 [:scheme: https]
* h2 [:authority: tests-haaawk.turso.io]
* h2 [:path: /v2]
* h2 [user-agent: curl/8.1.2]
* h2 [accept: */*]
* Using Stream ID: 1 (easy handle 0x7f9082810a00)
> GET /v2 HTTP/2
> Host: tests-haaawk.turso.io
> User-Agent: curl/8.1.2
> Accept: */*
> 
< HTTP/2 200 
< content-type: text/plain
< content-length: 44
< access-control-allow-origin: *
< vary: origin
< vary: access-control-request-method
< vary: access-control-request-headers
< date: Mon, 16 Oct 2023 06:55:15 GMT
< server: Fly/55f8eaa0 (2023-10-09)
< via: 2 fly.io
< fly-request-id: 01HCVM8XHVFXEPNP1QNMFSSDRY-waw
< 
* Connection #0 to host tests-haaawk.turso.io left intact
Hello, this is HTTP API v2 (Hrana over HTTP)% 

[manakbisht]

v1 and v2 in the path are not versions of HTTP protocol but versions of HRANA protocol. It's an aplication level protocol that we use.

I understand that. I was not referring to the HRANA protocol. HttpClient.Version.HTTP_1_1 & HttpClient.Version.HTTP_2 in the Java code refer to the HTTP protocol. The documentation section I linked mentions the following,

Hrana over HTTP runs on top of HTTP. Any version of the HTTP protocol can be used.

This according to me implies, both HTTP/1.1 and HTTP/2. Am I misunderstanding this?

Took me a while to figure out because 2 is the default.

I was referring to the Java HttpClient here.

This must be something specific to your local setup.

Possibly. I am unable to use HTTP/2 with curl as well. So, the Java code is not the problem here.

$ curl --http1.1 http://localhost:8080/v2
Hello, this is HTTP API v2 (Hrana over HTTP)% 
$ curl --http2 --max-time 10 http://localhost:8080/v2
curl: (28) Operation timed out after 10005 milliseconds with 0 bytes received

The only difference I see here is that I am running the instance locally.

[haaawk]

You also run http vs https but it might be some flag on the sqld that you're missing. I will check if there's any relevant flag we're using on turso platform.

[serge-rider]

Hi,

We just released JDBC driver for LibSQL/Turso: https://github.com/dbeaver/dbeaver-jdbc-libsql (Apache license)

It is a general purpose JDBC, in version 1.0 it supports most basic JDBC features. Currently we use it in DBeaver and CloudBeaver for LibSQL but perhaps it worth mentioning in the list of community SDKs.