From JSON Files to PostgreSQL: Why Proper Databases Matter

When I first built Community Manager, I took a shortcut. Content was stored in JSON files on disk. It worked. It was fast to prototype. But it was never going to scale.

Today, I'm migrating it to PostgreSQL. Here's why, and what I learned.

The Problem with JSON Files

File-based storage seems simple:

typescript

<span class="hljs-comment">// Write content</span>
<span class="hljs-keyword">await</span> fs.<span class="hljs-title function_">writeFile</span>(<span class="hljs-string">&#x27;data/content.json&#x27;</span>, <span class="hljs-title class_">JSON</span>.<span class="hljs-title function_">stringify</span>(content))

<span class="hljs-comment">// Read content</span>
<span class="hljs-keyword">const</span> content = <span class="hljs-title class_">JSON</span>.<span class="hljs-title function_">parse</span>(<span class="hljs-keyword">await</span> fs.<span class="hljs-title function_">readFile</span>(<span class="hljs-string">&#x27;data/content.json&#x27;</span>))

Clean. Fast. No database server needed.

But then reality hits:

1. Concurrency is a Nightmare

Two users editing content at the same time? Last write wins. One user's changes disappear. No transactions. No conflict resolution. Just data loss.

2. Queries are Slow

Want to find all content by a specific project? You read the entire file, parse it, filter it in memory. Every. Single. Time.

With 100 content items, it's fine. With 10,000? Your app grinds to a halt.

3. No Data Integrity

Foreign keys? Constraints? Validation? You're on your own. Nothing stops you from creating content linked to a project that doesn't exist.

4. Backup and Recovery

Files can corrupt. A partial write during a crash? Your entire dataset is toast. No point-in-time recovery. No replication.

5. Search is Terrible

Full-text search across content? Write your own indexing system or load everything into memory. Good luck with that at scale.

Why PostgreSQL

PostgreSQL solves all of these problems:

Transactions

typescript

<span class="hljs-keyword">await</span> prisma.$transaction(<span class="hljs-title function_">async</span> (tx) =&gt; {
  <span class="hljs-keyword">const</span> content = <span class="hljs-keyword">await</span> tx.<span class="hljs-property">content</span>.<span class="hljs-title function_">create</span>({ <span class="hljs-attr">data</span>: newContent })
  <span class="hljs-keyword">await</span> tx.<span class="hljs-property">project</span>.<span class="hljs-title function_">update</span>({ 
    <span class="hljs-attr">where</span>: { <span class="hljs-attr">id</span>: projectId },
    <span class="hljs-attr">data</span>: { <span class="hljs-attr">contentCount</span>: { <span class="hljs-attr">increment</span>: <span class="hljs-number">1</span> } }
  })
})

Both operations succeed or both fail. No partial state. No data inconsistency.

Proper Querying

typescript

<span class="hljs-comment">// Find all content from last week, sorted by engagement</span>
<span class="hljs-keyword">const</span> trending = <span class="hljs-keyword">await</span> prisma.<span class="hljs-property">content</span>.<span class="hljs-title function_">findMany</span>({
  <span class="hljs-attr">where</span>: {
    <span class="hljs-attr">createdAt</span>: { <span class="hljs-attr">gte</span>: <span class="hljs-keyword">new</span> <span class="hljs-title class_">Date</span>(<span class="hljs-title class_">Date</span>.<span class="hljs-title function_">now</span>() - <span class="hljs-number">7</span> * <span class="hljs-number">24</span> * <span class="hljs-number">60</span> * <span class="hljs-number">60</span> * <span class="hljs-number">1000</span>) },
    <span class="hljs-attr">platform</span>: <span class="hljs-string">&#x27;linkedin&#x27;</span>
  },
  <span class="hljs-attr">orderBy</span>: { <span class="hljs-attr">likes</span>: <span class="hljs-string">&#x27;desc&#x27;</span> },
  <span class="hljs-attr">take</span>: <span class="hljs-number">10</span>
})

Indexed. Optimized. Fast.

Data Integrity

prisma

model Content {
  id        String   @id @default(cuid())
  projectId String
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  title     String
  status    String   @default(&quot;draft&quot;)
  
  @@index([projectId])
  @@index([status])
}

Foreign keys enforce relationships. Indexes speed up queries. Cascading deletes keep things clean.

Full-Text Search

typescript

<span class="hljs-keyword">const</span> results = <span class="hljs-keyword">await</span> prisma.<span class="hljs-property">$queryRaw</span><span class="hljs-string">`
  SELECT * FROM &quot;Content&quot;
  WHERE to_tsvector(&#x27;english&#x27;, title || &#x27; &#x27; || body) @@ plainto_tsquery(&#x27;english&#x27;, <span class="hljs-subst">${query}</span>)
  ORDER BY ts_rank(to_tsvector(&#x27;english&#x27;, title || &#x27; &#x27; || body), plainto_tsquery(&#x27;english&#x27;, <span class="hljs-subst">${query}</span>)) DESC
`</span>

Built-in. Powerful. Production-ready.

The Migration Path

1. Design the Schema

Start with Prisma Schema. Model your data properly:

prisma

model Project {
  id        String    @id @default(cuid())
  name      String
  slug      String    @unique
  platforms String[]  // Array of connected platforms
  content   Content[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Content {
  id          String   @id @default(cuid())
  projectId   String
  project     Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  platform    String
  title       String
  body        String
  status      String   @default(&quot;draft&quot;)
  scheduledAt DateTime?
  publishedAt DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  @@index([projectId])
  @@index([status])
  @@index([platform])
}

2. Write a Migration Script

Read your JSON files. Insert into the database:

typescript

<span class="hljs-keyword">import</span> { <span class="hljs-title class_">PrismaClient</span> } <span class="hljs-keyword">from</span> <span class="hljs-string">&#x27;@prisma/client&#x27;</span>
<span class="hljs-keyword">import</span> { readdir, readFile } <span class="hljs-keyword">from</span> <span class="hljs-string">&#x27;fs/promises&#x27;</span>

<span class="hljs-keyword">const</span> prisma = <span class="hljs-keyword">new</span> <span class="hljs-title class_">PrismaClient</span>()

<span class="hljs-keyword">async</span> <span class="hljs-keyword">function</span> <span class="hljs-title function_">migrate</span>(<span class="hljs-params"></span>) {
  <span class="hljs-keyword">const</span> files = <span class="hljs-keyword">await</span> <span class="hljs-title function_">readdir</span>(<span class="hljs-string">&#x27;./data&#x27;</span>)
  
  <span class="hljs-keyword">for</span> (<span class="hljs-keyword">const</span> file <span class="hljs-keyword">of</span> files) {
    <span class="hljs-keyword">const</span> data = <span class="hljs-title class_">JSON</span>.<span class="hljs-title function_">parse</span>(<span class="hljs-keyword">await</span> <span class="hljs-title function_">readFile</span>(<span class="hljs-string">`./data/<span class="hljs-subst">${file}</span>`</span>, <span class="hljs-string">&#x27;utf-8&#x27;</span>))
    
    <span class="hljs-keyword">await</span> prisma.<span class="hljs-property">content</span>.<span class="hljs-title function_">create</span>({
      <span class="hljs-attr">data</span>: {
        <span class="hljs-attr">title</span>: data.<span class="hljs-property">title</span>,
        <span class="hljs-attr">body</span>: data.<span class="hljs-property">body</span>,
        <span class="hljs-attr">status</span>: data.<span class="hljs-property">status</span>,
        <span class="hljs-attr">project</span>: {
          <span class="hljs-attr">connectOrCreate</span>: {
            <span class="hljs-attr">where</span>: { <span class="hljs-attr">slug</span>: data.<span class="hljs-property">projectSlug</span> },
            <span class="hljs-attr">create</span>: { <span class="hljs-attr">name</span>: data.<span class="hljs-property">projectName</span>, <span class="hljs-attr">slug</span>: data.<span class="hljs-property">projectSlug</span> }
          }
        }
      }
    })
  }
}

<span class="hljs-title function_">migrate</span>()

3. Update Your API Routes

Replace file I/O with Prisma queries:

typescript

<span class="hljs-comment">// Before</span>
<span class="hljs-keyword">export</span> <span class="hljs-keyword">async</span> <span class="hljs-keyword">function</span> <span class="hljs-title function_">GET</span>(<span class="hljs-params"></span>) {
  <span class="hljs-keyword">const</span> content = <span class="hljs-title class_">JSON</span>.<span class="hljs-title function_">parse</span>(<span class="hljs-keyword">await</span> fs.<span class="hljs-title function_">readFile</span>(<span class="hljs-string">&#x27;data/content.json&#x27;</span>))
  <span class="hljs-keyword">return</span> <span class="hljs-title class_">Response</span>.<span class="hljs-title function_">json</span>(content)
}

<span class="hljs-comment">// After</span>
<span class="hljs-keyword">export</span> <span class="hljs-keyword">async</span> <span class="hljs-keyword">function</span> <span class="hljs-title function_">GET</span>(<span class="hljs-params"></span>) {
  <span class="hljs-keyword">const</span> content = <span class="hljs-keyword">await</span> prisma.<span class="hljs-property">content</span>.<span class="hljs-title function_">findMany</span>({
    <span class="hljs-attr">include</span>: { <span class="hljs-attr">project</span>: <span class="hljs-literal">true</span> },
    <span class="hljs-attr">orderBy</span>: { <span class="hljs-attr">createdAt</span>: <span class="hljs-string">&#x27;desc&#x27;</span> }
  })
  <span class="hljs-keyword">return</span> <span class="hljs-title class_">Response</span>.<span class="hljs-title function_">json</span>(content)
}

4. Deploy

Run migrations in production:

bash

npx prisma migrate deploy

No downtime. No data loss. Just better infrastructure.

Lessons Learned

Start with a proper database - Even for prototypes. Prisma makes it so easy, there's no excuse.
JSON files are fine for config - Settings, translations, static data. But not user-generated content.
Migrations are scary but necessary - Test on a copy of production data. Verify before deploying.
Use transactions - If multiple operations must succeed together, wrap them in a transaction.
Index your queries - Add indexes for every WHERE clause you use frequently.

What's Next

Community Manager is getting its database upgrade this week. Here's what I'm building on top of it:

Bulk operations - Approve 10 posts at once
Analytics - Which platforms perform best?
Content recommendations - AI-suggested posts based on past performance
Collaboration - Multiple users editing content safely

None of this is possible with JSON files.

Sometimes shortcuts cost more than they save. This is one of those times.

Follow along: I'm documenting the entire migration process. Next post: "Setting Up PostgreSQL on Hetzner and Optimizing for Production."