Pastas lost after container restart

[github-cli]

Describe the bug
Using the sqlite database, the pastas are all gone after a container reboot.
The sqlite database file size stays the same though, so I assume the pastas are still in there but not being shown after a container recreation

To Reproduce
Steps to reproduce the behavior:

1. start up microbin with a bind mount
2. create a few public pastas
3. check the size of the sqlite database
4. down and up the container
5. check the size of the sqlite database (should be the same as in step 3)
6. check the web-ui and admin area for any pastas, they are all gone

Expected behavior
I would expect for all non-expired pastas to still be there on contrainer restarts / recreation

Desktop (please complete the following information):

• OS: Windows
• Browser Chrome
• Version 149

** Server (running microbin)
Ubuntu 24.04.4 LTS
Docker version 27.3.1, build ce12230
Docker Compose version v2.29.7
CPU Model name: 12th Gen Intel(R) Core(TM) i9-12900H
MemTotal: 32527052 kB

Additional context
I found #347 where he mentions that sqlite is not working but I could not find an active bug for this

[andrechalella]

I was just coming to say this. Indeed, after container restart the pastas don't show at /list, but I confirm they do exist in /app/microbin_data/attachments and they also exist in /app/microbin_data/database.sqlite. It's funny: only pastas created during the lifetime of the container appear, but the previous pastas can be normally accessed via /uploads/<PASTA-ID> or other means.

[andrechalella]

This comment was partially generated with AI assistance, but the analysis and fix have been verified on my own database and worked 100%.

---

Root cause: column-ordinal mismatch between CREATE TABLE and SELECT * readback, introduced in commit 1abbfe6 ("Add multi-attachment support", Feb 23 2026).

When attachments was added, it was inserted before pasta_type in the CREATE TABLE statement:

burn_after_reads INTEGER NOT NULL,    -- 15
attachments TEXT,                     -- 16 ***
pasta_type TEXT NOT NULL              -- 17

But the SELECT * query_map keeps pasta_type at row.get(16) and reads attachments at row.get(17) — so every read swaps the two fields. pasta_type ends up as "null" (the JSON of None) instead of "text"/"url", and the admin/list templates filter by pasta_type == "text", so no pastas are rendered.

The INSERT is unaffected because it uses named columns.

This only bites on fresh databases where CREATE TABLE IF NOT EXISTS creates the table with the new column order. Databases migrated from an older version via ALTER TABLE ADD COLUMN (which always appends) still have pasta_type at column 16, so they work by accident — which is probably why it wasn't caught in testing.

---

Fix for your existing database — recreates the table with the correct column order, preserving all data by name:

#!/bin/sh
# fix-db.sh — reassigns columns to positions the code expects
# Usage: ./fix-db.sh /path/to/database.sqlite

set -e
DB="$1"
if [ -z "$DB" ]; then
    echo "Usage: $0 <path-to-database.sqlite>"
    exit 1
fi

if [ ! -f "$DB" ]; then
    echo "Error: file '$DB' not found"
    exit 1
fi

echo "Backing up $DB to ${DB}.bak ..."
cp "$DB" "${DB}.bak"

sqlite3 "$DB" <<'EOF'
ALTER TABLE pasta RENAME TO pasta_old;

CREATE TABLE pasta (
    id INTEGER PRIMARY KEY,
    content TEXT NOT NULL,
    file_name TEXT,
    file_size INTEGER,
    extension TEXT NOT NULL,
    read_only INTEGER NOT NULL,
    private INTEGER NOT NULL,
    editable INTEGER NOT NULL,
    encrypt_server INTEGER NOT NULL,
    encrypt_client INTEGER NOT NULL,
    encrypted_key TEXT,
    created INTEGER NOT NULL,
    expiration INTEGER NOT NULL,
    last_read INTEGER NOT NULL,
    read_count INTEGER NOT NULL,
    burn_after_reads INTEGER NOT NULL,
    pasta_type TEXT NOT NULL,
    attachments TEXT
);

INSERT INTO pasta (
    id, content, file_name, file_size, extension, read_only, private,
    editable, encrypt_server, encrypt_client, encrypted_key,
    created, expiration, last_read, read_count, burn_after_reads,
    pasta_type, attachments
)
SELECT
    id, content, file_name, file_size, extension, read_only, private,
    editable, encrypt_server, encrypt_client, encrypted_key,
    created, expiration, last_read, read_count, burn_after_reads,
    pasta_type, attachments
FROM pasta_old;

DROP TABLE pasta_old;
EOF

echo "Done. Backup saved to ${DB}.bak"

After running this, restart the container and pastas will reappear.

---

The code fix is open-ended — the obvious approach (swapping row.get(16) ↔ row.get(17)) would break migrated databases, and SELECT * with ordinals is inherently fragile. The proper solution should handle both fresh and migrated schemas. Leaving that to the maintainer.

[github-cli]

worked for me for now, hope it gets fixed without breaking the install again :-D
Thank you