Allow `turso dev` to persist to local file

[arjunyel]

In some environments such as Cloudflare Workers you can't use file: URLs because they don't let you use the filesystem.

Instead, we could connect to turso dev running locally and have an option to persist to a local LibSQL file such as file:somefile.db. This would be an optional alternative to the current in-memory support.

Fixes https://github.com/libsql/libsql-client-ts/issues/21

[CodingDoug]

There are also a number of other ways to start a local sqld, which you can read about in the docs for sqld:

https://github.com/libsql/sqld/blob/main/docs/BUILD-RUN.md

They all persist data in a file by default and you can connect to them using a localhost URL. You can also work with the database file using the sqlite3 command line.

[arjunyel]

Sorry, what's the difference between turso dev and running sqld?

[CodingDoug]

They are running the same software, generally speaking, but you get more control over how sqld operates if you use it directly. It seems to me that you're looking for persistence, which is exactly what you get by default if you run sqld today using one of the documented procedures. You can also choose to upgrade or downgrade at will in order to match what you have deployed, or simply experiment with what's new. sqld moves quickly, so you might want to move quickly with it.

With turso dev, currently, you only have the option of hosting an in-memory database, and only the version of sqld that comes with it (which is hidden from view). As you can see from the output, it is also an experimental feature of the CLI.

[glommer]

you do have the option of running a local file with turso dev by passing the option --local-file. But as Doug mentioned, it is experimental and won't support interactive transactions.

We are moving interactive transactions over pure HTTP instead of WebSockets, so we'll not touch turso dev until that gets done.

[ben-xD]

It seemed important to run migrations on the local database, to set up the data? I launched the in-memory database with turso dev, then tried turso db shell http://localhost:3030, but any command, e.g. select * from users; gave an error:

Error: Binary was compiled with 'CGO_ENABLED=0', go-sqlite3 requires cgo to work. This is a stub

If the database was persisted, I suspect it would be pretty simple to edit it. I'll take a look at sqld, thanks!

---

My application can't use the file directly, given that it runs in Cloudflare Workers.

---

you do have the option of running a local file with turso dev by passing the option --local-file.

turso dev --local-file=dev.sqlite doesn't create any files. I'm also not sure if turso will reload the file if the file changes.

When I try to connect to the database over using @libsql/client using libsql://localhost:3030, I get an error:

[0] LibsqlError: HRANA_WEBSOCKET_ERROR: Uncaught Error: internal error
[0]     at mapHranaError (index.js:34477:12)
[0]     at #openStream (index.js:34346:13)
[0]     at async HranaClient.execute (index.js:34245:25)
[0]     at async index.js:32871:22
[0]     at async resolveMiddleware (index.js:27315:22)
[0]     at async callRecursive (index.js:27348:25)
[0]     at async callRecursive (index.js:27348:25)
[0]     at async index.js:32241:18
[0]     at async callRecursive (index.js:27348:25)
[0]     at async callRecursive (index.js:27348:25) {
[0]   code: HRANA_WEBSOCKET_ERROR,
[0]   name: LibsqlError,
[0]   stack: LibsqlError: HRANA_WEBSOCKET_ERROR: Uncaught Error...5)
[0]     at async callRecursive (index.js:27348:25),
[0]   message: HRANA_WEBSOCKET_ERROR: Uncaught Error: internal error,
[0]   cause: WebSocketError: Uncaught Error: internal error
[0]     at #onSocketError (index.js:33984:21)
[0]     at ind...
[0] }

I also tried using DATABASE_URL=http://localhost:3030

$ turso dev --local-file=dev.sqlite --verbose
This is experimental: In particular, some Turso features are currently not present in local-server mode:
→   extensions.
→   interactive transactions/libsql-based URLs.

For environments where a filesystem is available:
→   use file:dev.sqlite as a URL. This server is not needed. (Ctrl-C)
For all others environments:
→   Database is at dev.sqlite. Connect to http://localhost:3030.

[ben-xD]

When I switched from import { createClient } from '@libsql/client/web'; to import { createClient } from '@libsql/client/http';, I got another error:

@libsql/client tries to use https and the worker fails:

[0] workerd/jsg/util.c++:276: error: e = kj/compat/tls.c++:74: failed: OpenSSL error; message = error:100000f7:SSL routines:OPENSSL_internal:WRONG_VERSION_NUMBER
[0] stack: 1027b92ab 1027bbb1f 1027bc23b 1027bc968 1027c47f8 103760d3c 103762998 1028b3304 1028b3728 1028b2eec 1037506d0 1024495d0 1024468d4 10251c2fc 10245df10 1

I reported this in https://github.com/libsql/libsql-client-ts/issues/39

[glommer]

@ben-xD we'll look into some of those issues - thanks! But Cloudflare workers unfortunately may not work with a local IP anyway - this has been reported on our discord. For now the best will be to just create a new database for development in the actual service.

[CodingDoug]

Persistence to a local file should be resolved in the latest CLI 0.62. You can pass a --db-file argument with a file path to turso dev, and it will use that SQLite file to persist data between executions.