Support LibSQL about goose

[mfridman]

Yikes, probably need to parse the DSN. And no worries, I'm glad you filed an issue, if anything it looks like quite an exciting project.

I still think there's some underlying issue with the driver though, because those intermittent connection closed / stream errors were a little suspect.

[mfridman]

If you can give me the absolute bare minimum repro, and/or error you're running into, I can take a look.

[wagslane]

Yeah for sure! Thanks for the help @mfridman .

Here's the error:

2023/12/07 13:10:28 error ensuring database: error running goose migrations: failed to execute SQL: BEGIN

My assumption is that this is due to the connection string looking like this:

libsql://[your-database].turso.io?authToken=[your-auth-token]

Whereas the SQLite dialect expects a filepath.

Here's the code:

package main

import (
	"database/sql"
	"embed"
	"log"
	"os"

	"github.com/pressly/goose/v3"

	_ "github.com/libsql/libsql-client-go/libsql"
)

//go:embed schema/*.sql
var embedMigrations embed.FS

func main() {
	// https://github.com/libsql/libsql-client-go/#open-a-connection-to-sqld
	// libsql://[your-database].turso.io?authToken=[your-auth-token]
	sqlURL := os.Getenv("SQLURL")
	if sqlURL == "" {
		log.Fatal("No SQLURL set in .env")
	}
	db, err := sql.Open("libsql", sqlURL)
	if err != nil {
		log.Fatal("error opening database: ", err)
	}
	goose.SetBaseFS(embedMigrations)
	if err := goose.SetDialect("sqlite"); err != nil {
		log.Fatal(err)
	}
	if err := goose.Up(db, "sqlc/schema"); err != nil {
		log.Fatal(err)
	}
}

And here's some sql files (though I don't think they matter much in this case):

schema/001_responses.sql

-- name: CreateTableResponses :exec
CREATE TABLE IF NOT EXISTS responses (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    question_id TEXT NOT NULL,
    survey_id TEXT NOT NULL,
    answer TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, question_id)
);

schema/002_users.sql

-- name: CreateTableUsers :exec
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    hashed_ip TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

[mfridman]

I signed up for https://turso.tech, spun up a database, copied the above migrations into a file and ran the following main.go

Expand to see main.go

package main

import (
	"database/sql"
	"embed"
	"log"

	_ "github.com/libsql/libsql-client-go/libsql"
	"github.com/pressly/goose/v3"
)

//go:embed schema/*.sql
var embedMigrations embed.FS

func main() {
	db, err := sql.Open("libsql", "libsql://[my-database].turso.io?authToken=[my-auth-token]")
	if err != nil {
		log.Fatal("error opening database: ", err)
	}
	goose.SetBaseFS(embedMigrations)
	if err := goose.SetDialect("sqlite"); err != nil {
		log.Fatal(err)
	}
	if err := goose.Up(db, "schema"); err != nil {
		log.Fatal(err)
	}
}

The migration files (note the use of -- +goose Up directives):

Migration 1

-- +goose Up
CREATE TABLE IF NOT EXISTS responses (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    question_id TEXT NOT NULL,
    survey_id TEXT NOT NULL,
    answer TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, question_id)
);

Migration 2

-- +goose Up
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    hashed_ip TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

And then ran go run main.go, it applied the migrations:

2023/12/07 20:22:00 OK   00001_a.sql (171.14ms)
2023/12/07 20:22:00 OK   00002_b.sql (158.33ms)
2023/12/07 20:22:00 goose: successfully migrated database to version: 2

... and the tables were created:

But, I did notice when spinning up a new database and immediately trying to apply migrations it sometimes returned errors like the ones you linked above and when using goose.NewProvider similar errors with stream being closed.

2023/12/07 23:24:01 error running migrations: failed to initialize: failed to execute SQL: BEGIN
stream is closed: driver: bad connection; sql: connection is already closed
exit status 1

So, I wonder if there's an issue with the driver itself?

ps. This libsql is kind of a cool project and the turso looks really cool.

[mfridman]

ps. There's a goose.NewProvider, which also works:

Using goose.Provider

package main

import (
	"context"
	"database/sql"
	"log"
	"os"

	_ "github.com/libsql/libsql-client-go/libsql"
	"github.com/pressly/goose/v3"
)

func main() {
	db, err := sql.Open("libsql", "libsql://[my-database].turso.io?authToken=[my-auth-token]")
	if err != nil {
		log.Fatal("error opening database: ", err)
	}
        // Note, can use `embed.FS`, but just showing how also to do it with `os.DirFS`
	provider, err := goose.NewProvider(goose.DialectSQLite3, db, os.DirFS("schema"))
	if err != nil {
		log.Fatal("error creating goose provider: ", err)
	}
	results, err := provider.Up(context.Background())
	if err != nil {
		log.Fatal("error running migrations: ", err)
	}
	for _, r := range results {
		log.Printf("OK   %s (%s)\n", r.Source.Path, r.Duration)
	}
}

[wagslane]

Damn that's crazy... I haven't been able to get it to work once (yeah I did copy pasta and miss the goose up directives, but its the same result with them in)

I'll keep troubleshooting to see if I can find the problem.

[wagslane]

I've found that this error is super generic. If you mangled the connection string you still get the same error.

[wagslane]

Aaaand I found the problem. The issue was a trailing slash in my connection string:

Good: libsql://[your-database].turso.io?authToken=[your-auth-token]

Bad: libsql://[your-database].turso.io/?authToken=[your-auth-token]

So we're all good here! Sorry about the hullabaloo, maybe this helps someone in the future.