File Uploads - TrailBase

TrailBase provides built-in file upload handling with automatic storage, JSON schema validation, and secure access controls.

Overview

File upload features:

JSON Schema validation - Enforce file types and constraints
Object storage - Local filesystem or S3-compatible storage
Automatic cleanup - Orphaned files are deleted
Content type detection - MIME type inference
Record API integration - Upload files as part of record creation/updates
Access control - Files inherit record permissions

Basic File Upload

1. Define Schema

Add a file upload column to your table:

migrations/main/U1234567890__add_avatar_to_profiles.sql

ALTER TABLE profiles ADD COLUMN avatar TEXT 
  CHECK(jsonschema('std.FileUpload', avatar, 'image/png, image/jpeg, image/webp'));

File Upload Schema:

std.FileUpload - Single file
std.FileUploads - Multiple files (array)
Second parameter: column name
Third parameter (optional): allowed MIME types

2. Upload File from Client

TypeScript
React
Dart/Flutter
cURL

import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");
const profilesApi = client.records("profiles");

// Get file from input
const fileInput = document.querySelector<HTMLInputElement>('#avatar')!;
const file = fileInput.files?.[0];

if (file) {
  // Create profile with avatar
  await profilesApi.create({
    username: "alice",
    avatar: file,
  });
  
  console.log("Profile created with avatar!");
}

import { useState } from "react";
import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");

function ProfileUpload() {
  const [file, setFile] = useState<File | null>(null);
  const [username, setUsername] = useState("");

  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault();
    
    if (!file) return;

    const profilesApi = client.records("profiles");
    await profilesApi.create({
      username,
      avatar: file,
    });
    
    alert("Profile created!");
  }

  return (
    <form onSubmit={handleSubmit}>
      <input
        type="text"
        value={username}
        onChange={(e) => setUsername(e.target.value)}
        placeholder="Username"
      />
      
      <input
        type="file"
        accept="image/png,image/jpeg,image/webp"
        onChange={(e) => setFile(e.target.files?.[0] ?? null)}
      />
      
      <button type="submit">Upload</button>
    </form>
  );
}

import 'package:trailbase/trailbase.dart';
import 'package:image_picker/image_picker.dart';

final client = Client("http://localhost:4000");

Future<void> uploadAvatar() async {
  // Pick image
  final picker = ImagePicker();
  final image = await picker.pickImage(source: ImageSource.gallery);
  
  if (image != null) {
    final bytes = await image.readAsBytes();
    
    // Create profile with avatar
    await client.records("profiles").create({
      "username": "alice",
      "avatar": MultipartFile.fromBytes(
        bytes,
        filename: image.name,
      ),
    });
    
    print("Profile created with avatar!");
  }
}

# Upload file with multipart/form-data
curl -X POST http://localhost:4000/api/records/profiles \
  -F 'username=alice' \
  -F 'avatar=@/path/to/avatar.png'

3. Access Uploaded Files

Files are automatically stored and accessible via the Record API:

import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");
const profilesApi = client.records("profiles");

// Read profile with avatar
const profile = await profilesApi.read(1);

if (profile.avatar) {
  console.log("Avatar:", profile.avatar);
  // {
  //   id: "01234567-89ab-cdef-0123-456789abcdef",
  //   filename: "avatar_abc123.png",
  //   original_filename: "avatar.png",
  //   content_type: "image/png",
  //   mime_type: "image/png"
  // }
  
  // Construct URL to download file
  const fileUrl = `/api/records/profiles/${profile.id}/files/avatar/${profile.avatar.filename}`;
  console.log("File URL:", fileUrl);
}

File Upload Schema

Single File Upload

CREATE TABLE posts (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  -- Single image with type constraints
  image TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg'))
) STRICT;

Multiple File Uploads

CREATE TABLE posts (
  id      INTEGER PRIMARY KEY,
  title   TEXT NOT NULL,
  -- Multiple images
  gallery TEXT CHECK(jsonschema('std.FileUploads', gallery, 'image/png, image/jpeg'))
) STRICT;

Upload multiple files:

const postsApi = client.records("posts");

const files = Array.from(fileInput.files ?? []);

await postsApi.create({
  title: "My Post",
  gallery: files, // Array of File objects
});

Mixed Content

Combine different file types:

CREATE TABLE documents (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  -- PDF document
  pdf         TEXT CHECK(jsonschema('std.FileUpload', pdf, 'application/pdf')),
  -- Supporting images
  attachments TEXT CHECK(jsonschema('std.FileUploads', attachments))
) STRICT;

File Metadata

The FileUpload type contains:

interface FileUpload {
  // Unique file identifier (UUID)
  id: string;
  
  // Unique filename for storage
  filename: string;
  
  // Original filename from upload
  original_filename?: string | null;
  
  // User-provided content type
  content_type?: string | null;
  
  // Inferred MIME type (more reliable)
  mime_type?: string | null;
}

Always use mime_type for validation, not content_type. The mime_type is inferred by TrailBase and cannot be spoofed by clients.

Accessing Files

Download File

Files are served via the Record API:

/api/records/{table}/{record_id}/files/{column}/{filename}

Example:

const profile = await profilesApi.read(1);

if (profile.avatar) {
  const url = `/api/records/profiles/${profile.id}/files/avatar/${profile.avatar.filename}`;
  
  // Display image
  document.querySelector('img')!.src = url;
}

Direct URL Construction

For convenience, construct URLs directly:

function getFileUrl(
  table: string,
  recordId: number | string,
  column: string,
  filename: string
): string {
  return `${baseUrl}/api/records/${table}/${recordId}/files/${column}/${filename}`;
}

// Usage
const avatarUrl = getFileUrl("profiles", 1, "avatar", profile.avatar.filename);

Updating Files

Replace File

const profilesApi = client.records("profiles");

// Upload new avatar (old one is automatically deleted)
await profilesApi.update(1, {
  avatar: newFile,
});

Remove File

// Set column to null to delete file
await profilesApi.update(1, {
  avatar: null,
});

TrailBase automatically deletes orphaned files. When you replace or remove a file, the old file is queued for deletion.

File Storage

Local Filesystem

By default, files are stored in traildepot/uploads/:

traildepot/
└── uploads/
    ├── 01/
    │   ├── 23/
    │   │   └── 01234567-89ab-cdef-0123-456789abcdef
    │   └── 45/
    │       └── 01234567-89ab-cdef-0123-456789abcdef
    └── 67/
        └── 89/
            └── 01234567-89ab-cdef-0123-456789abcdef

Files are organized by UUID prefix for efficient storage.

S3-Compatible Storage

Configure S3 or compatible storage (MinIO, DigitalOcean Spaces, etc.):

# Set environment variables
export OBJECT_STORE_TYPE=s3
export OBJECT_STORE_BUCKET=my-bucket
export OBJECT_STORE_REGION=us-east-1
export OBJECT_STORE_ACCESS_KEY_ID=your-access-key
export OBJECT_STORE_SECRET_ACCESS_KEY=your-secret-key

# Optional: Custom S3 endpoint
export OBJECT_STORE_ENDPOINT=https://s3.example.com

trail run

For production deployments, use S3 or compatible storage for better scalability and CDN integration.

Access Control

File access inherits record permissions:

traildepot/config.textproto

record_apis: [
  {
    name: "profiles"
    table_name: "profiles"
    acl_world: [READ]
    acl_authenticated: [CREATE, UPDATE]
    # Users can only update their own profile
    update_access_rule: "_ROW_.user = _USER_.id"
  }
]

With this configuration:

Anyone can view profile avatars (world-readable)
Only the profile owner can upload/change their avatar

Private Files

traildepot/config.textproto

record_apis: [
  {
    name: "documents"
    table_name: "documents"
    acl_authenticated: [CREATE, READ, UPDATE, DELETE]
    # Users can only access their own documents
    read_access_rule: "_ROW_.user = _USER_.id"
  }
]

Files are only accessible to the document owner.

Validation

File Type Constraints

Restrict file types using MIME type patterns:

-- Images only
image TEXT CHECK(jsonschema('std.FileUpload', image, 'image/*'))

-- Specific image formats
image TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg, image/webp'))

-- Documents
pdf TEXT CHECK(jsonschema('std.FileUpload', pdf, 'application/pdf'))

-- Videos
video TEXT CHECK(jsonschema('std.FileUpload', video, 'video/mp4, video/webm'))

-- Any file type
file TEXT CHECK(jsonschema('std.FileUpload', file))

Client-Side Validation

function validateFile(file: File): boolean {
  // Check file size (5MB max)
  if (file.size > 5 * 1024 * 1024) {
    alert("File too large (max 5MB)");
    return false;
  }
  
  // Check file type
  const allowedTypes = ["image/png", "image/jpeg", "image/webp"];
  if (!allowedTypes.includes(file.type)) {
    alert("Invalid file type");
    return false;
  }
  
  return true;
}

// Usage
const file = fileInput.files?.[0];
if (file && validateFile(file)) {
  await profilesApi.create({
    username: "alice",
    avatar: file,
  });
}

Always validate on the server. Client-side validation can be bypassed. TrailBase validates MIME types based on file content, not the provided Content-Type header.

Real-World Example: Blog with Images

From the Blog Example:

migrations/main/U1725019362__create_articles.sql

CREATE TABLE articles (
    id           BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
    author       BLOB NOT NULL REFERENCES _user(id) ON DELETE CASCADE,

    title        TEXT NOT NULL,
    intro        TEXT NOT NULL,
    tag          TEXT NOT NULL,
    body         TEXT NOT NULL,

    -- Article featured image
    image        TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg')),

    created      INTEGER DEFAULT (UNIXEPOCH()) NOT NULL
) STRICT;

Create article with image:

import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");
const articlesApi = client.records("articles");

const fileInput = document.querySelector<HTMLInputElement>('#image')!;
const imageFile = fileInput.files?.[0];

await articlesApi.create({
  title: "My First Article",
  intro: "This is the introduction",
  tag: "technology",
  body: "Full article content...",
  image: imageFile,
});

Display article with image:

import { useEffect, useState } from "react";
import { initClient } from "trailbase";

const client = initClient("http://localhost:4000");

type Article = {
  id: string;
  title: string;
  intro: string;
  body: string;
  image?: {
    id: string;
    filename: string;
    mime_type: string;
  };
};

function Article({ id }: { id: string }) {
  const [article, setArticle] = useState<Article | null>(null);

  useEffect(() => {
    async function load() {
      const api = client.records<Article>("articles");
      const data = await api.read(id);
      setArticle(data);
    }
    load();
  }, [id]);

  if (!article) return <div>Loading...</div>;

  const imageUrl = article.image
    ? `/api/records/articles/${article.id}/files/image/${article.image.filename}`
    : null;

  return (
    <article>
      <h1>{article.title}</h1>
      {imageUrl && (
        <img
          src={imageUrl}
          alt={article.title}
          style={{ maxWidth: "100%" }}
        />
      )}
      <p>{article.intro}</p>
      <div dangerouslySetInnerHTML={{ __html: article.body }} />
    </article>
  );
}

Image Optimization

Client-Side Resizing

Resize images before upload to reduce bandwidth:

async function resizeImage(
  file: File,
  maxWidth: number,
  maxHeight: number
): Promise<Blob> {
  return new Promise((resolve) => {
    const img = new Image();
    img.onload = () => {
      const canvas = document.createElement("canvas");
      
      // Calculate dimensions
      let width = img.width;
      let height = img.height;
      
      if (width > maxWidth) {
        height = (height * maxWidth) / width;
        width = maxWidth;
      }
      
      if (height > maxHeight) {
        width = (width * maxHeight) / height;
        height = maxHeight;
      }
      
      canvas.width = width;
      canvas.height = height;
      
      const ctx = canvas.getContext("2d")!;
      ctx.drawImage(img, 0, 0, width, height);
      
      canvas.toBlob((blob) => {
        resolve(blob!);
      }, file.type);
    };
    
    img.src = URL.createObjectURL(file);
  });
}

// Usage
const file = fileInput.files?.[0];
if (file) {
  const resized = await resizeImage(file, 1200, 1200);
  
  await articlesApi.create({
    title: "Article",
    image: new File([resized], file.name, { type: file.type }),
  });
}

WASM Image Processing

Process images server-side using WASM:

import { defineConfig } from "trailbase-wasm";
import { HttpHandler } from "trailbase-wasm/http";
import { query } from "trailbase-wasm/db";

export default defineConfig({
  httpHandlers: [
    HttpHandler.post("/api/images/thumbnail/:id", async (req) => {
      const id = req.getPathParam("id");
      
      // Get image from database
      const rows = await query(
        "SELECT image FROM articles WHERE id = ?",
        [id]
      );
      
      if (!rows.length) {
        return HttpResponse.json(
          { error: "Not found" },
          { status: 404 }
        );
      }
      
      // In production: resize image using WASM image library
      // For now, return original
      const image = rows[0][0];
      return HttpResponse.json({ thumbnail: image });
    }),
  ],
});

Cleanup

Automatic Cleanup

TrailBase automatically deletes orphaned files:

When a record is deleted, files are queued for deletion
When a file column is updated, the old file is queued
A background job processes the deletion queue

Manual Cleanup

Query pending deletions:

SELECT * FROM _file_deletions;

Force cleanup via SQL:

-- Delete all pending files
DELETE FROM _file_deletions;

Manual deletion bypasses TrailBase’s cleanup mechanism. Only use for debugging.

Best Practices

Validate File Types

Always specify allowed MIME types in your schema:

-- Good: Specific types
avatar TEXT CHECK(jsonschema('std.FileUpload', avatar, 'image/png, image/jpeg'))

-- Bad: Any type
avatar TEXT CHECK(jsonschema('std.FileUpload', avatar))

Limit File Sizes

Implement client-side size limits:

const MAX_SIZE = 5 * 1024 * 1024; // 5MB

if (file.size > MAX_SIZE) {
  alert("File too large");
  return;
}

Use Descriptive Column Names

Name columns based on their purpose:

-- Good
profile_picture TEXT CHECK(...)
resume_pdf TEXT CHECK(...)

-- Bad
file1 TEXT CHECK(...)
file2 TEXT CHECK(...)

Optimize Images

Resize large images before upload:

Reduce dimensions to reasonable sizes (e.g., 1200x1200)
Compress quality for web delivery
Consider WebP format for better compression

Use CDN for Production

Serve files through a CDN for better performance:

Configure S3 with CloudFront
Use custom domain for files
Enable caching headers

Troubleshooting

File Upload Fails

Symptom: Upload returns 400 error Solutions:

Check MIME type constraints in schema
Verify file is not corrupted
Check request Content-Type is multipart/form-data
Ensure file size is within limits

File Not Found

Symptom: File URL returns 404 Solutions:

Verify record exists and contains file metadata
Check file column is not NULL
Verify filename matches exactly
Check access permissions

Files Not Deleted

Symptom: Old files remain after update/delete Solutions:

Check _file_deletions table for pending deletions
Wait for background cleanup job (runs periodically)
Verify object storage credentials are correct

Next Steps

First App

Build an app with file uploads

Database Setup

Learn about file upload schemas

Authentication

Secure file uploads

WASM Runtime

Process files with WebAssembly

Examples

Blog Example - Articles with featured images
User Avatars - Profile avatars with authentication

​Overview

​Basic File Upload

​1. Define Schema

​2. Upload File from Client

​3. Access Uploaded Files

​File Upload Schema

​Single File Upload

​Multiple File Uploads

​Mixed Content

​File Metadata

​Accessing Files

​Download File

​Direct URL Construction

​Updating Files

​Replace File

​Remove File

​File Storage

​Local Filesystem

​S3-Compatible Storage

​Access Control

​Private Files

​Validation

​File Type Constraints

​Client-Side Validation

​Real-World Example: Blog with Images

​Image Optimization

​Client-Side Resizing

​WASM Image Processing

​Cleanup

​Automatic Cleanup

​Manual Cleanup

​Best Practices

​Troubleshooting

​File Upload Fails

​File Not Found

​Files Not Deleted

​Next Steps

First App

Database Setup

Authentication

WASM Runtime

​Examples

Overview

Basic File Upload

1. Define Schema

2. Upload File from Client

3. Access Uploaded Files

File Upload Schema

Single File Upload

Multiple File Uploads

Mixed Content

File Metadata

Accessing Files

Download File

Direct URL Construction

Updating Files

Replace File

Remove File

File Storage

Local Filesystem

S3-Compatible Storage

Access Control

Private Files

Validation

File Type Constraints

Client-Side Validation

Real-World Example: Blog with Images

Image Optimization

Client-Side Resizing

WASM Image Processing

Cleanup

Automatic Cleanup

Manual Cleanup

Best Practices

Troubleshooting

File Upload Fails

File Not Found

Files Not Deleted

Next Steps

Examples