Database Support

Fakestack supports 6 major database systems with native drivers and optimized connections:

SQLite - Lightweight, file-based database
MySQL - Popular open-source relational database
PostgreSQL - Advanced open-source relational database
MariaDB - MySQL-compatible database with enhanced features
MS SQL Server - Microsoft's enterprise database system
CockroachDB - Distributed SQL database (PostgreSQL-compatible)

SQLite

Overview

SQLite is a lightweight, file-based database perfect for development, testing, and small applications.

Pros:

No server setup required
Single file storage
Fast for small to medium datasets
Cross-platform compatible

Cons:

Limited concurrent write access
No network access
Not suitable for high-concurrency applications

Configuration

{
  "database": {
    "dbtype": "sqlite",
    "drivername": "sqlite",
    "database": "path/to/database.db"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"sqlite"`
`drivername`	Yes	-	Must be `"sqlite"`
`database`	Yes	-	Path to database file

File Paths

Relative path:

"database": "myapp.db"

Absolute path:

"database": "/Users/username/databases/myapp.db"

In-memory (temporary):

"database": ":memory:"

Usage Example

Python:

from fakestack import Fakestack

schema = {
    "database": {
        "dbtype": "sqlite",
        "drivername": "sqlite",
        "database": "test.db"
    },
    "tables": [...],
    "populate": [...]
}

faker = Fakestack(schema)
faker.run()

Node.js:

const Fakestack = require('fakestack');

const schema = {
    database: {
        dbtype: 'sqlite',
        drivername: 'sqlite',
        database: 'test.db'
    },
    tables: [...],
    populate: [...]
};

const faker = new Fakestack(schema);
faker.run();

CLI:

fakestack schema.json

MySQL

Overview

MySQL is a popular open-source relational database management system ideal for web applications.

Pros:

High performance
Mature and widely supported
Good for high-concurrency
Scales well

Cons:

Requires server setup
More resource intensive
More complex configuration

Configuration

{
  "database": {
    "dbtype": "mysql",
    "drivername": "mysql+mysqlconnector",
    "username": "root",
    "password": "password",
    "host": "localhost",
    "port": 3306,
    "database": "myapp"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"mysql"`
`drivername`	Yes	-	Must be `"mysql+mysqlconnector"`
`username`	Yes	-	MySQL username
`password`	Yes	-	MySQL password
`host`	Yes	`"localhost"`	Server host
`port`	No	`3306`	Server port
`database`	Yes	-	Database name

Prerequisites

Install MySQL Server:

# macOS (Homebrew)
brew install mysql
brew services start mysql

# Ubuntu/Debian
sudo apt-get install mysql-server
sudo systemctl start mysql

# Windows
# Download from: https://dev.mysql.com/downloads/installer/

Create Database:
```
mysql -u root -p
CREATE DATABASE myapp;
```

Create User (optional):

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Connection Examples

Local development:

{
  "database": {
    "dbtype": "mysql",
    "drivername": "mysql+mysqlconnector",
    "username": "root",
    "password": "password",
    "host": "localhost",
    "port": 3306,
    "database": "myapp_dev"
  }
}

Remote server:

{
  "database": {
    "dbtype": "mysql",
    "drivername": "mysql+mysqlconnector",
    "username": "app_user",
    "password": "secure_password",
    "host": "db.example.com",
    "port": 3306,
    "database": "production_db"
  }
}

Docker container:

{
  "database": {
    "dbtype": "mysql",
    "drivername": "mysql+mysqlconnector",
    "username": "root",
    "password": "root",
    "host": "127.0.0.1",
    "port": 3306,
    "database": "test_db"
  }
}

Docker Setup

# Start MySQL container
docker run -d \
  --name mysql-test \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=myapp \
  -p 3306:3306 \
  mysql:8.0

# Wait for container to be ready
docker logs -f mysql-test

# Run Fakestack
fakestack schema.json

PostgreSQL

Overview

PostgreSQL is a powerful, advanced open-source relational database with strong standards compliance.

Pros:

Advanced features (JSON, arrays, full-text search)
ACID compliant
Extensible
Great for complex queries

Cons:

Requires server setup
Can be resource intensive
Steeper learning curve

Configuration

{
  "database": {
    "dbtype": "postgresql",
    "drivername": "postgresql+psycopg2",
    "username": "postgres",
    "password": "password",
    "host": "localhost",
    "port": 5432,
    "database": "myapp"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"postgresql"`
`drivername`	Yes	-	Must be `"postgresql+psycopg2"`
`username`	Yes	-	PostgreSQL username
`password`	Yes	-	PostgreSQL password
`host`	Yes	`"localhost"`	Server host
`port`	No	`5432`	Server port
`database`	Yes	-	Database name

Prerequisites

Install PostgreSQL:

# macOS (Homebrew)
brew install postgresql
brew services start postgresql

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows
# Download from: https://www.postgresql.org/download/windows/

Create Database:

# Connect as postgres user
sudo -u postgres psql

# Create database
CREATE DATABASE myapp;

# Create user (optional)
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

Connection Examples

Local development:

{
  "database": {
    "dbtype": "postgresql",
    "drivername": "postgresql+psycopg2",
    "username": "postgres",
    "password": "postgres",
    "host": "localhost",
    "port": 5432,
    "database": "myapp_dev"
  }
}

Remote server:

{
  "database": {
    "dbtype": "postgresql",
    "drivername": "postgresql+psycopg2",
    "username": "app_user",
    "password": "secure_password",
    "host": "postgres.example.com",
    "port": 5432,
    "database": "production_db"
  }
}

Docker container:

{
  "database": {
    "dbtype": "postgresql",
    "drivername": "postgresql+psycopg2",
    "username": "postgres",
    "password": "postgres",
    "host": "127.0.0.1",
    "port": 5432,
    "database": "test_db"
  }
}

Docker Setup

# Start PostgreSQL container
docker run -d \
  --name postgres-test \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:15

# Wait for container to be ready
docker logs -f postgres-test

# Run Fakestack
fakestack schema.json

Comparison

Feature	SQLite	MySQL	PostgreSQL
Setup Complexity	✅ None	⚠️ Medium	⚠️ Medium
Performance	✅ Fast (small data)	✅ Fast	✅ Fast
Concurrent Writes	❌ Limited	✅ Excellent	✅ Excellent
Data Types	⚠️ Basic	✅ Good	✅ Advanced
Storage	File	Server	Server
Best For	Development, Testing	Web Apps, APIs	Complex Apps, Analytics
Network Access	❌ No	✅ Yes	✅ Yes
Resource Usage	✅ Low	⚠️ Medium	⚠️ Medium-High

Choosing a Database

Use SQLite when:

Developing or testing locally
Building a prototype
Small to medium dataset (< 1M rows)
Single-user or low-concurrency application
No server setup required

Use MySQL when:

Building web applications
High-concurrency read/write operations
Need proven reliability at scale
Team familiar with MySQL
Standard RDBMS features sufficient

Use PostgreSQL when:

Need advanced features (JSON, arrays, full-text search)
Complex queries and analytics
Data integrity is critical
Need extensibility
Want standards compliance

Connection String Format

For manual connections, here are the equivalent connection strings:

SQLite:

sqlite:///path/to/database.db

MySQL:

mysql+mysqlconnector://username:password@host:port/database

PostgreSQL:

postgresql+psycopg2://username:password@host:port/database

MariaDB

Overview

MariaDB is a MySQL-compatible database with enhanced performance, security features, and additional storage engines.

Pros:

Drop-in MySQL replacement
Better performance than MySQL in many workloads
More storage engines (Aria, ColumnStore, etc.)
Active open-source development

Cons:

Some MySQL features not available
Less widely adopted than MySQL

Configuration

{
  "database": {
    "dbtype": "mariadb",
    "username": "root",
    "password": "yourpassword",
    "host": "localhost:3306",
    "database": "testdb"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"mariadb"`
`username`	Yes	-	Database user
`password`	Yes	-	User password
`host`	Yes	-	Host and port (e.g., `localhost:3306`)
`database`	Yes	-	Database name

Usage Example

# Create and populate
fakestack -c -p -f schema.json

Schema example:

{
  "database": {
    "dbtype": "mariadb",
    "username": "root",
    "password": "password",
    "host": "localhost:3306",
    "database": "mydb"
  },
  "tables": [...],
  "populate": [...]
}

MS SQL Server

Overview

Microsoft SQL Server is an enterprise-grade relational database management system with advanced analytics and integration capabilities.

Pros:

Enterprise features and support
Excellent Windows integration
Advanced analytics and BI tools
Strong security features

Cons:

Primarily Windows-focused (Linux support improving)
Licensing costs for production
More resource-intensive

Configuration

{
  "database": {
    "dbtype": "mssql",
    "username": "sa",
    "password": "YourPassword123!",
    "host": "localhost:1433",
    "database": "testdb"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"mssql"`
`username`	Yes	-	Database user (typically `sa`)
`password`	Yes	-	Strong password required
`host`	Yes	-	Host and port (e.g., `localhost:1433`)
`database`	Yes	-	Database name

Docker Setup

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourPassword123!' \
  -p 1433:1433 --name mssql \
  mcr.microsoft.com/mssql/server:2022-latest

Usage Example

fakestack -c -p -f mssql-schema.json

Special Considerations

Password requirements: Must be strong (uppercase, lowercase, numbers, special chars)
IDENTITY columns: Use autoincrement: true for auto-incrementing primary keys
Connection timeout: May need longer timeout for first connection

CockroachDB

Overview

CockroachDB is a distributed SQL database that is PostgreSQL-compatible and designed for cloud-native applications.

Pros:

Horizontal scalability
Built-in replication and consistency
PostgreSQL wire protocol compatibility
Resilient to node failures

Cons:

More complex setup than traditional databases
Different performance characteristics
Some PostgreSQL features not supported

Configuration

{
  "database": {
    "dbtype": "cockroachdb",
    "username": "root",
    "password": "",
    "host": "localhost:26257",
    "database": "testdb"
  }
}

Connection Options

Option	Required	Default	Description
`dbtype`	Yes	-	Must be `"cockroachdb"`
`username`	Yes	-	Database user (default: `root`)
`password`	No	`""`	Password (empty for insecure mode)
`host`	Yes	-	Host and port (default: `26257`)
`database`	Yes	-	Database name

Docker Setup

# Start single-node cluster (insecure for testing)
docker run -d -p 26257:26257 -p 8080:8080 \
  --name cockroach \
  cockroachdb/cockroach:latest start-single-node --insecure

# Create database
docker exec -it cockroach ./cockroach sql --insecure \
  --execute="CREATE DATABASE testdb;"

Usage Example

fakestack -c -p -f cockroachdb-schema.json

Special Considerations

Uses PostgreSQL driver internally
Default port is 26257 (not 5432)
Supports most PostgreSQL SQL syntax
Better suited for distributed deployments

Troubleshooting

SQLite

File permissions:

chmod 644 database.db

Database locked:

Close all connections
Check for other processes using the file
Use PRAGMA busy_timeout

MySQL

Connection refused:

# Check if MySQL is running
brew services list  # macOS
systemctl status mysql  # Linux

# Check port
netstat -an | grep 3306

Access denied:

# Check user permissions
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'username'@'localhost';

PostgreSQL

Connection refused:

# Check if PostgreSQL is running
brew services list  # macOS
systemctl status postgresql  # Linux

# Check port
netstat -an | grep 5432

Authentication failed:

# Edit pg_hba.conf
# Change 'peer' to 'md5' for password auth
sudo nano /etc/postgresql/*/main/pg_hba.conf
sudo systemctl restart postgresql

Performance Tips

Use Indexes: Add indexes on frequently queried columns
Batch Inserts: Fakestack uses batch inserts for optimal performance
Connection Pooling: For repeated operations, keep connections alive
Appropriate Data Types: Use the right column types for your data
Database Tuning: Configure your database for your workload

Uh oh!

FilesExpand file tree

databases.md

Latest commit

History

databases.md

File metadata and controls

Database Support

SQLite

Overview

Configuration

Connection Options

File Paths

Usage Example

MySQL

Overview

Configuration

Connection Options

Prerequisites

Connection Examples

Docker Setup

PostgreSQL

Overview

Configuration

Connection Options

Prerequisites

Connection Examples

Docker Setup

Comparison

Choosing a Database

Use SQLite when:

Use MySQL when:

Use PostgreSQL when:

Connection String Format

MariaDB

Overview

Configuration

Connection Options

Usage Example

MS SQL Server

Overview

Configuration

Connection Options

Docker Setup

Usage Example

Special Considerations

CockroachDB

Overview

Configuration

Connection Options

Docker Setup

Usage Example

Special Considerations

Troubleshooting

SQLite

MySQL

PostgreSQL

Performance Tips

See Also