Local db get nuked when syncing from remote if dev server restarted

[qtfkwk]

Steps to reproduce:

1. Run the dev server

   $ turso dev -f remote.db
   sqld listening on port 8080.
   Use the following URL to configure your libSQL client SDK for local development:
   
       http://127.0.0.1:8080
   
   By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
   
   Using database file remote.db.
   

2. Create a rust client

   cargo new client
   cd client
   cargo add libsql -F remote,replication,sync
   cargo add tokio -F full
   cat <<"EOF" >src/main.rs
   use libsql::{Builder, Result};
   
   const CREATE_USERS_TABLE: &str = "\
   CREATE TABLE IF NOT EXISTS users (\
   id INTEGER PRIMARY KEY, \
   name TEXT UNIQUE NOT NULL, \
   email TEXT UNIQUE NOT NULL\
   )";
   const SELECT_USER: &str = "SELECT * FROM users WHERE name = ?1 AND email = ?2";
   const INSERT_USER: &str = "INSERT INTO users (name, email) VALUES (?1, ?2)";
   const SELECT_USERS: &str = "SELECT * FROM users";
   
   #[tokio::main]
   async fn main() -> Result<()> {
       let local_path = "local.db";
       // let local_path = ":memory:";
       let remote_url = "http://127.0.0.1:8080";
       let remote_auth = "";
       print!("Builder (path={local_path:?}, url={remote_url:?}, auth_token={remote_auth:?})... ");
       let db = Builder::new_remote_replica(
           local_path.to_string(),
           remote_url.to_string(),
           remote_auth.to_string(),
       )
       .build()
       .await?;
       println!("Done");
   
       print!("Syncing... ");
       let f = db.sync().await?;
       println!("{f:?}");
   
       print!("Connecting... ");
       let conn = db.connect()?;
       println!("{conn:?}");
   
       print!("Syncing... ");
       let f = db.sync().await?;
       println!("{f:?}");
   
       print!("Create users table (if doesn't already exist)... ");
       conn.execute(CREATE_USERS_TABLE, ()).await?;
       println!("Done");
   
       println!("Insert users...");
       for (name, email) in &[
           ("Alice", "alice@example.com"),
           ("Bob", "bob@example.com"),
           ("Charlie", "charlie@example.com"),
       ] {
           let mut rows = conn.query(SELECT_USER, (*name, *email)).await?;
           if let Some(row) = rows.next().await? {
               println!("* User {name:?} {email:?} already exists! `row = {row:?}`");
           } else {
               println!("* Inserting user {name:?} {email:?}");
               conn.execute(INSERT_USER, [*name, *email]).await?;
   
               print!("\n  Syncing... ");
               let f = db.sync().await?;
               println!("{f:?}\n");
           }
       }
   
       println!("Select all users...");
       let mut rows = conn.query(SELECT_USERS, ()).await?;
       print!("\n```json\n{{\"users\": [");
       let mut count = 0;
       while let Some(row) = rows.next().await? {
           count += 1;
   
           let id = row.get_value(0)?;
           let name = row.get_value(1)?;
           let email = row.get_value(2)?;
   
           let id = id.as_integer().unwrap();
           let name = name.as_text().unwrap();
           let email = email.as_text().unwrap();
   
           if count > 1 {
               print!(",");
           }
   
           print!("\n    {{\"id\": {id:?}, \"name\": {name:?}, \"email\": {email:?}}}");
       }
       println!("\n]}}\n```\n");
   
       Ok(())
   }
   EOF

3. Build and run it:

   $ cargo run
   ...
   Builder (path="local.db", url="http://127.0.0.1:8080", auth_token="")... Done
   Syncing... Replicated { frame_no: Some(0), frames_synced: 1 }
   Connecting... Connection
   Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
   Create users table (if doesn't already exist)... Done
   Insert users...
   * Inserting user "Alice" "alice@example.com"
   
     Syncing... Replicated { frame_no: Some(7), frames_synced: 0 }
   
   * Inserting user "Bob" "bob@example.com"
   
     Syncing... Replicated { frame_no: Some(10), frames_synced: 0 }
   
   * Inserting user "Charlie" "charlie@example.com"
   
     Syncing... Replicated { frame_no: Some(13), frames_synced: 0 }
   
   Select all users...
   
   ```json
   {"users": [
       {"id": 1, "name": "Alice", "email": "alice@example.com"},
       {"id": 2, "name": "Bob", "email": "bob@example.com"},
       {"id": 3, "name": "Charlie", "email": "charlie@example.com"}
   ]}
   ```
   

4. Check that it wrote to the remote db:

   $ turso db shell http://127.0.0.1:8080 .dump
   PRAGMA foreign_keys=OFF;
   BEGIN TRANSACTION;
   CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
   INSERT INTO users VALUES(1,'Alice','alice@example.com');
   INSERT INTO users VALUES(2,'Bob','bob@example.com');
   INSERT INTO users VALUES(3,'Charlie','charlie@example.com');
   COMMIT;
   

5. Check that it wrote to the local db:

   $ turso dev -f local.db -p 8081
   sqld listening on port 8081.
   Use the following URL to configure your libSQL client SDK for local development:
   
       http://127.0.0.1:8081
   
   By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
   
   Using database file local.db.
   

   $ turso db shell http://127.0.0.1:8081 .dump
   PRAGMA foreign_keys=OFF;
   BEGIN TRANSACTION;
   CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
   INSERT INTO users VALUES(1,'Alice','alice@example.com');
   INSERT INTO users VALUES(2,'Bob','bob@example.com');
   INSERT INTO users VALUES(3,'Charlie','charlie@example.com');
   COMMIT;
   

6. Ctrl+C the dev servers started in steps 1 and 4.

7. Up arrow to restart the dev server and run turso db -f remote.db again.

8. Confirm data persisted in remote: turso db shell http://127.0.0.1:8080 .dump

   $ turso db shell http://127.0.0.1:8080 .dump
   PRAGMA foreign_keys=OFF;
   BEGIN TRANSACTION;
   CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
   INSERT INTO users VALUES(1,'Alice','alice@example.com');
   INSERT INTO users VALUES(2,'Bob','bob@example.com');
   INSERT INTO users VALUES(3,'Charlie','charlie@example.com');
   COMMIT;
   

9. Rerun the client: cargo run

   $ cargo run
   ...
   Builder (path="local.db", url="http://127.0.0.1:8080", auth_token="")... Done
   Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
   Connecting... Connection
   Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
   Create users table (if doesn't already exist)... Done
   Insert users...
   Error: SqliteFailure(1, "no such table: users")
   

   Fails and says the users table doesn't exist.

10. Check state of the local db:

    $ turso dev -f local.db -p 8081
    sqld listening on port 8081.
    Use the following URL to configure your libSQL client SDK for local development:
    
        http://127.0.0.1:8081
    
    By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
    
    Using database file local.db.
    

    $ turso db shell http://127.0.0.1:8081 .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    COMMIT;
    

DATA IS GONE!

It's in the remote... but how can the local db sync it???

rm local.db* and rerunning cargo run doesn't work...

Any ideas?

[qtfkwk]

Also... it's odd that turso CLI is not able to read a local db directly without firing up a dev server... no? (Or perhaps I'm not seeing how to do it?)