Creating an app with MS SQL data is now faster and simpler, thanks to tools like Adalo and DreamFactory. You can design and deploy apps for web, iOS, and Android - all without writing code. Here's how:
- Adalo: A visual app builder that connects to external databases like MS SQL using REST APIs. It supports full CRUD operations (Create, Read, Update, Delete) and costs start at $45/month for database integration.
- DreamFactory: A platform that generates REST APIs for MS SQL databases. It acts as the bridge between your database and Adalo, providing secure, ready-to-use endpoints.
- Steps: Prepare your MS SQL database, configure DreamFactory to create API endpoints, and connect the APIs to Adalo's External Collections. Then, design app screens using drag-and-drop components.
Adalo allows you to build one app and deploy it as a Progressive Web App (PWA) or natively to iOS and Android without rebuilding for each platform. This process cuts development time by up to 50% and reduces costs by about 48%.
Whether you're managing customer records, inventory, or internal tools, this approach ensures your app connects seamlessly to your MS SQL database and scales as your user base grows.
Creating a task app with no code and mysql
What You Need Before You Start
To connect your MS SQL data to your app, you'll need three key components: a properly configured MS SQL database, an active Adalo account with access to external collections, and a DreamFactory account to create the API bridge. Each of these plays a crucial role in making the integration work smoothly.
First, your MS SQL database must be ready for external access and secured. You'll need details like the host address (e.g., sql.yourdomain.com), port number (usually 1433), database name, username, and password. With these in hand, you can link your database to Adalo without a hitch.
Adalo's External Collections feature allows your app to interact with databases outside its built-in system. However, this feature requires a paid subscription. The connection uses JSON formatting to transfer data between your MS SQL database and your app's screens.
DreamFactory acts as the middleman, generating REST API endpoints for your MS SQL database. Since Adalo can't directly connect to MS SQL, DreamFactory fills the gap by creating a secure API layer. It offers a free trial and significantly cuts down backend development time by automating API creation.
Set Up Your MS SQL Database
Start by configuring your MS SQL database for external access. Enable the TCP/IP protocol in SQL Server Configuration Manager and ensure port 1433 is open. Use Mixed Mode authentication so both Windows and SQL Server logins are supported for API-based connections.
Create a dedicated service account, such as df_api_reader, with limited permissions (like SELECT-only) for the tables your app will use. This approach limits security risks by restricting access to only the necessary data.
For Adalo compatibility, each table you plan to use must include a unique identifier field. Adalo relies on this distinct ID to manage and display data in lists and forms. If your tables use composite keys or unconventional identifiers, consider creating database views that map your primary key to a field named id.
To ensure secure data transfer, enable SSL encryption in your database connection settings. Configure your Windows Firewall to allow inbound connections on port 1433, but restrict access to known IP addresses whenever possible. Using read-only views can add another layer of security by filtering out sensitive data before it's exposed to your app.
Once your database is set up and secure, you're ready to move on to configuring your Adalo account and tools.
Adalo Account and Tools
After preparing your database, the next step is to connect it to your Adalo account via the External Collections feature. You'll need an active Adalo account to build and deploy your app. While Adalo offers a free plan for basic web publishing, connecting to MS SQL databases requires a paid plan starting at $45 per month. This subscription also allows you to publish your app on Google Play and the Apple App Store.
Within Adalo's builder, access the External Collections section. Here, you can configure connections to external data sources using REST API endpoints. You'll need the API base URL from DreamFactory and the necessary authentication headers.
Adalo supports full CRUD operations (Create, Read, Update, Delete) on your MS SQL data. This means you can display, add, edit, or delete records directly through Adalo's visual interface - no SQL queries required.
DreamFactory for API Generation
DreamFactory is a powerful platform that generates REST APIs for over 20 database types, including MS SQL Server. It serves as the bridge between your MS SQL database and Adalo by converting database operations into API calls.
"DreamFactory lets you create a secure Microsoft SQL Server API in just minutes - no coding required." - Kevin McGahey, Solutions Engineer
Sign up for a DreamFactory account to take advantage of its free trial. Once you create a service in DreamFactory, the platform automatically generates API endpoints, complete with Swagger/OpenAPI documentation.
The APIs provide data in JSON format, with record collections nested under a resource key - exactly the structure Adalo's External Collections feature requires. DreamFactory also includes an API Docs tab, where you can test GET, POST, PUT, and DELETE operations directly in your browser before integrating them with your app.
When you save your MS SQL service in DreamFactory, you'll receive an API key. This key must be included in requests to Adalo using the header X-DreamFactory-API-Key. This authentication ensures that only authorized applications can access your database through the API.
How to Connect Your MS SQL Database to Adalo
How to Connect MS SQL Database to Adalo App - Step by Step Process
Adalo integrates with MS SQL databases using a REST API bridge, with DreamFactory acting as the intermediary. DreamFactory converts your database tables into RESTful API endpoints that Adalo can call. The process involves three key steps: creating API endpoints in DreamFactory, setting up the external data source in Adalo, and testing the connection to ensure it works seamlessly. Here's how to get it done.
This connection lets your app pull live data directly from your MS SQL database. Once linked, your app can retrieve customer records, inventory details, or any other data stored in SQL Server.
Generate API Endpoints for MS SQL
Start by logging into your DreamFactory account and creating a new SQL Server service. Select SQL Server from the list of available database connectors. Provide the necessary connection details, including the host address (e.g., sql.yourcompany.com), port number (usually 1433), database name, username, and password. For added security, enable SSL encryption in the advanced settings to protect data during transit.
DreamFactory will automatically generate RESTful endpoints for your database tables, views, and procedures, complete with Swagger documentation for easy reference. You can test these endpoints using Swagger's "Try it out" feature.
To secure access, create a role in DreamFactory with specific permissions for the tables your app needs. Then, generate an API key and associate it with this role. This API key will authorize Adalo's requests, significantly streamlining the development process.
Configure External Data Source in Adalo
In Adalo, navigate to the Database menu and click on Add Collection, then choose External Collection. Enter your DreamFactory endpoint URL in the Base URL field. The URL format should look something like this:
https://your-instance.com/api/v2/sql_service/_table/table_name
Next, add a new header named X-DreamFactory-API-Key and paste your generated API key as its value. This header ensures that Adalo can authenticate its requests to your database. In the "Get All" endpoint settings, set the Results Key to resource, as DreamFactory returns record collections under this JSON key. Additionally, switch the update method from PUT to PATCH to update only modified fields, preventing unintended data overwrites.
| Configuration Field | What to Enter | Example Value |
|---|---|---|
| Base URL | DreamFactory endpoint URL | https://api.example.com/api/v2/mssql_inventory/_table/products |
| Header Name | Authentication header | X-DreamFactory-API-Key |
| Header Value | Your generated API key | abc123xyz789 |
| Results Key | JSON key for records | resource |
| Update Method | HTTP method for updates | PATCH |
Once you've configured the external collection, you’re ready to test the connection.
Test the Connection
In Adalo, click Run Test to check the connection. A successful test will display a green success message along with a list of data fields from your MS SQL database, such as product_id, product_name, and price. If the test fails, carefully review the X-DreamFactory-API-Key header for any typos or extra spaces.
After a successful test, click Save Collection to finalize the setup. Then, use Adalo's Staging Preview or View App mode to confirm that the data displays correctly in your app's UI components, like lists or text fields. If the data appears during the test but not in your app, double-check that the JSON response from your API aligns with the expected format and that the Results Key is correctly mapped.
Adalo’s platform allows you to deploy your app as a Progressive Web App or publish it natively to iOS and Android app stores - all from a single codebase - making it easy to deliver a polished, ready-to-use solution.
Design App Screens and Link to MS SQL Data
Once your MS SQL database is connected, it's time to create the interface your users will interact with. Adalo's visual builder makes this process straightforward. You can drag and drop components onto your screens and link them directly to your MS SQL data. Here's how to set up screens and connect data fields for a smooth user experience.
Create Screens and Components
Start by opening Adalo's visual editor and clicking "+" to access the Add Panel. From there, you can drag components like lists, forms, and buttons onto your screen. Each serves a specific purpose:
- Lists: Ideal for displaying multiple records, such as a product catalog or a customer directory.
- Forms: Used for data entry, whether you're creating new records or updating existing ones.
- Buttons: Trigger actions like navigating to a detail view or deleting a record.
Adalo offers different list styles to suit your data needs. For example:
- Simple Lists: Perfect for showing basic text information.
- Card Lists: Include images and descriptions, making them great for visual content like product displays.
- Custom Lists: Allow you to fully customize the layout by dragging and arranging elements within each list item.
Let’s say you’re building an inventory app. A Card List could display product images, names, and prices, giving users a clear and appealing view of the available items.
Map Components to MS SQL Data
Once you've added a component, click on it and select "What is this a list of?" to link it to your external collection, such as "Products" or "Customers." Adalo will automatically populate the list with data from your MS SQL database.
To map specific fields, use Magic Text. For instance:
- Click on a text label in a list item.
- Select the Magic Text icon.
- Choose the database field you want to display, like
product_nameorprice.
For images, map the source of the image component to a URL field in your MS SQL table. You can also refine the data shown by adding filters (e.g., displaying only products where in_stock is "True") or applying sorting rules, such as arranging items by "Price: Low to High."
When using forms to create or update records, map each input field to the corresponding column in your database. For example, a "New Product" form might include fields for product_name, description, and price, each linked to the appropriate MS SQL table column. Set the form action to either "Create New Record" or "Update Current Record" to ensure changes are correctly saved. For updates, make sure to use the PATCH method, which modifies only the fields you change, preventing accidental overwrites of other data.
sbb-itb-d4116c7
How to Work with MS SQL Data in Your App
Once you've linked your components to your MS SQL database, you can easily fetch, create, update, and delete records. These actions are the foundation of a functional app, and Adalo's visual builder, combined with API connections, simplifies the process.
Fetch and Display Data
When you connect a List component to an External Collection, Adalo automatically fetches and displays records in real time. If you're working with large datasets, it's a good idea to use the "Execute Query" action within your integration platform. This allows you to filter data directly at the database level, ensuring your app performs smoothly even with thousands of rows.
MS SQL data types map seamlessly to Adalo property types. For example:
- Text fields (like
varcharornvarchar) appear as Text properties. - Numeric fields (
intordecimal) convert to Number properties. - Bit columns turn into true/false switches.
- Datetime fields are displayed in user-friendly formats (e.g., 03/15/2026 or 3:30 PM), even though they're stored as UNIX timestamps.
To avoid mapping errors, ensure your MS SQL column names align with the corresponding Adalo property names. For real-time updates, you can trigger a new fetch whenever records change. Once your data is fetched and displayed correctly, you’re ready to move on to creating or updating records.
Create and Update Records
Forms are the go-to tool for adding new records or editing existing ones. Add a Form component to your app screen, link it to your MS SQL External Collection, and map each input field to the appropriate database column. Then, set the form action to "Create" for new entries or "Update" for modifications.
Keep in mind:
- Your MS SQL table must have a Primary Key for update and delete operations.
- Server-side triggers on your MS SQL table can sometimes interfere with direct insert or update requests from external connectors.
When a user submits the form, Adalo sends a POST request for new records or a PATCH request for updates. The PATCH method ensures that only the specified fields are modified, helping avoid accidental overwrites. Before deploying your app, use the "Run Once" feature in your integration tool to test the data flow between the form and your MS SQL database.
With forms set up, the final step is to enable record deletion.
Delete Records
To allow users to delete records, you’ll need:
- A Primary Key in your MS SQL table.
- A properly configured DELETE endpoint in your External Collection settings.
- A user interface element, like a Trash icon or Delete button, to trigger the action.
For example, you can add a Delete button inside your List component and set its click action to "Delete > Current [Record Name]". This ensures only the selected record is removed.
Your DELETE endpoint URL should look like this:
https://api.your-tool.com/records/{id}
where {id} is the Primary Key value. When the delete action is triggered, Adalo sends a request to execute the SQL DELETE command. To keep things secure, use API tokens or Personal Access Tokens to ensure only authorized records are deleted.
If your table doesn’t already have a Primary Key, you’ll need to add one in MS SQL Server before enabling the delete function.
Adalo makes it easy to build and deploy apps for web, iOS, and Android - all from a single platform.
Test, Deploy, and Scale Your Adalo App
Test Your App
Click Preview to ensure your app’s create, update, and delete (CRUD) functions work seamlessly. Check that each operation updates your MS SQL database correctly and returns valid JSON responses. To avoid issues, verify your API’s JSON output with tools like JSONLint, ensuring every object has a unique id key. Stick to underscores in JSON keys (e.g., user_id instead of User ID) for consistency. If you’re using integration platforms like Latenode or n8n, try their "Run once" feature to manually test your data flow. Review the execution history to pinpoint any errors or breaks in the process.
Before making major changes, save a Design Version in Adalo. This backup allows you to revert your app if testing uncovers critical issues. Once your app is live, use the Analytics tab to monitor traffic patterns. Pay attention to screens with high drop-off rates, as these might indicate issues like slow data loading.
When testing confirms everything is working smoothly, you’re ready to move on to deployment.
Deploy to Web, iOS, and Android
After successful testing, it’s time to publish your app across platforms. Adalo makes this process straightforward by letting you deploy to the web, iOS, and Android from a single build. For web apps, head to the Publish panel to set up your URL. You can use an Adalo subdomain (e.g., subdomain.adalo.com/app-name) or connect your own custom domain (e.g., app.yourcompany.com).
Publishing to the Apple App Store and Google Play Store requires a paid Adalo plan. Once subscribed, you can use the Publish panel to download the files needed for submission to these stores.
If you’re managing both web and mobile versions, you can handle them in the same builder or share a database between separate projects using the "Advanced Options" during setup. To gather feedback before your app goes live, generate a shareable link using the Share button. This lets external testers try out your app without needing an Adalo account.
Scale with MS SQL Backend
Once your app is deployed, focus on scaling to meet the demands of a growing user base. MS SQL is an excellent choice for handling large-scale operations, offering the flexibility needed for real-time data editing and deletion. Moving your data to an external MS SQL database not only supports scalability but also makes future migrations easier if your app outgrows Adalo’s framework. Developers have successfully used MS SQL backends to support apps with up to 100,000 users and 3 million actions per month.
To maintain performance as your app grows, use Adalo’s native list types (simple, card, or image) and enable the "Load Items as User Scrolls" option in advanced settings. This helps prevent lag when handling large datasets. Avoid embedding lists within other lists, as this can slow down your app significantly. Instead of using filters to calculate record counts on the fly, add a number property to your collection to track and display counts directly.
For more complex data operations and workflows, platforms like n8n or Latenode can handle tasks like branching logic and data transformations between Adalo and MS SQL. Since Adalo’s pricing is tied to "Actions" (Create, Update, Delete), keep a close eye on your database operations to manage costs effectively. Roll out features incrementally rather than all at once to ensure a smoother launch and better performance.
Adalo allows you to create and publish a production-ready app as a Progressive Web App or natively to iOS and Android - all from a single build. It’s a streamlined solution for turning your app idea into reality.
Conclusion
Building an app with MS SQL data using Adalo is a simple and efficient process. Start by setting up your MS SQL database, create an Adalo account, and use DreamFactory to connect your data to Adalo's user-friendly visual builder. This method streamlines development while taking advantage of MS SQL's robust data management capabilities. By pairing MS SQL's relational database power with Adalo's design tools, you can significantly cut development time - by as much as 90% compared to traditional methods.
This workflow is versatile enough to handle a variety of needs, whether you're launching an MVP, showcasing business data, or creating internal tools. Developers have already used MS SQL backends to power apps with up to 100,000 users and 3 million actions per month. These tools make it easier than ever to quickly turn your ideas into scalable applications.
With the no-code market projected to hit $187 billion by 2030 and 70% of new apps expected to be built using no-code solutions by 2025, there's no better time to get started. You can begin today - connect your MS SQL database, design your first app screen, and test the connection. This streamlined process enables you to launch scalable, production-ready apps for web, iOS, and Android with ease.
FAQs
How can I connect an MS SQL database to Adalo using DreamFactory?
DreamFactory makes it easy to connect your MS SQL database to Adalo by turning your database into a fully operational REST API. After creating a DreamFactory service for your SQL database, it automatically generates standard CRUD (Create, Read, Update, Delete) endpoints. Plus, it includes features like role-based access control, API key management, and optional OAuth authentication - no backend coding needed.
In Adalo, the External Collections feature lets you link directly to the API generated by DreamFactory. This setup allows Adalo to securely fetch, display, and even update data from your MS SQL database, all based on the permissions you set in DreamFactory. With this integration, your no-code app can interact with your database in real time, effortlessly bridging the gap between front-end and back-end functionality.
What security steps should I follow to connect an MS SQL database to Adalo?
To securely link your MS SQL database with an Adalo app, start by enabling TLS 1.2 or higher to ensure encrypted communication. This step safeguards your data while it moves between Adalo and your database. Next, tighten security by whitelisting Adalo’s IP addresses, allowing only authorized traffic to access your database server.
Set up a dedicated SQL login with minimal permissions, granting access solely to the tables your app requires. Avoid using admin credentials, and securely store login details in Adalo’s credential manager. If your setup allows, consider using Azure Active Directory or managed identities for added security and easier credential management.
Additionally, activate your database’s built-in firewall to block unauthorized access, and make it a habit to review connection logs for any unusual activity. By combining encryption, IP whitelisting, least-privilege access, and strong authentication, you can establish a secure and dependable integration between MS SQL and Adalo.
Can I publish my app to multiple platforms at the same time using Adalo?
Yes, Adalo lets you publish your app on both iOS and Android platforms. When your app is complete, Adalo offers tools to simplify the publishing process, helping you get your app into users' hands on different devices. However, you’ll still need to meet platform-specific requirements, such as adhering to app store guidelines, during the submission process.
Related Blog Posts
