Build an application with Logos SQL

Run a password-manager example and follow its SQL writes between two local databases.

Version

This document is accurate for Testnet v0.3.

This guide demonstrates how Logos SQL can be used to maintain an application's database on the Logos Blockchain. It does this by walking through a password-manager example in the logos-blockchain repository. It shows how an application starts Logos SQL, prepares SQL transactions and reads its local database.

The example separates startup configuration, database operations and terminal commands. The same library calls apply to other applications. Read the introduction first for the relationship between local data and shared history.

Use fake credentials only

The example encrypts accounts, passwords and notes before submitting them to Logos SQL. Labels and write activity remain public. It is a demonstration, not an audited password manager: use fake credentials only. Deleting an entry does not erase its encrypted data from channel history.

The walkthrough uses two instances on your own machine, each with a separate database directory.

Prerequisites

• Git, Rust and the Logos Blockchain build prerequisites.
• The OpenSSL command-line tool to generate keys for the example.
• A compatible Logos Blockchain node that has reached Online, with a reachable HTTP API (default: http://127.0.0.1:8080). Follow Run a Logos Blockchain node to set one up.
• A funded key in the node's wallet for inscription fees. On a testnet node, follow Mine tokens to fund your node to fund the PoWClaim key. A local standalone node configures no auto-claim targets, so use the funded genesis key from its wallet.known_keys instead, as Step 2 sets out.
• A maximum inscription fee appropriate for your test network.

What to expect

• You can build and run an existing Logos SQL application.
• You can update a fake credential and read it from another participant.
• You can identify the SQL calls and review commands used to handle displaced writes.

Step 1: Build the example

Logos SQL is not yet published to crates.io. Build the example inside its source workspace.

1. Clone the repository and check out the tested revision:

   git clone https://github.com/logos-blockchain/logos-blockchain.git
   cd logos-blockchain
   git checkout 47a65ab3c

2. Build the password manager:

   cargo build --locked --release -p logos-sql --example password_manager

3. Check its startup options:

   ./target/release/examples/password_manager --help

   Expected result: help lists the encryption key file, channel, node, signing key, funding key, fee and state-directory options.

Step 2: Configure and start the application

1. In terminal A, generate a separate encryption key for this example:

   (umask 077; openssl rand -out password-manager.key 32)

   Run this once, before starting either instance. Do not overwrite an existing key. The file contains 32 random bytes, not a password or a hexadecimal string. Keep it private and backed up: without it, the encrypted data cannot be read. Do not use the channel signing key for encryption.

2. Generate a channel signing key:

   (umask 077; openssl genpkey -algorithm ed25519 -out channel-signing.pem)

   Generate this key only once and keep the file private. Do not overwrite it when restarting or adding a participant. Its public key identifies the new channel; you do not need to create the channel separately.

3. Extract the signing key and channel ID in the hexadecimal format the example accepts:

   export LOGOS_SQL_SIGNING_KEY=$(openssl pkey -in channel-signing.pem -outform DER | tail -c 32 | od -An -tx1 | tr -d ' \n')
   export LOGOS_SQL_CHANNEL_ID=$(openssl pkey -in channel-signing.pem -pubout -outform DER | tail -c 32 | od -An -tx1 | tr -d ' \n')

   These are 32-byte values without a 0x prefix. See the Zone SDK channel setup for channel identity and signing authority.

4. Set the remaining values from your test-network setup:

   export LOGOS_SQL_NODE_URL='<node-http-url>'
   export LOGOS_SQL_FUNDING_KEY='<funding-public-key-hex>'
   export LOGOS_SQL_MAX_TX_FEE='<maximum-fee>'
   export LOGOS_SQL_ENCRYPTION_KEY_FILE="$PWD/password-manager.key"

   Replace every angle-bracket placeholder:

   • For a local node, the default HTTP URL is http://127.0.0.1:8080.
   • On a testnet node, use the auto-claim target's public_key as the funding key: pow_status prints it in Step 4 of the "Run a Blockchain node" tutorial, and it is also at pow.auto_claim.targets[0].public_key in the node's user_config.yaml.
   • On a local standalone node, which configures no auto-claim targets, use a funded key from the node's wallet.known_keys—check a candidate with curl -s http://127.0.0.1:8080/wallet/<key>/balance first, because the genesis keys are not all funded. The example does not create or fund a wallet.
   note

   This walkthrough was tested with example source at commit 47a65ab3c and LOGOS_SQL_MAX_TX_FEE=1000000. This value is a fee cap, not a fixed charge. Required fees can change with network conditions.

5. Start the application with its own state directory:

   ./target/release/examples/password_manager --state-dir ./password-manager-a

   Expected result: after catching up and preparing the schema, it prints:

   Password manager is running. Enter `help` to list commands.
   Credentials are encrypted; labels remain public. Use test credentials only.
   password-manager>

Understand how it works

1. Open logos_sql/examples/password_manager/passwords.rs and find PasswordManager::start:

   /// Starts `λSQL`, catches up with the channel, and prepares the database.
   pub async fn start(config: LogosSqlConfig, encryption: Encryption) -> AppResult<Self> {
      let manager = Self {
         logos_sql: LogosSql::start(config).await?,
         encryption,
      };
   
      match manager.initialize().await {
         // Keep the terminal available if schema creation was displaced.
         Ok(()) | Err(AppError::LogosSql(LogosSqlError::UnhandledDisplacements)) => {}
         Err(error) => {
               manager.shutdown().await?;
               return Err(error);
         }
      }
   
      Ok(manager)
   }

   It calls LogosSql::start(config).await?, then checks whether the credentials table exists. If needed, it submits CREATE TABLE through Logos SQL. A later participant obtains the schema by replaying channel history.

2. Then, open config.rs. This file handles constructing LogosSqlConfig, with signing and funding credentials in WriterConfig:

   /// Parses startup options and builds the `λSQL` configuration.
   pub fn from_args() -> AppResult<(LogosSqlConfig, Encryption)> {
      let options = Options::parse();
      let encryption =
         Encryption::from_file(&options.encryption_key_file, options.channel_id.as_ref())?;
   
      let config = LogosSqlConfig {
         channel_id: options.channel_id,
         node_url: options.node_url,
         writer: Some(WriterConfig {
               publication: PublicationConfig::default(),
               signing_key: options.signing_key,
               funding: FundingConfig {
                  funding_pk: options.funding_key,
                  change_pk: None,
                  max_tx_fee: options.max_tx_fee.into(),
                  priority_fee_percent: options.priority_fee_percent,
               },
         }),
         state_dir: options.state_dir,
      };
   
      Ok((config, encryption))
   }

   Publication settings control batching and queue limits. The library drives replication in the background; the application does not run a separate Zone SDK event loop.

   note

   Encryption belongs to the application, not LogosSqlConfig. The example uses XChaCha20-Poly1305 from RustCrypto to encrypt each field and detect tampering. It stores the nonce with the encrypted value; the key stays in the local key file and is never submitted to Logos SQL.

Step 3: Add and read a credential

1. At the prompt in terminal A, enter:

   add email [email protected] not-a-real-password

   Expected result: committed locally as <tx-id>; not final. The transaction ID varies. This confirms a local commit, not publication or finality.

2. Read the credential:

   show email

   Expected result:

   email
     account: [email protected]
     password: not-a-real-password
     notes:

Understand how it works

1. Find prepare_credential_insert in passwords.rs:

   fn prepare_credential_insert(credential: &StoredCredential) -> TransactionBuilder {
       TransactionBuilder::new(
           "INSERT INTO credentials (label, account, password, notes)
            VALUES (?1, ?2, ?3, ?4)",
       )
       .bind(&credential.label)
       .bind(credential.account.as_slice())
       .bind(credential.password.as_slice())
       .bind(credential.notes.as_slice())
   }

2. In the same file, find the application's add method. It encrypts the account, password and initially empty notes into a StoredCredential:

   /// Adds one credential.
   ///
   /// Concurrent attempts to use the same label produce a primary-key
   /// conflict.
   pub async fn add(&self, label: String, account: String, password: String) -> AppResult<TxId> {
      let credential = StoredCredential {
         account: self.encryption.encrypt(&label, "account", &account)?,
         password: self.encryption.encrypt(&label, "password", &password)?,
         notes: self.encryption.encrypt(&label, "notes", "")?,
         label,
      };
   
      Ok(self
         .logos_sql
         .execute(prepare_credential_insert(&credential))
         .await?)
   }

   Each bind above supplies the next parameter through the standard rusqlite::ToSql interface: a readable label and three encrypted binary values. The app then passes this builder to self.logos_sql.execute(...).await?.

   tip

   For multiple statements in one SQL transaction, append .query(sql) and its bindings. They commit or roll back together under one transaction ID. The library owns BEGIN and COMMIT; do not include them in the SQL.

3. Find credential in the same file:

   /// Reads one credential from the local `SQLite` database.
   pub fn credential(&self, label: &str) -> AppResult<Option<Credential>> {
      self.stored_credential(label)?
         .map(|credential| credential.decrypt(&self.encryption))
         .transpose()
   }
   
   fn stored_credential(&self, label: &str) -> AppResult<Option<StoredCredential>> {
      if !self.schema_exists()? {
         return Ok(None);
      }
   
      let connection = self.logos_sql.read_connection()?;
   
      let mut statement = connection.prepare(
         "SELECT label, account, password, notes
            FROM credentials WHERE label = ?1",
      )?;
   
      Ok(statement
         .query_row([label], |row| {
               Ok(StoredCredential {
                  label: row.get("label")?,
                  account: row.get("account")?,
                  password: row.get("password")?,
                  notes: row.get("notes")?,
               })
         })
         .optional()?)
   }

It calls stored_credential, which opens self.logos_sql.read_connection()? and queries credentials with ordinary SQLite calls. The app decrypts the fields before displaying them. Reads use the local database, not a network request.

Step 4: Read from another participant

1. In terminal B, enter the same source checkout and repeat only the export commands from Step 2. They reuse the existing channel-signing.pem and password-manager.key files. Do not generate new keys: both instances must use the same channel and encryption key. On separate machines, each instance needs a securely transferred copy of the encryption key.

   The example has no read-only startup mode: both instances have writer credentials, even when you only issue read commands. Keep these test credentials on your own machine.

2. Start a second instance with a different directory:

   ./target/release/examples/password_manager --state-dir ./password-manager-b

3. At its prompt, enter show email.

   Expected result: the same account and fake password appear. If it prints credential not found, leave both processes running and try again after the write reaches this participant.

   Each instance has its own SQLite database. The schema and data arrive through channel history; no database files are copied between them. Seeing the row here confirms replication, not finality.

Step 5: Update the password and notes

1. In terminal A, enter:

   update email another-fake-password
   notes email recovery codes stored separately

   Expected result: each command reports committed locally as <tx-id>; not final.

2. In terminal B, run show email again until both changes appear. Run list to see the label and account:

   email ([email protected])

Understand how it works

1. Find prepare_password_update in passwords.rs:

   fn prepare_password_update(label: &str, password: &[u8], old_password: &[u8]) -> TransactionBuilder {
       TransactionBuilder::new(
           "UPDATE credentials SET password = ?2
            WHERE label = ?1 AND password = ?3",
       )
       .bind(label)
       .bind(password)
       .bind(old_password)
   }

   The application reads the stored password, checks it can decrypt it, and encrypts the replacement. The SQL compares the original encrypted bytes with the bytes still in the row; it does not decrypt them. Notes updates check the old notes independently, so changing the notes does not invalidate the password condition. Deletes check all the entry's stored values as read when the command was prepared.

   These conditions stay with the original SQL if the user retries a displaced write. The retry reuses the saved encrypted values, rather than encrypting them again. A condition that no longer matches changes no rows; SQLite still considers that successful. Check the resulting data rather than treating a transaction ID as proof that a value changed.

Step 6: Review a displaced write

This step applies if channel history changes and the application reports displaced writes. The commands above do not deliberately create a fork, so an ordinary run may have none.

1. Enter displacements to list writes awaiting review. If there are none, it prints no displacements awaiting review.

   While any remain, ordinary new writes are blocked. A blocked write prints an error and directs the user to displacements, then show or list to inspect the data.

2. Inspect the current entry with show email. Decide whether the original operation should still be attempted.

3. Choose one action for an ID from the list, replacing <tx-id>:

   Command	Effect
   retry <tx-id>	Executes the saved SQL with its original conditions and marks that displacement handled after success.
   handle <tx-id>	Marks the displacement handled without submitting another write.

   A retry prints retry committed locally as <new-tx-id>; not final. Use show to check the data afterwards. If the selected displacement has changed since it was listed, refresh displacements and review it again.

   This example leaves retries to the user. Other applications can handle displacements automatically when their SQL conditions and application policy make that safe. Handling a displacement does not cancel the original write; it can return after another reorganisation.

4. Check displacements again before continuing. If another displacement arrives during review, the next ordinary write remains blocked until that item is handled too.

   See Handle displaced writes for the underlying library calls and how to add this flow to another application.

Step 7: Remove the entry and stop

1. In terminal A, enter remove email.

2. In terminal B, run show email until it prints credential not found.

   If the entry keeps appearing, run displacements in terminal A. If the removal is listed, review it as described in Step 6.

3. Enter exit in both terminals. The example calls shutdown().await to stop replication. Shutdown does not wait for finality.

4. Restart either instance with its original command and state directory, then enter list.

   Expected result: it resumes from saved state and follows the channel. If the deletion remains in the current history, email is absent. A chain reorganisation can still change non-finalised data.

Troubleshooting

Startup prints nothing and does not return

LogosSql::start processes channel history before it returns, so the example is silent while it catches up—a few minutes is normal on a local node, even when everything is configured correctly. If the prompt has not appeared after that, check that the node is running, that it reports mode: Online, and that LOGOS_SQL_NODE_URL matches its api.backend.listen_address. The state directory is created before the first connection attempt, so finding database files there does not mean startup succeeded.

A credential cannot be decrypted

Check that both instances use the same encryption key file contents and channel. A wrong key or modified encrypted data causes decryption to fail. Do not generate a replacement key to fix this: a new key cannot decrypt existing entries.

Use a fresh channel and state directory for this encrypted example. It does not migrate old unencrypted entries or rotate keys. Encrypting future writes cannot hide unencrypted data already published in the channel history.

Adding a credential reports a primary key constraint error

The label already exists. Use show to inspect it, update to change its password, or choose another label for a new entry.

The second instance does not show a change

Check that both instances follow the same channel, that the node is processing blocks, and that the writer has valid signing authority and funding. Keep both processes running and issue the read command again. The terminal does not refresh displayed results automatically.

A request returns PublishPending

The pending-write queue is full or the library is still saving an accepted publication. The new request has not committed any SQL changes. Leave replication running and try that request later; do not assume other errors have the same retry guarantee.

A read needs finalised data

The example reads the latest local state. Applications needing settled history can use finalized_read_connection() with the same SQLite API. The schema and each data change appear in that view only after their writes finalise.