DreamFactory's custom SQL endpoints let you create APIs that are tailored to your exact needs, making your data more flexible and powerful. In this article, we will show you how to set up these endpoints, provide practical examples, and explain how to connect endpoints to your external systems.
DreamFactory Custom SQL Endpoints are a powerful feature within Dreamfactory that allow you to create unique API endpoints based on your own custom sql queries. Now, pretend you want an API that returns data in a very specific manner... maybe as the aggregation of multiple tables or after advanced filtering where standard endpoints simply do not work. This is where Custom SQL endpoints come in.
While using SQL allows you to better define both what data needs to be fetched as well as how it must be processed - all from your database. With this flexibility, you can tune performance and extend capability while still maintaining the integrity of database schemas. In other words, you can think of it as a hotline to your data specific to whatever suits the needs of the one that is going in for it.
These endpoints are perfect for scenarios where you need to go beyond simple CRUD operations. Whether you're aggregating data, transforming formats, or implementing complex business logic, custom SQL endpoints give you the control to make your APIs do exactly what you want. Plus, they integrate with DreamFactory's robust security and role-based access control, ensuring your custom solutions are both powerful and secure.
Their ability to elaborate on existing data and create large detailed reports or analytics makes custom SQL endpoints a great choice. They allow you to do complex query, aggregate more data or filter the data in ways standard endpoints can't. This ability makes them ideal for business intelligence uses, where the need to have all data immediately available is critical.
If possible, custom SQL endpoints are also more flexible and responsive than the traditional CRUD endpoints. Direct query manipulation enables developers to respond far more rapidly (< 15 seconds) to changing requirements than waiting for a new table/view/index in the database or adding an extra document endpoint / embeds nested API structure. This is particularly useful in data migration projects, where the ability of SQL to transform and map your enterprise's vast rivers of discrete information according to relevant templates across any number systems becomes crucial.
Creating custom SQL endpoints in DreamFactory is straightforward and incredibly useful for tailored API needs. Here’s a step-by-step guide to get you started:
Log in to your DreamFactory admin console.
Click on the "API Generation" tab from the main menu.~~
Choose an existing SQL-based service or create a new one if needed. For creating a new service, find the database dropdown and select the appropriate database type (MySQL, PostgreSQL, etc.).
Once your service is set up, click on the service name to open its settings. Navigate to the "API Endpoints" tab.
Click "Add" to create a new custom endpoint. You'll be prompted to enter details such as the endpoint name and HTTP method (GET, POST, PUT, DELETE).
In the provided field, write your custom SQL query. This is where you define what data to retrieve or manipulate.
Example: `SELECT id, name, email FROM users WHERE active = 1;`
Optionally, you can add parameters to your SQL query to make it dynamic. For instance, `SELECT id, name FROM users WHERE id = :user_id;` allows you to pass `user_id` as a parameter in the API request.
Save your endpoint and test it using the built-in API Docs or any API client like Postman. Ensure it returns the expected results.
1. Simple Data Retrieval:
- Endpoint: `/active-users`
- Method: GET
- SQL Query: `SELECT id, name, email FROM users WHERE active = 1;`
- Description: Retrieves a list of active users.
2. Parameterized Query:- Endpoint: `/user-details`
- Method: GET
- SQL Query: `SELECT id, name, email, created_at FROM users WHERE id = :user_id;`
- Description: Retrieves details for a specific user by ID.
3. Data Aggregation:- Endpoint: `/sales-summary`
- Method: GET
- SQL Query: `SELECT SUM(amount) as total_sales, COUNT(*) as total_orders FROM sales WHERE date BETWEEN :start_date AND :end_date;`
- Description: Provides a summary of sales within a specified date range.
4. Data Insertion:- Endpoint: `/add-user`
- Method: POST
- SQL Query: `INSERT INTO users (name, email, active) VALUES (:name, :email, :active);`
- Description: Adds a new user to the database.
5. Data Update:- Endpoint: `/update-user-status`
- Method: PUT
- SQL Query: `UPDATE users SET active = :active WHERE id = :user_id;`
- Description: Updates the active status of a user by ID.
By following these steps and examples, you can harness the full power of custom SQL endpoints in DreamFactory, tailoring your APIs to meet precise business needs.
Custom SQL endpoints in DreamFactory can be a game-changer when it comes to integrating with external applications and services. Whether you need to connect to BI tools, CRM systems, or other software, custom SQL endpoints provide the flexibility and control needed for seamless data interactions.
- Start by generating an API key in DreamFactory for the external application. This key will authenticate the external system when making API calls.
- Navigate to the "Apps" section in the DreamFactory admin console, create a new app, and note down the generated API key.
- Ensure CORS is enabled in DreamFactory if the external application is hosted on a different domain. This allows secure cross-domain requests.
- Go to the "Config" tab in the admin console and configure the CORS settings.
- Share the custom SQL endpoint URL with the external application, along with any necessary parameters.
- Example URL: `https://your-dreamfactory-instance/api/v2/your_service/_func/custom_endpoint`
- External applications should include the API key in the headers of their HTTP requests.
- Example header: `Authorization: Bearer your_api_key`
- Use tools like Postman to simulate requests from the external application and ensure that the custom SQL endpoint responds correctly.
- Adjust parameters and headers as needed based on the requirements of the external system.
By connecting DreamFactory's custom SQL endpoints to external systems, you can clean up your data flows and improve the functionality of your applications. This integration capability ensures that your data is accessible, up-to-date, and ready for any business need, whether it’s analytics, customer management, or inventory control.
When it comes to data access and manipulation, various methods offer different advantages and drawbacks. Here’s a comparison of custom SQL endpoints with other common approaches:
Pros:
Cons:
Pros:
Cons:
Pros:
Cons:
Pros:
Cons:
Custom SQL endpoints in DreamFactory provide a powerful way to enhance your API's flexibility and functionality. Whether you're working with BI tools, CRM systems, or other applications, custom SQL endpoints ensure your APIs can meet the unique demands of your business.
Want to give it a try? Talk to an engineer to get DreamFactory spun up in your environment!