How to Manage Fastio File Metadata with Drizzle ORM
Managing Fastio file metadata with Drizzle ORM lets developers store and query agent workspace data in Postgres with full type safety. This approach syncs file details like IDs, sizes, and AI states from the Fastio API to a local database. Caching metadata reduces repeated API calls and enables complex relational queries beyond API limits.
Why Cache Fastio File Metadata Locally?
Fastio workspaces store files for agentic teams, with metadata like file IDs, names, sizes, MIME types, timestamps, and AI processing states. Querying this data directly via the API works for simple cases. But for frequent access, analytics, or joining with other data, API rate limits and latency add up.
Local caching in Postgres cuts API dependency. Drizzle ORM provides 100% TypeScript type safety for schema and queries. Developers gain relational power, like joining file metadata with user activity or custom tags.
This setup supports agent workflows. Agents upload files, metadata syncs automatically, and queries run locally for speed.
Set Up Drizzle ORM with Postgres
Install Drizzle and Postgres driver:
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg
Configure drizzle.config.ts:
import type { Config } from 'drizzle-kit';
export default {
schema: './src/schema.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
} satisfies Config;
Set DATABASE_URL in .env. Run npx drizzle-kit generate:pg and npx drizzle-kit push:pg to apply schema.
Build agent workspaces with persistent metadata?
Get 50GB free storage and 5,000 monthly credits. No credit card needed for agents.
Define Drizzle Schema for Fastio Files
Map Fastio storage nodes to a files table. Key fields from API: node_id (opaque string), workspace_id (profile ID), name, size_bytes, mime_type, created_at, updated_at, type ('file'), ai_state ('ready', etc.), version_id.
import { pgTable, text, bigint, timestamp, varchar, pgEnum } from 'drizzle-orm/pg-core';
const aiStateEnum = pgEnum('ai_state', ['disabled', 'pending', 'in_progress', 'ready', 'failed']);
export const files = pgTable('files', {
id: text('node_id').primaryKey(), // Fastio opaque node ID
workspaceId: text('workspace_id').notNull(),
parentId: text('parent_id'), // Folder node ID
name: varchar('name', { length: 256 }).notNull(),
path: text('path'), // Computed full path
sizeBytes: bigint('size_bytes', { mode: 'number' }),
mimeType: varchar('mime_type', { length: 128 }),
type: varchar('type', { length: 10 }).notNull().default('file'), // file/folder/note
aiState: aiStateEnum('ai_state'),
versionId: text('version_id'),
createdAt: timestamp('created_at').notNull(),
updatedAt: timestamp('updated_at').notNull(),
});
export type File = typeof files.$inferSelect;
This schema matches Fastio responses. Generate migrations with Drizzle Kit.
Fetch Metadata from Fastio API
Authenticate with API key (Bearer token). List files recursively or by cursor.
Example fetch workspace files:
async function fetchWorkspaceFiles(workspaceId: string, token: string, parentId = 'root') {
let cursor = '';
const allFiles: any[] = [];
do {
const url = `https://api.fast.io/current/workspace/${workspaceId}/storage/${parentId}/list/?page_size=500&sort_by=updated&sort_dir=desc${cursor ? `&cursor=${cursor}` : ''}`;
const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
const data = await res.json();
if (data.result) {
allFiles.push(...data.response.filter((item: any) => item.type === 'file'));
cursor = data.pagination.next_cursor;
}
} while (cursor);
return allFiles;
}
Handle pagination with next_cursor. Filter type === 'file'.
Sync Metadata to Drizzle with Upserts
Use Drizzle transactions for atomic upserts. Map API fields to insert.
import { db } from './db';
import { files } from './schema';
import { eq } from 'drizzle-orm';
async function syncFiles(workspaceFiles: any[]) {
await db.transaction(async (tx) => {
for (const file of workspaceFiles) {
await tx.insert(files)
.values({
id: file.node_id,
workspaceId: workspaceId,
parentId: file.parent_id,
name: file.name,
sizeBytes: BigInt(file.size_bytes),
mimeType: file.mime_type,
type: file.type,
aiState: file.ai?.state,
versionId: file.version,
createdAt: new Date(file.created_at),
updatedAt: new Date(file.updated_at),
})
.onConflictDoUpdate({
target: files.id,
set: {
parentId: file.parent_id,
name: file.name,
// ... other fields
updatedAt: new Date(file.updated_at),
},
});
}
});
}
Run on cron or webhook trigger from Fastio events.
Query Cached Metadata with Drizzle
Leverage type-safe queries for analytics.
Recent ready files:
const recentReadyFiles = await db.select()
.from(files)
.where(and(
eq(files.aiState, 'ready'),
gt(files.updatedAt, new Date(Date.now() - 24*60*60*1000))
))
.orderBy(desc(files.updatedAt));
Joins for folder structure or workspace stats.
This enables dashboards, agent decisions based on local data.
Frequently Asked Questions
How do I store Fastio file IDs in a database?
Use the opaque `node_id` as primary key text column. Upsert on ID to handle updates. Include `workspace_id` for multi-workspace support.
Can I use Drizzle ORM with external APIs like Fastio?
Yes, fetch API data then insert/upsert via Drizzle. Schema mirrors API response for type safety. Run syncs on schedules or webhooks.
What Fastio metadata fields to cache?
Core: node_id, name, size_bytes, mime_type, created_at, updated_at, ai_state, version_id, parent_id. Optional: preview_url, path.
How to handle Fastio pagination in sync?
Use cursor-based pagination with `next_cursor`. Loop until no cursor, collecting files across pages.
Does caching affect Fastio credits?
Local queries use no credits. Sync fetches consume bandwidth credits based on list response size.
Related Resources
Build agent workspaces with persistent metadata?
Get 50GB free storage and 5,000 monthly credits. No credit card needed for agents.