Df Sqldb Laravel Package

Getting Started

First, calibrate expectations: dreamfactory/df-sqldb is not a query builder or an Eloquent replacement you drop into an app. It is the SQL service package for DreamFactory, which itself is a Laravel application. Installing this package only makes sense inside a DreamFactory instance — it registers the sql_db service type (plus the mysql, pgsql, sqlsrv, sqlite variants) that turns a database into a governed REST API. If you want DB::table() in a normal Laravel app, you want illuminate/database, not this.

Minimal setup inside a DreamFactory checkout:

composer require dreamfactory/df-sqldb
php artisan df:setup      # runs migrations/seeders and refreshes platform config
php artisan optimize:clear

Look here first, in this order:

1. src/Services/ — the base SqlDb service class and per-driver subclasses. This is where connection params, caching, and table/resource wiring live.
2. src/Resources/ — the _table, _schema, and _proc resource handlers. This is the actual REST surface.
3. src/Models/ / database/migrations/ — service definitions are persisted in the platform DB, not in config/.
4. /api/v2/api_docs on a running instance — live Swagger for every configured service. Faster than reading source.

Your first use case: create a service through the admin UI (Services → Create → SQL DB), point it at a MySQL/Postgres database, then immediately hit:

curl -H "X-DreamFactory-Api-Key: $KEY" \
  "https://df.test/api/v2/mydb/_table/orders?limit=5&order=created_at%20DESC"

Then consume it from a separate Laravel app with Http::withHeaders([...])->get(...). That HTTP boundary — DreamFactory as a headless backend, Laravel as the client — is the realistic day-to-day arrangement for a Laravel developer.

Implementation Patterns

Provision services as code, not clicks. Service definitions live in the service table, so they are environment state. Create them with POST /api/v2/system/service (type sql_db, config containing dsn/host/port/database/username/password and options) from a seeder or deploy script, or export/import definitions between environments. Treat a missing service the same way you'd treat a missing migration.

Use _table as your CRUD contract. The resource supports the parameters you'd expect, and a few you should standardize on immediately:

• filter=status=open and total>100 (URL-encode it; server-side filtering is also the security boundary for roles)
• fields=id,name,total, related=items (follows real FK constraints), order, limit, offset, include_count=true
• POST with a JSON array for batch inserts; PUT/PATCH/DELETE by /{id} — which requires a declared primary key.

Push business logic into server-side scripts, not a parallel Laravel app. Service-level pre_process and post_process scripts (and platform event scripts) are where you validate payloads, stamp created_by, redact fields, or fan out to another service via platform.api. If you find yourself building a Laravel controller that only reshapes DreamFactory responses, the logic probably belongs in a script instead.

Use stored procedures for anything complex. POST /api/v2/mydb/_proc/my_report with a params object is the escape hatch when filter can't express the query. It also keeps the SQL under version control in the database rather than in a scripting sandbox.

Govern with roles, not with client code. Define roles with per-service access, allowed verbs, field allow-lists, and a mandatory row filter (e.g. tenant_id={user.tenant_id}). Issue JWT/session tokens per user so {user.*} substitution works; reserve API-key-only access for genuinely public or admin tooling.

Feed the OpenAPI spec to your LLM tooling. DreamFactory generates a spec per service; register those endpoints as tools for local models. Because roles and filters apply at the API layer, the model can't see rows or columns the role denies — that's the whole point of the "governed access" pitch.

Gotchas and Tips

• No models, no relationships you control. related works only off real foreign keys. SQLite needs PRAGMA foreign_keys = ON, and some MySQL setups with MyISAM silently ignore FKs entirely.
• The record cap is silent. A platform-level max-records setting truncates responses without an obvious error. Always pass explicit limit and use include_count when the client needs totals; page deliberately.
• filter is not SQL. No subqueries, limited OR support depending on version, and column names are validated against schema — expect 400s on anything clever. URL-encode aggressively; a raw + or unencoded space is the most common "it works in Postman" bug.
• Schema caching goes stale after DDL. Adding a column in a migration won't appear in _schema until the platform cache is cleared (php artisan cache:clear or the service's cache refresh). This burns people in CI where migrations run after the service is warmed.
• Naming is driver-specific. Postgres lowercases unquoted identifiers; SQL Server needs dbo.table; MySQL reserved words need backticks. Use schema-qualified names consistently in filters and related.
• Don't trust API-key auth for user data. An API key identifies the app, not the user. Without a session token, {user.id} substitution and role filters have nothing to bind to — and a leftover "Allow All" role is a full-table exfiltration path.
• Scripting engine drift. The V8 scripting engine is a separate package; PHP-based scripting is legacy and absent from current docs. Verify which engine your instance actually has before copying snippets from old blog posts.
• SQL Server/MySQL procedure params differ. Named @param vs positional args — build a thin wrapper in your client rather than assuming one shape.
• Batch writes aren't atomic by default. Wrap related inserts in a transaction at the database level or verify the rollback flag for your version; partial batch failure leaves orphans.
• Debug in the Laravel app, not the API. Connection failures, DSN typos, and driver errors surface in storage/logs/laravel.log; the REST response usually collapses them into a generic 500.
• Extension point: subclass the base SQL service (src/Services/SqlDb.php) and register it through the platform's service manager if you need custom connection handling or extra endpoints — but keep that in a separate package, because DreamFactory upgrades will overwrite anything you patch in place.
• Upgrades: composer require inside a DreamFactory root can conflict with the dreamfactory/dreamfactory metapackage's pins. Change the constraint, run composer update dreamfactory/df-sqldb, then php artisan df:setup and test every service — the upgrade path is the most common source of "the API suddenly returns 404 for _table".