PHP Integration

Connect to Spile using Snowflake's PHP PDO driver, enabling seamless integration with PHP applications and frameworks.

Installation

Install via Composer:

composer require snowflake/pdo

For Laravel users:

composer require snowflake/snowflake-laravel

Quick Start with PDO

Basic usage with PDO:

<?php
 
try {
    // Create connection
    $dsn = sprintf(
        "snowflake:account=%s;warehouse=%s;database=%s;schema=%s",
        '{universql_server}',  // Spile server
        'your_warehouse',
        'your_database',
        'your_schema'
    );
    $username = "your_username";
    $password = "your_password";
    
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    
    // Execute a query
    $stmt = $pdo->query("
        SELECT 
            DATE_TRUNC('month', created_at) as month,
            COUNT(*) as user_count,
            COUNT(DISTINCT country) as countries
        FROM users
        GROUP BY 1
        ORDER BY 1 DESC
        LIMIT 12
    ");
 
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo sprintf(
            "Month: %s, Users: %d, Countries: %d\n",
            $row['month'],
            $row['user_count'],
            $row['countries']
        );
    }
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Laravel Integration

Configure Snowflake in Laravel:

// config/database.php
'connections' => [
    'snowflake' => [
        'driver' => 'snowflake',
        'account' => '{universql_server}',  // Spile server
        'username' => env('SNOWFLAKE_USERNAME'),
        'password' => env('SNOWFLAKE_PASSWORD'),
        'warehouse' => env('SNOWFLAKE_WAREHOUSE'),
        'database' => env('SNOWFLAKE_DATABASE'),
        'schema' => env('SNOWFLAKE_SCHEMA'),
    ],
]

Using with Eloquent:

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
 
class Product extends Model
{
    protected $connection = 'snowflake';
    protected $table = 'products';
    protected $fillable = ['name', 'price', 'category'];
 
    public static function topSellingProducts($limit = 10)
    {
        return static::select([
            'products.id',
            'products.name',
            DB::raw('SUM(order_items.quantity) as total_sold'),
            DB::raw('AVG(products.price) as avg_price')
        ])
        ->join('order_items', 'products.id', '=', 'order_items.product_id')
        ->groupBy('products.id', 'products.name')
        ->orderBy('total_sold', 'desc')
        ->limit($limit)
        ->get();
    }
}

Connection Management

Using a connection manager:

<?php
 
class SnowflakeConnection
{
    private static ?PDO $instance = null;
    private static array $config = [
        'account' => '{universql_server}',  // Spile server
        'warehouse' => 'your_warehouse',
        'database' => 'your_database',
        'schema' => 'your_schema',
        'username' => 'your_username',
        'password' => 'your_password'
    ];
    
    public static function getInstance(): PDO
    {
        if (self::$instance === null) {
            $dsn = sprintf(
                "snowflake:account=%s;warehouse=%s;database=%s;schema=%s",
                self::$config['account'],
                self::$config['warehouse'],
                self::$config['database'],
                self::$config['schema']
            );
            
            self::$instance = new PDO(
                $dsn,
                self::$config['username'],
                self::$config['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        }
        
        return self::$instance;
    }
}

Symfony Integration

Configure Snowflake with Symfony:

# config/packages/doctrine.yaml
doctrine:
    dbal:
        connections:
            snowflake:
                driver_class: Snowflake\PDO\Driver
                url: 'snowflake://{universql_server}'  # Spile server
                options:
                    warehouse: '%env(SNOWFLAKE_WAREHOUSE)%'

Using with Doctrine DBAL:

<?php
 
namespace App\Repository;
 
use Doctrine\DBAL\Connection;
 
class ProductRepository
{
    private Connection $connection;
    
    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }
    
    public function findByCategory(string $category): array
    {
        $queryBuilder = $this->connection->createQueryBuilder();
        
        return $queryBuilder
            ->select('p.*')
            ->from('products', 'p')
            ->where('p.category = :category')
            ->setParameter('category', $category)
            ->executeQuery()
            ->fetchAllAssociative();
    }
}

Error Handling

Proper error handling:

<?php
 
try {
    $dsn = sprintf(
        "snowflake:account=%s;warehouse=%s;database=%s",
        '{universql_server}',  // Spile server
        'your_warehouse',
        'your_database'
    );
    $pdo = new PDO($dsn, $username, $password);
    $stmt = $pdo->query("SELECT * FROM non_existent_table");
} catch (PDOException $e) {
    $errorInfo = $e->errorInfo;
    error_log(sprintf(
        "Database error: %s\nSQLSTATE: %s\nDriver Error: %s",
        $e->getMessage(),
        $errorInfo[0],
        $errorInfo[2]
    ));
}

Best Practices

Use Environment Variables

$dsn = sprintf(
    "snowflake:account=%s;warehouse=%s;database=%s",
    '{universql_server}',  // Spile server
    $_ENV['SNOWFLAKE_WAREHOUSE'],
    $_ENV['SNOWFLAKE_DATABASE']
);

Implement Connection Pooling

// Using connection pooling with PHP-PM
$pool = new ConnectionPool([
    'min_connections' => 5,
    'max_connections' => 20,
    'dsn' => "snowflake:account={universql_server}"  // Spile server
]);

Use Query Builders

$query = new QueryBuilder($pdo);
$users = $query
    ->select('users', ['id', 'name', 'email'])
    ->where('active', true)
    ->limit(10)
    ->execute();