Skip to Content
API v2Database

Database

Multi-Connection Setup

The application uses two MySQL database connections defined in config/database.php:

Main Connection (mysql)

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=housr_database_dev
DB_USERNAME=root
DB_PASSWORD=

This is the default connection and is shared with the v1 API (Housr-API) and Portal (Housr Portal). Tables include:

  • users — All Housr users (shared across v1, v2, Portal)
  • houses — Property listings
  • liked_houses — User property likes
  • cities — City data
  • feature_flags — Feature flag configuration
  • global_variables — Global settings
  • property_operators — Property management companies
  • universities — University data
  • uob_approved_agents — University-approved agents
  • website_enquiries — Property enquiries
  • bookable_viewings — Agent bookable viewing slots
  • bills_included_agents — Agents with included bills
  • mapped_houses — Spatial data for house locations (UK only, MySQL 8)
  • search_areas — Search area boundaries
  • screens — App screen configurations
  • housemate_groups — Housemate group memberships

Bills Connection (bills)

DB_BILLS_DRIVER=mysql
DB_DATABASE_BILLS=housr_bills_dev

A separate database for the bills feature, using the same host/credentials as the main DB but a different database name. Tables include:

  • v2_contracts — Bills contracts (address, bedrooms, dates, pricing)
  • v2_tenants — Tenants linked to contracts
  • bills_prices_selectable — Bills pricing by bedrooms/city
  • bill_regions — Regional billing configuration
  • quote_prefill_houses — Pre-filled quote data from partners
  • quote_prefill_tenants — Tenants from pre-filled quotes
  • agent_referral_codes — Agent referral codes

Shared Database with v1 and Portal

The main database is shared with the v1 API and Portal. This means:

  • Do not create migrations that modify tables owned by v1 or Portal without coordination
  • The User model in v2 maps to the same users table as v1
  • The House model reads from the same houses table populated by v1 and property feeds
  • Models that represent shared tables are in app/Models/ (not in a module)

Cross-Database Queries

The Properties module performs cross-database joins between the main DB and bills DB. The House::scopeWithBillsPrice() method joins houses (main DB) with bills_prices_selectable (bills DB) and bills_included_agents (main DB) to calculate the total property price including bills.

// From Modules/Properties/app/Models/House.php
$billsDatabase = config('database.connections.bills.database');
 
$query->leftJoin(
    DB::raw("`{$billsDatabase}`.`bills_prices_selectable`"),
    // ... join conditions
);

Bills Module DB Connection

Bills models that live in the bills database specify their connection explicitly:

class V2Contract extends Model
{
    protected $connection = 'bills';
    protected $table = 'v2_contracts';
}

Migrations

Module Migrations

Each module has its own migrations directory at Modules/*/database/migrations/. These are auto-discovered by the modules package (config/modules.php has auto-discover.migrations = true).

# Run all migrations (root + all modules)
php artisan migrate
 
# Run migrations for a specific module
php artisan module:migrate Bills
 
# Create a migration for a module
php artisan module:make-migration create_items_table YourModule

Root Migrations

Root-level migrations in database/migrations/ are for tables not owned by any specific module.

Bills Migration Connection

Bills migrations that create tables in the bills database must specify the connection:

Schema::connection('bills')->create('v2_contracts', function (Blueprint $table) {
    // ...
});

Testing Database

Tests use separate test databases configured in phpunit.xml:

<env name="DB_CONNECTION" value="mysql" />
<env name="DB_DATABASE" value="testing" />
<env name="DB_BILLS_DRIVER" value="mysql" />
<env name="DB_DATABASE_BILLS" value="testing_bills" />

The FeatureTestCase base class uses RefreshDatabase to reset both databases between tests.

Factories

Module Factories

Module-specific factories live in Modules/*/database/factories/:

// Modules/Bills/database/factories/QuotePrefillHouseFactory.php
namespace Modules\Bills\Database\Factories;
 
class QuotePrefillHouseFactory extends Factory
{
    protected $model = QuotePrefillHouse::class;
    // ...
}

Root Factories

Shared model factories live in database/factories/ (e.g., UserFactory).

Redis

Redis is configured for caching (REDIS_CACHE_DB=1) and as the default cache/session store in production. Configuration is in config/database.php under redis.

Last updated on
AuthenticationAPI Patterns