Improve the experience for turso dev with a persistent database

[CodingDoug]

Currently, turso dev accepts a flag --db-file which is intended to allow developers to provide their own sqlite3 db file for sqld to access directly for local development. The current implementation in problematic in two ways:

1. The file must be in WAL journal mode.
2. When sqld starts using it, it drops other files (suffixed -shm and -wal) in the same directory without explanation

Until sqld is able to use any developer-provided db file cleanly for local development, I'm proposing to change the local dev experience as follows:

1. Remove the current implementation of --db-file entirely.
2. Replace that with two new flags with behavior that's easier to cope with.

Use case 1: starting local development with a new database

To start local development with a new database, the developer specifies a directory where sqld can do its business:

turso dev --db-dir path/to/dir

• The --db-dir flag is required for database persistence.
• The directory is created if it doesn't already exist.
• sqld creates and manages any necessary files in that space

The understanding is that local sqld "owns" this given directory, and will control the implementation details in that space. In practice, the file called "data" in there is a sqlite3 db file in WAL mode, but the developer doesn't need to be concerned about that. This is an implementation detail that could change in the future. The only concern is allocating the space for sqld to work.

Use case 2: starting local development starting with an existing sqlite3 database file

To start local development with an existing database, the developer specifies the path to the file, and a directory where sqld can do its business:

turso dev --import-db-file path/to/sqlite.db --db-dir path/to/dir

• The directory is created if it doesn't already exist.
• If the directory already contains sqld files, they are overwritten
• The given sqlite3 file is copied to the directory and converted to WAL mode (in addition to any other changes sqld might require).

The conversion process requires that the sqlite3 CLI to exist in the developer's PATH. If this is not the case, a resulting error message should state what is required, and why.

Use case 3: continuing local development after one of the above cases

To continue local development with a database directory previously created in case 1 or 2, the developer specifies the same path to the directory managed by sqld:

turso dev --db-dir path/to/dir

[CodingDoug]

The case for importing a sqlite db to a "managed" directory rather than using one in-place is even stronger now, given sqld instances configured to use the new namespaced directory structure by default.

[gris]

Additional info: sqld expects that the sqlite3 page has 4096 bytes.

[rosszurowski]

I filed #614 recently and came across this issue.

As you're listing out different use-cases, I'd consider the value of exposing an sqlite3 database file to users in some way. It makes it a lot easier to work with external tools: database viewers like TablePlus or ArcType, or CLI tools like the sqlite CLI. If I'm debugging the state of my database, or why a certain query is throwing an error, access to tools like that are invaluable. Until libsql has the broad support that sqlite does, that compatibility is a really nice feature.