Attachments / Files

Included with web and node and react-native packages, for react-native adapters install @powersync/attachments-storage-react-native.

import { Schema, Table, column, AttachmentTable } from '@powersync/web';

const appSchema = new Schema({
  users: new Table({
    name: column.text,
    email: column.text,
    photo_id: column.text  // References attachment ID
  }),
  // Add the attachment table
  attachments: new AttachmentTable()
});

// For web browsers (IndexedDB)
import { IndexDBFileSystemStorageAdapter } from '@powersync/web';
const localStorage = new IndexDBFileSystemStorageAdapter('my-app-files');

// For Node.js/Electron (filesystem)
// import { NodeFileSystemAdapter } from '@powersync/node';
// const localStorage = new NodeFileSystemAdapter('./user-attachments');

// For React Native (Expo or bare React Native)
// Need to install @powersync/attachments-storage-react-native
//
// For Expo projects, also install expo-file-system
// import { ExpoFileSystemStorageAdapter } from '@powersync/attachments-storage-react-native';
// const localStorage = new ExpoFileSystemStorageAdapter();
//
// For bare React Native, also install @dr.pogodin/react-native-fs
// import { ReactNativeFileSystemStorageAdapter } from '@powersync/attachments-storage-react-native';
// const localStorage = new ReactNativeFileSystemStorageAdapter();

// Remote storage adapter (example with signed URLs)
const remoteStorage = {
  async uploadFile(fileData: ArrayBuffer, attachment: AttachmentRecord) {
    // Request signed upload URL from your backend
    const { uploadUrl } = await fetch('/api/attachments/upload-url', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ 
        filename: attachment.filename,
        contentType: attachment.mediaType 
      })
    }).then(r => r.json());
    
    // Upload to cloud storage using signed URL
    await fetch(uploadUrl, {
      method: 'PUT',
      body: fileData,
      headers: { 
        'Content-Type': attachment.mediaType || 'application/octet-stream' 
      }
    });
  },
  
  async downloadFile(attachment: AttachmentRecord): Promise<ArrayBuffer> {
    // Request signed download URL from your backend
    const { downloadUrl } = await fetch(
      `/api/attachments/${attachment.id}/download-url`
    ).then(r => r.json());
    
    // Download from cloud storage
    const response = await fetch(downloadUrl);
    return response.arrayBuffer();
  },
  
  async deleteFile(attachment: AttachmentRecord) {
    // Delete via your backend
    await fetch(`/api/attachments/${attachment.id}`, {
      method: 'DELETE'
    });
  }
};

import { AttachmentQueue } from '@powersync/web';

const attachmentQueue = new AttachmentQueue({
  db: db,  // PowerSync database instance
  localStorage,
  remoteStorage,
  
  // Define which attachments exist in your data model
  watchAttachments: (onUpdate) => {
    db.watch(
      `SELECT photo_id FROM users WHERE photo_id IS NOT NULL`,
      [],
      {
        onResult: async (result) => {
          const attachments = result.rows?._array.map(row => ({
            id: row.photo_id,
            fileExtension: 'jpg'
          })) ?? [];
          await onUpdate(attachments);
        }
      }
    );
  },
  
  // Optional configuration
  syncIntervalMs: 30000,      // Sync every 30 seconds
  downloadAttachments: true,  // Auto-download referenced files
  archivedCacheLimit: 100     // Keep 100 archived files before cleanup
});

// Start the sync process
await attachmentQueue.startSync();

// Example: Watching users.photo_id, documents.document_id, and videos.video_id
// Assuming your tables store file extensions
const attachmentQueue = new AttachmentQueue({
  db: db,
  localStorage,
  remoteStorage,
  
  watchAttachments: (onUpdate) => {
    db.watch(
      `SELECT photo_id as id, photo_file_extension as file_extension
       FROM users 
       WHERE photo_id IS NOT NULL
       
       UNION ALL
       
       SELECT document_id as id, document_file_extension as file_extension
       FROM documents 
       WHERE document_id IS NOT NULL
       
       UNION ALL
       
       SELECT video_id as id, video_file_extension as file_extension
       FROM videos 
       WHERE video_id IS NOT NULL`,
      [],
      {
        onResult: async (result) => {
          const attachments = result.rows?._array.map(row => ({
            id: row.id,
            fileExtension: row.file_extension
          })) ?? [];
          await onUpdate(attachments);
        }
      }
    );
  },
  
  // ... other options
});

await attachmentQueue.startSync();

// Create separate queues for different attachment types
const photoQueue = new AttachmentQueue({
  db: db,
  localStorage,
  remoteStorage,
  watchAttachments: (onUpdate) => {
    db.watch(
      `SELECT photo_id FROM users WHERE photo_id IS NOT NULL`,
      [],
      {
        onResult: async (result) => {
          const attachments = result.rows?._array.map(row => ({
            id: row.photo_id,
            fileExtension: 'jpg'
          })) ?? [];
          await onUpdate(attachments);
        }
      }
    );
  },
});

const documentQueue = new AttachmentQueue({
  db: db,
  localStorage,
  remoteStorage,
  watchAttachments: (onUpdate) => {
    db.watch(
      `SELECT document_id FROM documents WHERE document_id IS NOT NULL`,
      [],
      {
        onResult: async (result) => {
          const attachments = result.rows?._array.map(row => ({
            id: row.document_id,
            fileExtension: 'pdf'
          })) ?? [];
          await onUpdate(attachments);
        }
      }
    );
  },
});

await Promise.all([
  photoQueue.startSync(),
  documentQueue.startSync()
]);

async function uploadProfilePhoto(imageBlob: Blob, userId: string) {
  const arrayBuffer = await imageBlob.arrayBuffer();
  
  const attachment = await attachmentQueue.saveFile({
    data: arrayBuffer,
    fileExtension: 'jpg',
    mediaType: 'image/jpeg',
    
    // updateHook runs in same transaction, ensuring atomicity
    updateHook: async (tx, attachment) => {
      await tx.execute(
        'UPDATE users SET photo_id = ? WHERE id = ?',
        [attachment.id, userId]
      );
    }
  });
  
  return attachment;
}

// The queue will:
// 1. Save file locally immediately
// 2. Create attachment record with state QUEUED_UPLOAD
// 3. Update user record in same transaction
// 4. Automatically upload file in background
// 5. Update state to SYNCED when complete

// Downloads happen automatically when watchAttachments references a file

async function getProfilePhotoUri(userId: string): Promise<string | null> {
  const user = await db.get(
    'SELECT photo_id FROM users WHERE id = ?',
    [userId]
  );
  
  if (!user?.photo_id) {
    return null;
  }
  
  const attachment = await db.get(
    'SELECT * FROM attachments WHERE id = ?',
    [user.photo_id]
  );
  
  if (!attachment) {
    return null;
  }
  
  if (attachment.state === 'SYNCED' && attachment.local_uri) {
    return attachment.local_uri;
  }
  
  return null;
}

// Example: Display image in React with watch query
function ProfilePhoto({ userId }: { userId: string }) {
  const [photoUri, setPhotoUri] = useState<string | null>(null);
  
  useEffect(() => {
    const watch = db.watch(
      `SELECT a.local_uri, a.state 
       FROM users u 
       LEFT JOIN attachments a ON a.id = u.photo_id 
       WHERE u.id = ?`,
      [userId],
      {
        onResult: (result) => {
          const row = result.rows?._array[0];
          if (row?.state === 'SYNCED' && row?.local_uri) {
            setPhotoUri(row.local_uri);
          }
        }
      }
    );
    
    return () => watch.close();
  }, [userId]);
  
  if (!photoUri) {
    return <div>Loading photo...</div>;
  }
  
  return <img src={photoUri} alt="Profile" />;
}

async function deleteProfilePhoto(userId: string, photoId: string) {
  await attachmentQueue.deleteFile({
    id: photoId,
    
    // updateHook ensures atomic deletion
    updateHook: async (tx, attachment) => {
      await tx.execute(
        'UPDATE users SET photo_id = NULL WHERE id = ?',
        [userId]
      );
    }
  });
  
  console.log('Photo queued for deletion');
  // The queue will:
  // 1. Delete from remote storage
  // 2. Delete local file
  // 3. Remove attachment record
}

// Alternative: Remove reference and let queue archive it automatically
async function removePhotoReference(userId: string) {
  await db.execute(
    'UPDATE users SET photo_id = NULL WHERE id = ?',
    [userId]
  );
  
  // The watchAttachments callback will detect this change
  // The queue will automatically archive the unreferenced attachment
  // After reaching archivedCacheLimit, it will be deleted
}

import { AttachmentErrorHandler } from '@powersync/web';

const errorHandler: AttachmentErrorHandler = {
  async onDownloadError(attachment, error) {
    console.error(`Download failed: ${attachment.filename}`, error);
    
    // Return true to retry, false to archive
    if (error.message.includes('404')) {
      return false; // File doesn't exist, don't retry
    }
    return true; // Retry on network errors
  },
  
  async onUploadError(attachment, error) {
    console.error(`Upload failed: ${attachment.filename}`, error);
    return true; // Always retry uploads
  },
  
  async onDeleteError(attachment, error) {
    console.error(`Delete failed: ${attachment.filename}`, error);
    return true; // Retry deletes
  }
};

const queue = new AttachmentQueue({
  // ... other options
  errorHandler
});

import { LocalStorageAdapter, RemoteStorageAdapter } from '@powersync/web';

// Example: IPFS remote storage
class IPFSStorageAdapter implements RemoteStorageAdapter {
  async uploadFile(fileData: ArrayBuffer, attachment: AttachmentRecord) {
    // Upload to IPFS
    const cid = await ipfs.add(fileData);
    // Store CID in your backend for retrieval
    await fetch('/api/ipfs-cids', {
      method: 'POST',
      body: JSON.stringify({ attachmentId: attachment.id, cid })
    });
  }
  
  async downloadFile(attachment: AttachmentRecord): Promise<ArrayBuffer> {
    // Retrieve CID from backend
    const { cid } = await fetch(`/api/ipfs-cids/${attachment.id}`)
      .then(r => r.json());
    // Download from IPFS
    return ipfs.cat(cid);
  }
  
  async deleteFile(attachment: AttachmentRecord) {
    // IPFS is immutable, but you can unpin and remove from backend
    await fetch(`/api/ipfs-cids/${attachment.id}`, { method: 'DELETE' });
  }
}

await attachmentQueue.verifyAttachments();

const queue = new AttachmentQueue({
  // ... other options
  archivedCacheLimit: 200  // Keep 200 archived files; oldest deleted when limit reached
});

// For manually expiring the cache
queue.expireCache()