Add libSQL support to SQLite generator

[plunkettscott]

In v0.29.0 you removed support for Atlas, which was allowing us to use Bob with libSQL/Turso (a database not directly supported by Bob). Since Bob is now focused on each database implementation, rather than schema tooling, supporting libSQL would allow us to use the generator directly on our database. To get around the removal in v0.29.0 we have to export our schema from libSQL to a local file to be read by Bob, which works, but adds unnecessary steps to generating code.

The libSQL project is a fork of SQLite, so nothing aside from the driver should need to change to support libSQL. The dialects are identical from a schema perspective.

[stephenafamo]

I'll look into this

[plunkettscott]

Thanks @stephenafamo !

I'm willing to work on this as well if you give me some direction as to how you'd want this configurable. The way most projects do this is by supporting various connection strings for SQLite, things like file:// for a standard SQLite file and libsql:// for libsql.

Perhaps something like this:

sqlite:
  dsn: 'libsql://my-libsql-url'

And perhaps if it does not contain a scheme or the scheme is file:// we could default to SQLite using the current modernc driver? The following being equivalent:

sqlite:
  dsn: "schema.sqlite"

sqlite:
  dsn: "file://schema.sqlite"

[stephenafamo]

This would be a good way to do it.
If the dsn starts with libsql://, then use the libsql driver.

The other concerns would be

1. Make sure to use the non-CGO driver.
2. How best to test this libsql driver? Or do we only test SQLite and assume that libsql will remain 100% compatible?

[stephenafamo]

Another thing I have just noticed.

The current sqlite driver supports attaching databases, and the existing test tries to do this.
However, attaching databases in libsql works slightly differently and has to be a remote database, attached within a transaction https://docs.turso.tech/features/attach-database. This may cause an error when trying to test.

[plunkettscott]

Yeah, we don't currently use ATTACH, but that could be a use case to watch out for. Because of the way you've structured config for ATTACH in the generator, I don't think that will be much of a hurdle. You can also operate a full sqld server (the actual server component of LibSQL) locally or in CI using Docker. I would imagine this would be as easy to spin up and seed with data for testing as PostgreSQL and other database engines currently being tested.

Here's a minimal Docker compose to spin up a sqld instance for your reference:

services:
  sqld:
    image: ghcr.io/tursodatabase/libsql-server:v0.24.27
    environment:
      - SQLD_NODE=primary
      - SQLD_ADMIN_LISTEN_ADDR=0.0.0.0:8082
    ports:
      - "8080:8080"
      - "8082:8082"
    command:
      - "/bin/sqld"
      - --enable-namespaces
      - --disable-default-namespace
      - --enable-http-console

From there, you can create databases for testing like so:

POST http://localhost:8082/v1/namespaces/schema/create

Accept: application/json
Content-Type: application/json

{
  "namespace": "a-namespace"
}

The connection string for that database would roughly be:

libsql://a-namespace.localhost:8080

Creating a second database, you could attach the above database using ATTACH.

ATTACH a-namespace

Does that help you understand how this could be tested? Instead of providing a file path, you provide the other database ID to attach to. The database ID in sqld is the namespace. You'd need to have both databases created before attempting to test and any necessary schemas applied as well.

Let me know if anything isn't clear, I'm happy to provide more context.

[plunkettscott]

This would be a good way to do it. If the dsn starts with libsql://, then use the libsql driver.

The other concerns would be

1. Make sure to use the non-CGO driver.

2. How best to test this libsql driver? Or do we only test SQLite and assume that libsql will remain 100% compatible?

1. Yeah, no reason to use the CGO driver. The CGO version is really only necessary if you're testing embedded replicas, but that's a runtime concern and not a schema concern as the schema is the same in that case.

2. I answered how to test this previously, but aside from how ATTACH works behind the scenes and that one is remote and one is a file, there really are not currently differences in the overall schema or dialect. It would likely be best to test with sqld though to ensure the driver works as expected in the generator.

[stephenafamo]

This is great... I think this should be fairly straightforward then.

1. Use the (non-CGO) libsql driver if the dsn begins with libsql://
2. Test the libsql driver in CI with sqld.