README

A Laravel database driver for Snowflake using the REST SQL API. No PHP extensions or ODBC drivers required.

Features

• Pure PHP implementation using Snowflake's REST API
• Full Eloquent support with models and relationships
• Laravel Query Builder with Snowflake-specific SQL
• Migrations with Snowflake-specific column types
• ULID primary keys optimized for Snowflake clustering
• Native support for VARIANT, OBJECT, and ARRAY types
• Large result set streaming via partitions

Requirements

• PHP 8.2+
• Laravel 12.0+
• Snowflake account with REST API access

Installation

composer require foundry-co/laravel-snowflake

The package will auto-register its service provider.

Configuration

1. Snowflake Account Setup

Set up key-pair authentication in Snowflake:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt
openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

Assign the public key to your Snowflake user:

ALTER USER your_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

2. Environment Variables

SNOWFLAKE_ACCOUNT=your-account-identifier
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_DATABASE=MY_DATABASE
SNOWFLAKE_SCHEMA=PUBLIC
SNOWFLAKE_USER=your_username
SNOWFLAKE_PRIVATE_KEY_PATH=/path/to/snowflake_key.p8
SNOWFLAKE_ROLE=SYSADMIN

3. Database Configuration

Add the Snowflake connection to config/database.php:

'connections' => [
    'snowflake' => [
        'driver' => 'snowflake',
        'account' => env('SNOWFLAKE_ACCOUNT'),
        'warehouse' => env('SNOWFLAKE_WAREHOUSE'),
        'database' => env('SNOWFLAKE_DATABASE'),
        'schema' => env('SNOWFLAKE_SCHEMA', 'PUBLIC'),
        'role' => env('SNOWFLAKE_ROLE'),
        'auth' => [
            'jwt' => [
                'user' => env('SNOWFLAKE_USER'),
                'private_key_path' => env('SNOWFLAKE_PRIVATE_KEY_PATH'),
                'private_key_passphrase' => env('SNOWFLAKE_PRIVATE_KEY_PASSPHRASE'),
            ],
        ],
    ],
],

You can also provide the private key content directly instead of a file path:

'auth' => [
    'jwt' => [
        'user' => env('SNOWFLAKE_USER'),
        'private_key' => env('SNOWFLAKE_PRIVATE_KEY'),
    ],
],

Usage

Eloquent Models

Add the UsesSnowflake trait to any model that connects to Snowflake:

use Illuminate\Database\Eloquent\Model;
use FoundryCo\Snowflake\Eloquent\Concerns\UsesSnowflake;

class User extends Model
{
    use UsesSnowflake;

    protected $connection = 'snowflake';
    protected $table = 'users';
}

The trait automatically generates ULID primary keys and handles Snowflake timestamp formats.

Query Builder

$users = DB::connection('snowflake')->table('users')->get();

DB::connection('snowflake')->table('users')->insert([
    'id' => Str::ulid()->toLower(),
    'name' => 'John Doe',
    'email' => 'john@example.com',
]);

DB::connection('snowflake')
    ->table('events')
    ->where('payload->type', 'purchase')
    ->get();

Migrations

use Illuminate\Database\Migrations\Migration;
use FoundryCo\Snowflake\Schema\SnowflakeBlueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    protected $connection = 'snowflake';

    public function up(): void
    {
        Schema::connection('snowflake')->create('users', function (SnowflakeBlueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->variant('preferences');
            $table->timestamps();
            $table->clusterBy(['created_at', 'id']);
        });
    }

    public function down(): void
    {
        Schema::connection('snowflake')->dropIfExists('users');
    }
};

Snowflake Column Types

Custom Casts

use FoundryCo\Snowflake\Casts\VariantCast;
use FoundryCo\Snowflake\Casts\SnowflakeTimestamp;

class Event extends Model
{
    use UsesSnowflake;

    protected $connection = 'snowflake';

    protected $casts = [
        'payload' => VariantCast::class,
        'occurred_at' => SnowflakeTimestamp::class,
    ];
}

Warehouse & Role Switching

$connection = DB::connection('snowflake');

$connection->useWarehouse('ANALYTICS_WH');
$connection->useRole('ANALYST');
$connection->useSchema('STAGING');

Transactions

DB::connection('snowflake')->transaction(function ($db) {
    $db->table('accounts')->where('id', 1)->decrement('balance', 100);
    $db->table('accounts')->where('id', 2)->increment('balance', 100);
});

Cursors

foreach (DB::connection('snowflake')->table('events')->cursor() as $event) {
    // Process one row at a time
}

Testing

composer test

Limitations

• No savepoints (Snowflake limitation)
• No row locking (Snowflake is append-only)
• No traditional indexes (use clustering keys instead)

License

MIT License. See LICENSE for details.