Df Database Laravel Package

Getting Started

dreamfactory/df-database is not a normal composer require-and-go library. It is the database service module of the DreamFactory platform (DreamFactory 4+/5+/6 is a Laravel application), so the realistic first step is to get the platform, not the package:

composer create-project dreamfactory/dreamfactory my-api
cd my-api
cp .env.example .env && php artisan key:generate
php artisan dreamfactory:setup      # or use the browser installer at /

df-database arrives as a transitive dependency of dreamfactory/dreamfactory and registers itself through the platform's service-type registry. You will spend nearly all your time in the admin UI (/admin) and the REST API, not in this package's PHP source.

Where to look first

Notice that connection settings live in the system database (the service table), not in config/database.php. Editing them is an admin-UI or API operation.

First use case — expose an existing database in five minutes

1. Admin → Services → Create → pick your type (mysql, pgsql, sqlsrv, sqlite, oracle).
2. Enter host/database/credentials, set the API namespace, save.
3. Admin → Roles → attach the service to a role and mint an API key.
4. Hit it:

curl -H "X-DreamFactory-Api-Key: $KEY" \
  "https://api.example.com/api/v2/db/_table/orders?limit=5&fields=id,total,created_at&order=created_at%20DESC"

You now have governed CRUD over a schema you never wrote a model or controller for. That is the entire value proposition — treat it as "instant REST for a legacy or third-party database," and use Laravel proper for the business logic that needs to live in code.

Implementation Patterns

Standard CRUD surface. One service, many tables, verbs map to SQL:

# read with filtering, related records, count
GET  /api/v2/db/_table/orders?filter=status='open'&related=customer_by_customer_id&include_count=true

# insert (array = bulk)
POST /api/v2/db/_table/orders        [{...}, {...}]

# update by id, or bulk by filter
PATCH /api/v2/db/_table/orders/42    {...}
PUT   /api/v2/db/_table/orders?ids=1,2,3   [{...},{...},{...}]

# delete by id list or filter
DELETE /api/v2/db/_table/orders?ids=1,2,3
DELETE /api/v2/db/_table/orders?filter=created_at < '2020-01-01'

Schema as an API too. /_schema returns table/field/view metadata; POST /_table creates tables. This is how you let a provisioning job build per-tenant schemas without a DBA:

curl -X POST ".../api/v2/db/_table" -d '{
  "name":"invoices",
  "label":"Invoices",
  "fields":[{"name":"id","type":"id","auto_increment":true,"primary_key":true},
            {"name":"amount","type":"decimal","precision":10,"scale":2}]
}'

Config-as-code for environments. Don't hand-click services in staging and prod. Export the service definition and replay it:

GET  /api/v2/system/service?filter=type like 'mysql'
POST /api/v2/system/service          # idempotent-ish import in CI

Keep a services.json per environment and a small Laravel command that diffs and applies it. This is the single biggest quality-of-life win when you have more than one environment.

Stored procedures work, with discipline.

POST /api/v2/db/_proc/report_monthly
{ "params": [ {"name":"month","value":"2024-11"} ] }

Use them for anything with heavy aggregation or multi-statement transactions — you'll get far better throughput than orchestrating many REST calls.

Wrapping it from a Laravel app. The pragmatic pattern is DreamFactory as a sidecar/gateway and Laravel as the consumer:

// App\Services\DreamFactory
$response = Http::withHeaders(['X-DreamFactory-Api-Key' => config('services.df.key')])
    ->baseUrl(config('services.df.url').'/api/v2/db/_table')
    ->retry(3, 200)
    ->get('orders', ['filter' => "status='open'", 'limit' => 100]);

This keeps credentials out of your app, gives you per-role scoping for free, and lets you swap the underlying database without touching Laravel code.

Custom database support (the real extension point). If you need a dialect that isn't shipped, subclass the two things the platform expects — a TableHandler (dialect SQL) and, if needed, a connector — then register your service type in a service provider:

class MyTableHandler extends \DreamFactory\Core\Database\TableHandlers\BaseTableHandler
{
    protected function buildColumnInfo(...) { /* map native types */ }
}

class MyService extends \DreamFactory\Core\Database\Services\BaseDatabaseService
{
    protected function getTableHandler() { return new MyTableHandler($this); }
}

Register the type in your package's ServiceProvider::boot() via the platform's service-type registry and it appears in the admin UI alongside MySQL and PostgreSQL.

Scoping with roles. Rather than building your own authorization layer, put table/field/verb restrictions into a DreamFactory role and issue one API key per consumer (mobile app, internal job, LLM agent). Combined with per-role max_records_returned, this is a genuinely good fit for giving local LLMs read-only, whitelisted access to a schema.

Gotchas and Tips

• You cannot reasonably use this package standalone. It is tightly coupled to df-core: the Service model, encrypted credentials, RBAC, ApiException, the service-type registry, and platform migrations. If you composer require dreamfactory/df-database into a vanilla Laravel app, expect missing providers and unresolved bindings. Use the platform, or copy patterns out of it.
• APP_KEY rotation is destructive. Service credentials are encrypted at rest with the app key. Rotate it and every configured service breaks silently with decryption errors. Back up .env before touching it.
• PHP driver prerequisites bite at install time. pdo_mysql/pdo_pgsql are easy; pdo_sqlsrv needs Microsoft's msodbcsql ODBC driver; Oracle needs oci8 compiled against matching Instant Client. Half the "service won't connect" issues in the wild are missing extensions, not bad credentials.
• Stale schema cache. Table/column changes made out-of-band (a DBA's ALTER TABLE) won't appear until the cache clears: php artisan dreamfactory:cache:clear (or the system/cache endpoint). Symptom: unknown column errors on fields that obviously exist.
• Tables without a primary key degrade badly. You lose record-by-ID operations, related traversal, and reliable bulk update semantics. Add a surrogate key or accept filter-only access.
• filter syntax is SQL-flavored, not JSON-native. Strings need quotes (name='Bob'), dates need ISO format, and reserved words/columns with spaces need backtick/identifier quoting. The JSON filter object form is equality/IN only — it won't express > or LIKE.
• related= requires a real foreign key and uses the <table>_by_<column> naming convention. No FK, no relation — and the error messages are unhelpful when the name is misspelled.
• include_count=true runs a second COUNT(*) on every page. On large tables this is often slower than the data query itself; cache counts or compute them separately.
• Respect the record cap. There is a platform-level max_records_returned (and per-role overrides). limit=0 doesn't mean "everything" — it means "up to the cap." Paginate deliberately and prefer fields= projection over SELECT *.
• Type coercion surprises. MySQL returns integers as ints via prepared statements, SQLite returns almost everything as strings, and DreamFactory's id handling normalizes inconsistently across dialects. Don't rely on strict JSON typing from id fields; cast at the boundary.
• Debugging workflow: enable APP_DEBUG=true and DF_LOG_LEVEL=debug, then watch storage/logs/laravel.log — the package logs the fully-built SQL for each request, which is the fastest way to diagnose filter and schema problems. The admin UI's "API Docs" tab for a service is also generated from live schema, so if it looks wrong, the schema cache is almost always the culprit.