Sqldelight: Is it possible to support other SQL dialects?

[Wavesonics]

Being able to support MySQL or Postgres would be an awesome feature for non-mobile projects.

Would this be easy to accomplish with sqldelight? Or are things pretty tightly coupled to Sqlite?

[AlecStrong]

we definitely intend on doing this. There isn't a ton of adjustments needed to make it work but right now it is strictly tied to sqlite, but only the dialect not the engine itself.

The sample sqlite driver we provide here: https://github.com/square/sqldelight/tree/master/drivers/sqlite-driver is actually just a wrapper around a jdbc sqlite driver (xerial). It would be possible to instead use a MySQL/Postgres jdbc driver with the same wrapper and then run the apis we generate. You'd be restricted to SQLite syntax (which is kind of a subset of SQL) though so it's more a hack than an actual feature at the moment

[Wavesonics]

Well great to know it's on your minds, I would love to use this in other environments.

[ScottPierce]

It looks like Xerial supports several database types, including MySql and Postgres. Supporting this might be fairly trivial.

[AlecStrong]

yea the driver side is fairly easily covered, but we have to switch out the dialect going in because we currently wouldnt support something like

CREATE TABLE stuff (
  text VARCHAR(20)
);

[AlecStrong]

(which also shouldnt be insanely hard, just not focusing on it till post-1.0)

[dibog]

+1 for Postgres and HSQLDB

[dibog]

In case you support additional databases: Would you also support datasources to be able to use the SQLDelight in a multi-thread context?

[AlecStrong]

we've set it up so that multithreading is up to the driver, so yea shouldnt be a problem

[GianfrancoMS]

Any updates on this issue? I would love to use sqldelight on the backend.

[AlecStrong]

i have begun the process of thinking deeply about it

[AlecStrong]

holy fuck 22 thumbs up

[ScottPierce]

Lol. I'd prefer using something like this to interact with databases on a server than all the crappy ORMs that are out there. I think a lot of people feel the same.

[NikkyAI]

I wonder if it would be possible to speed up this issue somehow, bugbounty or such ?

[AdityaAnand1]

Can anyone recommend a good library (ORM or not) for dealing with databases with a Kotlin backend? (Exposed doesn't seem very mature and there aren't a lot of others built for Kotlin libraries for this)

[meoyawn]

https://mybatis.org/mybatis-3/ it's like https://developer.android.com/training/data-storage/room/index.html but reflection-based and very mature

Use it in https://listenbox.app/ with ktor (1.4k MAU), couldn't find anything better on the JVM

[ScottPierce]

@AlecStrong Do you have any updated thoughts on this a year later? This issue is at 66 thumbs up now.

[AlecStrong]

i'd like to incrementally get this working. Goal right now is for some semblance of MySQL support - I'll start with adding column types but if there's other parts of MySQL's dialect which aren't in SQLite let me know and I can focus on that before the next release

[AlecStrong]

with 1.3.0 you can enable mysql as follows

sqldelight {
  MyDatabase {
    dialect = "mysql"
  }
}

// Use the jdbc driver to power it, requires an overriding class that provides a JDBC connection
dependencies {
    implementation "com.squareup.sqldelight:jdbc-driver:1.3.0"
}

[Savrov]

@AlecStrong sorry, for my incompetence in this field, but does it mean that a developer can write SQL queries in MySQL-manner (using some MySQL special functions)? And how is it possible to connect to an external MySQL database. Thank you.
P.S. I would like to use SqlDelight within my apps and backend server with a shared infrastructure layer. As I suppose there should be the only difference in setup (like connecting to MySQL database) and importing inside build.gradle files.

[LouisCAD]

@Savrov To connect to an external MySQL database, you need to configure the JDBC driver and connection accordingly in your code. This should be no different that without SqlDelight and only JDBC for the connection part.

[travisfw]

How can I contribute to SqlDelight for Postgres?

[AlecStrong]

to start there needs to be a grammar for postgres which starts overriding rules, similar to how we've done for mysql: https://github.com/AlecStrong/sql-psi/tree/master/core/src/main/kotlin/com/alecstrong/sql/psi/core/mysql

thats the hard part, the rest is easy! I'm focused on mysql/sqlite support and would absolutely love if postgres was contributed

[veyndan]

I've been using a fairly trivial implementation of the PostgreSQL dialect for a side project. The grammar only consists of the column types that I've been using. I'll polish it up and try to make a PR sometime over the weekend ✌️

[AlecStrong]

amazing - yea the goal isn't to have complete grammars off the bat but build stuff out as it comes up

[AlecStrong]

Alright I'm CLOSING THIS ONE OUT. We'll have 1.4.0 out soon which has mysql/postgres/h2 support in varying levels of usability, mysql having the most at the moment. It'll be documented more in that release, and from there we should have separate issues for unsupported features of those dialects. THANKS ALL ENJOY

[AlecStrong]

if you want to use it today target the snapshots and use the gradle flag

sqldelight {
  MyDatabase {
    dialect = "mysql" // or postgresql, hsql
  }
}