canonical: https://jentic.com/apis/dreamfactory.com/dreamfactory # DreamFactory SQL Server The DreamFactory SQL Server API turns a Microsoft SQL Server database into a REST interface, exposing each table for record reads and writes along with filtering, sorting, and pagination. It provides schema endpoints that retrieve, create, replace, and drop tables, and resource endpoints that list the tables, stored procedures, and functions the service makes available. Stored procedures and database functions are callable by name, so an application can run server-side database logic over HTTP without a direct database connection. ## For AI agents Read and write records in any SQL Server table over REST, retrieve and alter table schema, and call stored procedures and functions by name. DreamFactory auto-generates the endpoints from the database so an agent reaches the data over HTTP without a database driver. ## Scope Does not handle database hosting, backups, or user administration. Use for reading and writing SQL Server tables, schema, and stored procedures over REST only. ## Capabilities - Read and write records in any SQL Server table with filtering, sorting, and pagination - Retrieve table schema definitions and create or replace tables through the schema endpoints - Drop a table or patch its structure without a direct database connection - Call stored procedures by name and read their result sets - Run database functions by name and return their output - List the tables, stored procedures, and functions a service exposes ## Use cases ### Agent querying a SQL Server database An AI agent connected through Jentic can answer questions against a SQL Server database without writing SQL or holding a database connection. It lists the available tables, reads rows from the relevant one with a filter, and returns the result as JSON, so a natural-language question becomes a REST read against the auto-generated table endpoints. Example prompt: List the tables the service exposes, read rows from the Application.Cities table filtered to one country, and return the first 50 rows. ### Expose a legacy database to applications DreamFactory generates REST endpoints for every table in a SQL Server database, so a legacy datastore can serve modern apps without a custom backend. An application can insert, update, and read records through the table endpoints, using the same filtering and pagination parameters across every table rather than bespoke queries per screen. Example prompt: Insert a new record into a table, then read it back by its primary key to confirm the write succeeded. ### Schema introspection and migration The schema endpoints let a tool read a table's definition, create new tables, and replace or patch existing structures over HTTP. A migration agent can retrieve the current schema for a set of tables, create a new table with the required fields, and verify the definition, keeping structural changes scriptable and auditable. Example prompt: Retrieve the schema for an existing table, create a new table with matching fields, and confirm the new table's definition. ### Invoke stored procedures and functions DreamFactory exposes a database's stored procedures and functions as callable endpoints, so server-side logic stays in the database but is reachable over REST. An agent can call a stored procedure by name with its parameters and read the returned result set, triggering reporting or business logic without embedding SQL in the client. Example prompt: Call a stored procedure by name with its input parameters and return the result set it produces. ## Key endpoints | Method | Path | Description | | --- | --- | --- | | GET | `/` | Get the resources this service exposes | | GET | `/_table` | Retrieve one or more tables | | GET | `/_schema` | Retrieve table schema definitions | | POST | `/_schema` | Create one or more tables | | GET | `/_proc` | List stored procedures | | POST | `/_proc/{procedure_name}` | Call a stored procedure by name | | POST | `/_func/{function_name}` | Call a database function by name | ## Key resources - **Table Records** — Read and write records in each SQL Server table with filtering, sorting, and pagination - **Schema** — Retrieve, create, replace, patch, and drop table definitions - **Stored Procedures** — List and call stored procedures by name and read their result sets - **Stored Functions** — List and call database functions by name and return their output - **Service Resources** — List the tables, schema resources, procedures, and functions the service exposes ## AI readiness This API is usable in Jentic One now. Its AI-readiness score against Jentic's framework shows where it stands today and where improvements would make it even easier for agents to use. - **Score:** 47 / 100 - **Maturity:** Foundational - **Dimensions:** - Foundational Compliance: 68 / 100 - Developer Experience & Jentic Compatibility: 63 / 100 - AI-Readiness & Agent Experience: 38 / 100 - Agent Usability: 43 / 100 - Security: 33 / 100 - AI Discoverability: 64 / 100 - **View full report:** https://jentic.com/apis/dreamfactory.com/dreamfactory/scorecard - **How the score is calculated:** https://docs.jentic.com/reference/api-readiness-framework/overview/ - **More about the dimensions:** https://docs.jentic.com/reference/api-readiness-framework/specification/#dimensional-model-overview ### Score it yourself Every API in the directory is allowlisted, so you can re-score it with no key required. - **Score your own API:** https://jentic.com/scorecard.md - **Scoring CLI agent skill:** https://github.com/jentic/jentic-api-scorecard/blob/main/skills/jentic-api-scorecard/SKILL.md ```sh npx @jentic/api-scorecard-cli score ``` ## Why Jentic - **Setup:** Wiring the DreamFactory SQL Server API by hand means choosing among its several auth schemes, learning its table, schema, procedure, and function URL conventions, and navigating thousands of generated endpoints. Through Jentic you install once, import the service from the API Directory, store the credential once, and your agent calls it. - **Permission scoping:** DreamFactory puts the table name in the URL path, so a rule can scope your agent to specific tables and to read-only record and schema operations. You choose the operations it may call, so destructive ones such as dropping a table or deleting records are not included unless you add them. - **Credential handling:** Your DreamFactory API key is stored once, encrypted, by your own Jentic One instance and injected at execution time. It never enters the agent's prompt, logs, or context. - **Discovery method:** Agents search Jentic by intent such as 'query rows from a table' or 'call a stored procedure', and Jentic returns the matching DreamFactory operation with its input schema so the agent calls the right endpoint without scanning thousands of generated paths. ## Related APIs - **Hasura** — Hasura generates instant GraphQL and REST APIs over databases, like DreamFactory does for SQL Server - **Supabase** — Supabase auto-generates a REST API over a Postgres database, comparable to DreamFactory over SQL Server - **NocoDB** — NocoDB turns a database into REST endpoints and a spreadsheet UI, overlapping DreamFactory's table API - **Airtable** — Airtable offers a records API that can mirror or sync with data served by DreamFactory ## FAQ ### What authentication does the DreamFactory SQL Server API use? The spec declares several supported credentials: an API key sent as the X-DreamFactory-API-Key header or an api_key query parameter, a session token sent as the X-DreamFactory-Session-Token header or session_token query parameter, plus HTTP basic and bearer schemes. A request satisfies auth by presenting any one of them. Through Jentic the credential you use is stored encrypted by your own Jentic One instance and injected at call time, so it never reaches the agent's context. ### Is there a DreamFactory MCP server? You don't need an MCP server to give your agent the DreamFactory SQL Server API. Jentic connects it directly from the API Directory: import the service, store your credential once, and your agent calls its operations. Because this service exposes thousands of per-table endpoints, Jentic discovers the right operation on demand rather than loading every definition into the agent's context. ### Can I limit what my agent is allowed to do with the DreamFactory SQL Server API? Yes. Write a rule that allows only the read operations your agent needs, such as the table record reads and schema retrieval, so it can query data but cannot drop tables or write records, and every call it makes is logged. The table name travels in the URL path, so you can also scope a rule to the specific tables the agent should reach. ### Can I call a stored procedure with the DreamFactory SQL Server API? Yes. The procedure endpoints let you list the stored procedures the service exposes and call one by name with its parameters, returning the result set as JSON. The service exposes database functions the same way, so server-side logic stays in SQL Server but is reachable over REST. ### What are the rate limits for the DreamFactory SQL Server API? This OpenAPI spec does not specify rate limits; limits in a DreamFactory deployment are set per-role and per-service by the administrator who hosts it. Check your DreamFactory instance's role and limit configuration for the figures that apply to your API key. ### How do I query a table with the DreamFactory SQL Server API through Jentic? Install Jentic One, import the DreamFactory SQL Server service from the API Directory, and store your API key once. Your agent then searches by intent, for example 'query rows from a table', and Jentic returns the matching table endpoint with its filter and pagination parameters. To run it on your own infrastructure, install Jentic One from its GitHub repo.