Skip to main content

Database Configuration

Introduction

Almost every modern web application interacts with a database. Vania makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder, and the ORM.

Currently, Vania provides first-party support for four databases:

MariaDB 10.3+
MySQL 5.7+
PostgreSQL 10.0+
SQLite 3.26.0+

Configuration Overview

The configuration for Vania's database services is located in your application's lib/config/database.dart configuration file. In this file, you may define all of your database connections, as well as specify which connection should be used by default. Most of the configuration options within this file are driven by the values of your application's environment variables.

Environment Variables

In the .env file, you can specify your database information. Here's a complete list of available options:

Variable	Description	Default	Required
`DB_CONNECTION`	Database driver (mysql, postgresql, pgsql, sqlite)	mysql	✅
`DB_HOST`	Database host address	localhost	✅
`DB_PORT`	Database port number	3306	✅
`DB_NAME`	Database name	-	✅
`DB_USERNAME`	Database username	-	✅
`DB_PASSWORD`	Database password	-	✅
`DB_SECURE`	Enable SSL/TLS connection	false	❌
`DB_POOL`	Enable connection pooling	true	❌
`DB_POOL_SIZE`	Maximum pool connections	2	❌
`DB_COLLATION`	Database collation	utf8mb4_general_ci	❌
`DB_CHARSET`	Database character set	utf8mb4	❌
`DB_SSL_MODE`	SSL mode (PostgreSQL)	prefer	❌

Example .env Configuration

# MySQL Configuration
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=vania_app
DB_USERNAME=root
DB_PASSWORD=your_password
DB_SECURE=false
DB_POOL=true
DB_POOL_SIZE=5
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci

# PostgreSQL Configuration  
# DB_CONNECTION=pgsql
# DB_HOST=localhost
# DB_PORT=5432
# DB_NAME=vania_app
# DB_USERNAME=postgres
# DB_PASSWORD=your_password
# DB_SSL_MODE=prefer

# SQLite Configuration
# DB_CONNECTION=sqlite
# DB_NAME=database.sqlite
# DB_FILE_PATH=./database/
# SQLITE_OPEN_IN_MEMORY=false

Database Configuration Structure

The lib/config/database.dart file contains the main database configuration. Here's the complete structure:

import 'package:vania/vania.dart' show env;

Map<String, dynamic> database = {
  // Default connection to use
  "default": env<String>('DB_CONNECTION', 'mysql'),
  
  // Additional connections (optional)
  "additional_connections": <String>[
    // "secondary_mysql",
    // "analytics_postgres"
  ],
  
  // Database connections configuration
  "connections": {
    // MySQL Connection
    'mysql': {
      'driver': 'mysql',
      'host': env<String>('DB_HOST', '127.0.0.1'),
      'port': env<int>('DB_PORT', 3306),
      'database': env<String>('DB_NAME', 'forge'),
      'username': env<String>('DB_USERNAME', 'forge'),
      'password': env<String>('DB_PASSWORD', ''),
      'sslmode': env<bool>('DB_SECURE', false),
      'charset': env<String>('DB_CHARSET', 'utf8mb4'),
      'collation': env<String>('DB_COLLATION', 'utf8mb4_unicode_ci'),
      'pool': env<bool>('DB_POOL', true),
      'poolsize': env<int>('DB_POOL_SIZE', 5),
    },
    
    // PostgreSQL Connection
    'pgsql': {
      'driver': 'pgsql',
      'host': env<String>('DB_HOST', '127.0.0.1'),
      'port': env<int>('DB_PORT', 5432),
      'database': env<String>('DB_NAME', 'forge'),
      'username': env<String>('DB_USERNAME', 'forge'),
      'password': env<String>('DB_PASSWORD', ''),
      'sslmode': env<String>('DB_SSL_MODE', 'prefer'),
      'pool': env<bool>('DB_POOL', true),
      'poolsize': env<int>('DB_POOL_SIZE', 3),
    },
    
    // SQLite Connection
    'sqlite': {
      'driver': 'sqlite',
      'database': env<String>('DB_NAME', 'database.sqlite'),
      'file_path': env<String>('DB_FILE_PATH', './database/'),
      'openInMemorySQLite': env<bool>('SQLITE_OPEN_IN_MEMORY', false),
    }
  }
};

Database-Specific Configurations

MySQL/MariaDB Configuration

MySQL is the default database driver for Vania. Here's a complete MySQL configuration:

'mysql': {
  'driver': 'mysql',
  'host': env<String>('DB_HOST', '127.0.0.1'),
  'port': env<int>('DB_PORT', 3306),
  'database': env<String>('DB_NAME'),
  'username': env<String>('DB_USERNAME'),
  'password': env<String>('DB_PASSWORD'),
  'charset': env<String>('DB_CHARSET', 'utf8mb4'),
  'collation': env<String>('DB_COLLATION', 'utf8mb4_unicode_ci'),
  'pool': env<bool>('DB_POOL', true),
  'poolsize': env<int>('DB_POOL_SIZE', 5),
  'sslmode': env<bool>('DB_SECURE', false),
  'timeout': env<int>('DB_TIMEOUT', 30),
}

MySQL Environment Variables:

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci
DB_POOL=true
DB_POOL_SIZE=5
DB_SECURE=false
DB_TIMEOUT=30

PostgreSQL Configuration

PostgreSQL is a powerful, open-source relational database:

'pgsql': {
  'driver': 'pgsql',
  'host': env<String>('DB_HOST', '127.0.0.1'),
  'port': env<int>('DB_PORT', 5432),
  'database': env<String>('DB_NAME'),
  'username': env<String>('DB_USERNAME'),
  'password': env<String>('DB_PASSWORD'),
  'sslmode': env<String>('DB_SSL_MODE', 'prefer'),
  'pool': env<bool>('DB_POOL', true),
  'poolsize': env<int>('DB_POOL_SIZE', 3),
  'timeout': env<int>('DB_TIMEOUT', 30),
}

PostgreSQL SSL Modes:

disable: No SSL connection
allow: Try non-SSL, then SSL
prefer: Try SSL, then non-SSL (default)
require: SSL required
verify-ca: SSL required with CA verification
verify-full: SSL required with full verification

PostgreSQL Environment Variables:

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database
DB_USERNAME=postgres
DB_PASSWORD=your_password
DB_SSL_MODE=prefer
DB_POOL=true
DB_POOL_SIZE=3
DB_TIMEOUT=30

SQLite Configuration

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

'sqlite': {
  'driver': 'sqlite',
  'database': env<String>('DB_NAME', 'database.sqlite'),
  'file_path': env<String>('DB_FILE_PATH', './database/'),
  'openInMemorySQLite': env<bool>('SQLITE_OPEN_IN_MEMORY', false),
}

SQLite Environment Variables:

DB_CONNECTION=sqlite
DB_NAME=database.sqlite
DB_FILE_PATH=./database/
SQLITE_OPEN_IN_MEMORY=false

Working with Multiple Databases

Vania supports working with multiple database connections simultaneously. This is useful when you need to:

Store different types of data in separate databases
Connect to both MySQL and PostgreSQL in the same application
Use SQLite for local development and PostgreSQL for production
Separate analytics data from application data

Configuring Multiple Connections

Define additional connections in your config:

"additional_connections": <String>[
  "analytics_db",
  "logs_db",
  "cache_db"
],

"connections": {
  // Default MySQL connection
  'mysql': {
    'driver': 'mysql',
    'host': env<String>('DB_HOST', '127.0.0.1'),
    'port': env<int>('DB_PORT', 3306),
    'database': env<String>('DB_NAME'),
    'username': env<String>('DB_USERNAME'),
    'password': env<String>('DB_PASSWORD'),
    'pool': env<bool>('DB_POOL', true),
    'poolsize': env<int>('DB_POOL_SIZE', 5),
  },
  
  // Analytics PostgreSQL connection
  "analytics_db": {
    'driver': 'pgsql',
    'host': env<String>('ANALYTICS_DB_HOST', '127.0.0.1'),
    'port': env<int>('ANALYTICS_DB_PORT', 5432),
    'database': env<String>('ANALYTICS_DB_NAME', 'analytics'),
    'username': env<String>('ANALYTICS_DB_USERNAME', 'analytics_user'),
    'password': env<String>('ANALYTICS_DB_PASSWORD', ''),
    'sslmode': env<String>('ANALYTICS_DB_SSL_MODE', 'prefer'),
    'pool': env<bool>('ANALYTICS_DB_POOL', true),
    'poolsize': env<int>('ANALYTICS_DB_POOL_SIZE', 3),
  },
  
  // Logs SQLite connection
  "logs_db": {
    'driver': 'sqlite',
    'database': env<String>('LOGS_DB_NAME', 'logs.sqlite'),
    'file_path': env<String>('LOGS_DB_PATH', './storage/logs/'),
    'openInMemorySQLite': false,
  }
}

Environment Variables for Multiple Connections:

# Default Database (MySQL)
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=main_app
DB_USERNAME=root
DB_PASSWORD=password
DB_POOL=true
DB_POOL_SIZE=5

# Analytics Database (PostgreSQL)
ANALYTICS_DB_HOST=analytics.example.com
ANALYTICS_DB_PORT=5432
ANALYTICS_DB_NAME=analytics
ANALYTICS_DB_USERNAME=analytics_user
ANALYTICS_DB_PASSWORD=analytics_password
ANALYTICS_DB_SSL_MODE=require
ANALYTICS_DB_POOL=true
ANALYTICS_DB_POOL_SIZE=3

# Logs Database (SQLite)
LOGS_DB_NAME=logs.sqlite
LOGS_DB_PATH=./storage/logs/

Using Different Connections in Your Code:

// Use default connection
final users = await DB.table('users').get();

// Use specific connection
final analytics = await DB.connection('analytics_db')
    .table('page_views')
    .get();

final logs = await DB.connection('logs_db')
    .table('error_logs')
    .get();

// Using with models
class User extends Model {
  // Uses default connection
}

class PageView extends Model {
  @override
  String get defaultConnection = 'analytics_db';
}

class ErrorLog extends Model {
  @override
  String get defaultConnection = 'logs_db';
}

Introduction
Configuration Overview
- Environment Variables
- Example .env Configuration
Database Configuration Structure
Database-Specific Configurations
Working with Multiple Databases
- Configuring Multiple Connections