Database Setup Guide

Complete guide to setting up and configuring databases for your Flowfull backend.

Supported Databases

Flowfull supports multiple database types through Kysely ORM:

Database	Type	Best For
PostgreSQL	Traditional	Production, complex queries, full-text search
MySQL	Traditional	Production, compatibility, wide hosting support
LibSQL/Turso	Serverless	Edge deployments, low latency, global distribution
Neon	Serverless	Serverless Postgres, auto-scaling
PlanetScale	Serverless	Serverless MySQL, branching workflows

PostgreSQL Setup

Local Development

Docker

docker run --name flowfull-postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=flowfull \
  -p 5432:5432 \
  -d postgres:16

Homebrew (macOS)

brew install postgresql@16
brew services start postgresql@16
createdb flowfull

apt (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres createdb flowfull

Configuration

DATABASE_TYPE=postgresql
DATABASE_URL=postgresql://postgres:password@localhost:5432/flowfull

Install Driver

npm

npm install pg

yarn

yarn add pg

bun

bun add pg

MySQL Setup

Local Development

Docker

docker run --name flowfull-mysql \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=flowfull \
  -p 3306:3306 \
  -d mysql:8

Homebrew (macOS)

brew install mysql
brew services start mysql
mysql -u root -e "CREATE DATABASE flowfull;"

apt (Ubuntu/Debian)

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql -e "CREATE DATABASE flowfull;"

Configuration

DATABASE_TYPE=mysql
DATABASE_URL=mysql://root:password@localhost:3306/flowfull

Install Driver

npm

npm install mysql2

yarn

yarn add mysql2

bun

bun add mysql2

LibSQL/Turso Setup

Create Database

1. Sign up at turso.tech
2. Install Turso CLI:

curl -sSfL https://get.tur.so/install.sh | bash

3. Create database:

turso db create flowfull

4. Get connection URL:

turso db show flowfull --url

5. Get auth token:

turso db tokens create flowfull

Configuration

DATABASE_TYPE=libsql
DATABASE_URL=libsql://flowfull-your-org.turso.io
DATABASE_AUTH_TOKEN=your-auth-token-here

Install Driver

npm

npm install @libsql/client

yarn

yarn add @libsql/client

bun

bun add @libsql/client

Neon Setup

Create Database

1. Sign up at neon.tech
2. Create a new project
3. Copy connection string

Configuration

DATABASE_TYPE=postgresql
DATABASE_URL=postgresql://user:password@ep-xxx.neon.tech/flowfull?sslmode=require

Install Driver

Same as PostgreSQL:

bun add pg

PlanetScale Setup

Create Database

1. Sign up at planetscale.com
2. Create a new database
3. Create a branch (e.g., main)
4. Get connection string

Configuration

DATABASE_TYPE=mysql
DATABASE_URL=mysql://user:password@aws.connect.psdb.cloud/flowfull?ssl={"rejectUnauthorized":true}

Install Driver

Same as MySQL:

bun add mysql2

Database Initialization

Create Database Instance

// src/lib/database.ts
import { Kysely } from 'kysely';
import { PostgresDialect } from 'kysely';
import { Pool } from 'pg';

const dialect = new PostgresDialect({
  pool: new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,
  })
});

export const db = new Kysely<Database>({
  dialect,
});

Define Schema Types

// src/lib/database.types.ts
export interface Database {
  users: UsersTable;
  sessions: SessionsTable;
  // ... more tables
}

export interface UsersTable {
  id: string;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
}

export interface SessionsTable {
  id: string;
  user_id: string;
  expires_at: Date;
  created_at: Date;
}

Query Examples

Basic Queries

// Select with conditions
const users = await db
  .selectFrom('users')
  .select(['id', 'email', 'name'])
  .where('is_verified', '=', true)
  .execute();

// Insert
const newUser = await db
  .insertInto('users')
  .values({
    id: generateId(),
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returningAll()
  .executeTakeFirst();

// Update
await db
  .updateTable('users')
  .set({ name: 'Jane Doe' })
  .where('id', '=', userId)
  .execute();

// Delete
await db
  .deleteFrom('users')
  .where('id', '=', userId)
  .execute();

Advanced Queries

// Join tables
const postsWithAuthors = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .select([
    'posts.id',
    'posts.title',
    'users.name as author_name',
  ])
  .execute();

// Pagination
const page = 1;
const limit = 20;
const offset = (page - 1) * limit;

const posts = await db
  .selectFrom('posts')
  .selectAll()
  .orderBy('created_at', 'desc')
  .limit(limit)
  .offset(offset)
  .execute();

Best Practices

1. Use PostgreSQL or LibSQL/Turso for Production

• PostgreSQL: Best for traditional deployments with complex queries and high traffic
• LibSQL/Turso: Best for edge deployments, global distribution, and serverless architectures
• MySQL: Good alternative for compatibility and wide hosting support

2. Enable Connection Pooling

Configure appropriate pool sizes based on your traffic:

DATABASE_POOL_MIN=2
DATABASE_POOL_MAX=10

3. Use SSL in Production

Always enable SSL for production databases:

# PostgreSQL with SSL
DATABASE_URL=postgresql://user:password@host:5432/db?sslmode=require

# MySQL with SSL
DATABASE_URL=mysql://user:password@host:3306/db?ssl={"rejectUnauthorized":true}

4. Monitor Performance

• Track query performance
• Monitor connection pool usage
• Set up alerts for slow queries
• Use database-specific monitoring tools

5. Regular Backups

Set up automated backups for your production database. Most cloud providers offer automated backup solutions.

Next Steps

• Multi-Database Support - Learn about database abstraction
• Environment Config - Configure database connection
• Deployment - Deploy with database

Need Help?

• 🌐 Notside.com - Professional database architecture
• 📧 Email: contact@notside.com