Data Storage Solutions

Why Do You Need a Database?

You build a Todo App. The user adds a few items, refreshes the page — they're all gone.

Why? Because browser data lives in memory. When the page refreshes, memory is cleared, and everything resets to zero.

That's what databases solve. They store data persistently — whether the user closes the browser, switches computers, or comes back a year later, the data is still there.

User accounts, posts, orders, uploaded images — all of these need to go into a database. Without one, your product is just a toy.

---

Supabase: The Best Partner for Beginners

There are many database options, but for beginners in AI-powered development, I strongly recommend Supabase.

First, it's free. The free tier gives you 500MB of database space, 1GB of file storage, and 50,000 monthly active users. More than enough for personal projects and MVPs.

Second, it's simple. Traditional databases require setting up servers, configuring environments, and writing raw SQL. Supabase is a cloud service — sign up and start using it. It has a visual dashboard for managing data like a spreadsheet.

Third, it's full-featured. Beyond the database, Supabase has built-in user authentication, file storage, and real-time subscriptions. One platform handles most of your backend needs.

Fourth, it's built on PostgreSQL. This is one of the world's most widely used open-source databases. What you learn in Supabase will transfer to other platforms.

---

Complete Supabase Setup Guide

Step 1: Create an Account

Go to supabase.com, click "Start your project," and sign in with your GitHub account. After logging in, you'll see the Dashboard listing all your projects.

Step 2: Create a Project

Click "New Project" and fill in:

• Organization: Select your org (auto-created on first use)
• Project name: e.g., my-blog-app
• Database Password: Write this down!
• Region: Choose the one closest to your users

Click "Create new project" and wait a minute or two. The project Dashboard will have entries for Table Editor, Authentication, Storage, SQL Editor, etc.

Step 3: Find Your API Keys

Click "Project Settings" (gear icon) on the left → "API" and find:

• Project URL: Looks like https://xxxx.supabase.co
• anon / public key: A long string

Create .env.local in your Next.js project root:

NEXT_PUBLIC_SUPABASE_URL=https://xxxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=***...

The NEXT_PUBLIC_ prefix is a Next.js convention indicating the variable is exposed to the frontend. The anon key is designed to be public — it's safe when used with RLS.

Step 4: Create a Table

In the left menu, click "Table Editor" → "New Table." For a blog system, create a posts table:

Column	Type	Description
id	int8 (auto-increment)	Primary key, auto-generated
created_at	timestamp	Creation time, auto-generated
title	text	Article title
content	text	Article content
author	text	Author
published	boolean	Whether published, defaults to false

Or create the table with SQL in the SQL Editor:

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  title TEXT NOT NULL,
  content TEXT,
  author TEXT NOT NULL,
  published BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_posts_author ON posts(author);

Step 5: Install the Client and Connect

npm install @supabase/supabase-js

Create lib/supabase.ts:

import { createClient } from '@supabase/supabase-js'

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

This file only needs to be created once — other files just import from it.

---

CRUD: The Four Operations That Handle Everything

The four core database operations: Create, Read, Update, Delete — abbreviated as CRUD.

Create: Store Data

const { data, error } = await supabase
  .from('posts')
  .insert({
    title: 'My First Blog Post',
    content: 'Starting my blog today...',
    author: 'Alex'
  })
  .select()  // Adding select() returns the inserted data (including auto-generated id)

if (error) {
  console.error('Creation failed:', error.message)
}

Read: Retrieve Data

// Get all posts
const { data: posts } = await supabase.from('posts').select('*')

// Filter by condition
const { data: myPosts } = await supabase
  .from('posts').select('*').eq('author', 'Alex')

// Select specific fields
const { data: titles } = await supabase
  .from('posts').select('id, title, author')

// Pagination + sorting
const { data: page1 } = await supabase
  .from('posts').select('*')
  .order('created_at', { ascending: false })
  .range(0, 9)

// Fuzzy search
const { data: results } = await supabase
  .from('posts').select('*').ilike('title', '%blog%')

Quick reference for common filters:

Method	Purpose	Example
.eq()	Equals	.eq('author', 'Alex')
.gt() / .lt()	Greater than / Less than	.gt('id', 10)
.in()	In list	.in('author', ['Alex', 'Sam'])
.ilike()	Fuzzy match	.ilike('title', '%keyword%')
.is()	Check null	.is('deleted_at', null)
.or()	OR condition	.or('author.eq.Alex,published.eq.true')

Update: Modify Existing Data

const { data, error } = await supabase
  .from('posts')
  .update({ title: 'Updated Title', published: true })
  .eq('id', 1)
  .select()

Important: update must be paired with .eq()! Missing the filter condition will update every row in the table. This is one of the most common beginner mistakes.

Delete: Remove Data

// Hard delete
const { error } = await supabase.from('posts').delete().eq('id', 1)

// Recommended: soft delete (add a deleted_at field instead of actually deleting)
const { error } = await supabase
  .from('posts')
  .update({ deleted_at: new Date().toISOString() })
  .eq('id', 1)

// Filter out deleted records when querying
const { data: posts } = await supabase
  .from('posts').select('*').is('deleted_at', null)

Soft deletion is better because data can be recovered if something goes wrong.

These four operations handle 90% of your data management needs.

---

Row Level Security (RLS): Your Data Security Moat

By default, anyone who knows your anon key can read and write all your table data. This is extremely dangerous. RLS ensures each user can only operate on their own data.

Enable RLS

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Once enabled, no users can access the data by default (until you set policies).

Create Policies

-- Anyone can read published posts
CREATE POLICY "Public posts are readable" ON posts FOR SELECT
USING (published = true);

-- Users can read all their own posts
CREATE POLICY "Users can read own posts" ON posts FOR SELECT
USING (auth.uid() = user_id);

-- Users can only create their own posts
CREATE POLICY "Users can create own posts" ON posts FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can only update their own posts
CREATE POLICY "Users can update own posts" ON posts FOR UPDATE
USING (auth.uid() = user_id);

-- Users can only delete their own posts
CREATE POLICY "Users can delete own posts" ON posts FOR DELETE
USING (auth.uid() = user_id);

auth.uid() is a Supabase built-in function that returns the current logged-in user's ID.

RLS is mandatory. If your app has user login, you must enable RLS. It should be set up when you create the table, not "added later."

---

Real-Time Subscriptions: Auto-Notify on Data Changes

Supabase supports real-time monitoring of database changes — great for chat rooms, collaborative editing, live dashboards, and more.

import { useEffect, useState } from 'react'

function PostList() {
  const [posts, setPosts] = useState([])

  useEffect(() => {
    // Initial load
    supabase.from('posts').select('*').then(({ data }) => setPosts(data || []))

    // Subscribe to real-time changes
    const channel = supabase
      .channel('realtime-posts')
      .on('postgres_changes', { event: '*', schema: 'public', table: 'posts' }, (payload) => {
        if (payload.eventType === 'INSERT') {
          setPosts(prev => [payload.new as any, ...prev])
        } else if (payload.eventType === 'UPDATE') {
          setPosts(prev => prev.map(p => p.id === payload.new.id ? payload.new : p))
        } else if (payload.eventType === 'DELETE') {
          setPosts(prev => prev.filter(p => p.id !== payload.old.id))
        }
      })
      .subscribe()

    return () => { supabase.removeChannel(channel) }
  }, [])

  return <div>{posts.map(post => <div key={post.id}>{post.title}</div>)}</div>
}

Note: Real-time features require you to enable Realtime for the target table in Supabase Dashboard → Database → Replication. It's not enabled by default.

---

Storage: File Uploads

User avatars, article images, PDF documents, and other files use Supabase Storage.

// Upload an image
const file = event.target.files[0]
const fileName = `${Date.now()}-${file.name}`

const { data, error } = await supabase.storage
  .from('images')  // Bucket name
  .upload(fileName, file)

// Get public URL
const { data: urlData } = supabase.storage
  .from('images')
  .getPublicUrl(fileName)

console.log('Image URL:', urlData.publicUrl)

// Delete a file
await supabase.storage.from('images').remove(['old-photo.png'])

In the Dashboard, click "Storage" → "New Bucket" to create a storage bucket. Set to Public for public images, Private for private files. Combined with RLS, you can implement "each user can only access their own files."

---

Database Design Basics

Three Principles of Table Design

Principle 1: Each table stores one type of thing. Users table for users, orders table for orders — don't mix them.

Principle 2: Use IDs to link, don't duplicate data. Store user_id in orders, not the username again.

-- ❌ Duplicated user info
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_name TEXT,
  user_email TEXT,
  product_name TEXT,
  amount DECIMAL
);

-- ✅ Linked by ID
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  product_id BIGINT REFERENCES products(id),
  amount DECIMAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Principle 3: Choose the right field types. Use DECIMAL for prices (not FLOAT), TIMESTAMPTZ for timestamps (with timezone), BOOLEAN for true/false values.

Universal Table Template

CREATE TABLE your_table (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ,
  -- business fields...
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at BEFORE UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

---

Choosing a Database Solution

Feature	Supabase	Firebase	PlanetScale	Neon
Database type	PostgreSQL	Firestore (document)	MySQL	PostgreSQL
Free tier	500MB + 1GB storage	1GB storage	Free tier removed	512MB
Real-time	✅ Built-in	✅ Best	❌	❌
Auth/Storage	✅ Built-in	✅ Built-in	❌ Self-build	❌ Self-build

Firebase: Google's product, strongest real-time capabilities, great for chat/collaboration apps. But it's a document database (NoSQL) — complex queries aren't as intuitive as relational databases.

PlanetScale: MySQL cloud database with good performance and database branching. But the free tier has been removed — better for teams with a budget.

Neon: PostgreSQL cloud service with Git-like branching, good for development and testing.

My advice: For most personal projects and MVPs, use Supabase. Don't overthink "which database" — just build something.

---

Common Pitfalls

Pitfall 1: Forgetting to enable RLS. First thing after creating a table: enable RLS. Otherwise anyone with the API key can read and write all data.

Pitfall 2: Update/delete without conditions. .update({ ... }) without .eq() updates the entire table. .delete() without .eq() deletes the entire table.

Pitfall 3: Exposing the service_role key. Supabase has two keys: anon key (public, safe with RLS) and service_role key (bypasses RLS). The service_role key must only be used on the backend and must never have the NEXT_PUBLIC_ prefix.

Pitfall 4: N+1 queries. Fetching 20 posts then querying each author separately. Use relational queries instead:

const { data } = await supabase
  .from('posts')
  .select('*, author:users(name, avatar_url)')

Pitfall 5: Not handling errors. Always check for errors:

const { data, error } = await supabase.from('posts').select('*')
if (error) {
  console.error('Query failed:', error.message)
  return []
}

Pitfall 6: Missing environment variables in production. Works locally but fails after deploy — 99% of the time it's because Vercel's Environment Variables don't have the Supabase URL and Key.

---

Final Words

Databases sound technical, but modern tools have lowered the barrier to the floor. The core is four operations: create, read, update, delete. Master CRUD, and you've mastered 90% of data management.

Store your data first — give your product "memory." That's what matters most.