AppML SQL Data — Advanced Database Configuration and Queries

In this tutorial, you will learn about AppML SQL Data. We cover key concepts, practical examples, and best practices to help you master this topic.

Beyond basic table mappings, AppML supports custom SQL queries, stored procedures, database views, and complex joins, giving you full control over data access while keeping the convenience of automatic UI generation.

What You'll Learn

You will configure advanced SQL datasources with custom queries, leverage database views, call stored procedures, and optimize database performance within AppML.

Why It Matters

Real-world applications often need custom queries for reporting, data aggregation, and business logic. AppML's advanced SQL features let you handle these cases without leaving the model-based development flow.

Real-World Use

DodaZIP uses an AppML model with a custom SQL view that joins compression logs, user data, and storage metrics to generate real-time dashboard reports for the operations team.

flowchart LR
    A[AppML Model] --> B[SQL Data Service]
    B --> C[Direct Table]
    B --> D[Custom Query]
    B --> E[Database View]
    B --> F[Stored Procedure]
    C --> G[Auto CRUD]
    D --> G
    E --> H[Read-Only View]
    F --> I[Parameter Execution]
    style B fill:#1e293b,color:#fff

Custom SQL Queries

Replace the automatic table mapping with a custom SQL query for complex data requirements.

<appml>
  <datasource type="mysql">
    <connection server="localhost" database="sales" user="appml" password="pass"/>
    <table name="monthly_summary">
      <query>
        SELECT
          DATE_FORMAT(order_date, '%Y-%m') AS month,
          COUNT(*) AS order_count,
          SUM(total) AS revenue,
          AVG(total) AS avg_order_value
        FROM orders
        WHERE order_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
        GROUP BY DATE_FORMAT(order_date, '%Y-%m')
        ORDER BY month DESC
      </query>
      <field name="month" type="string" key="true"/>
      <field name="order_count" type="integer"/>
      <field name="revenue" type="decimal"/>
      <field name="avg_order_value" type="decimal"/>
    </table>
  </datasource>
</appml>

Expected output: A read-only summary view showing monthly sales data calculated from the orders table.

Custom query tables are automatically read-only because AppML cannot reverse-engineer the query for write operations.

Database Views

Use existing database views as AppML data sources for pre-joined data.

CREATE VIEW user_profiles AS
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

<appml>
  <datasource type="mysql">
    <connection server="localhost" database="shop" user="appml" password="pass"/>
    <table name="user_profiles">
      <field name="id" type="integer" key="true"/>
      <field name="name" type="string"/>
      <field name="email" type="string"/>
      <field name="order_count" type="integer"/>
      <field name="total_spent" type="decimal"/>
    </table>
  </datasource>
</appml>

Expected output: AppML reads from the database view and renders the joined data as a standard list view.

Joins Between Tables

AppML supports automatic joins through relationship definitions in the model.

<table name="orders">
  <field name="id" type="integer" key="true"/>
  <field name="user_id" type="integer">
    <relationship table="users" field="id"/>
  </field>
  <field name="total" type="decimal"/>
  <field name="status" type="string"/>
</table>

List view with joined column:

<view type="list" table="orders">
  <column field="id" header="Order ID"/>
  <column field="user_id" header="Customer" display="user.name"/>
  <column field="total" header="Total" format="currency"/>
</view>

Expected output: The list view shows the user's name instead of the user_id numeric value. AppML generates a LEFT JOIN automatically.

Parameterized Filters

Pass URL parameters to custom queries for dynamic filtering.

<table name="filtered_orders">
  <query>
    SELECT * FROM orders
    WHERE status = '{status}'
    AND order_date >= '{start_date}'
  </query>
  <field name="id" type="integer" key="true"/>
  <field name="status" type="string"/>
  <field name="total" type="decimal"/>
</table>

Expected output: Accessing the model with /orders?status=shipped&start_date=2026-01-01 filters the results accordingly. Parameters use curly brace syntax in the query.

Performance Optimization

Index the columns used in filters and relationships. For large tables, set pagination limits.

<table name="logs" page_size="50">
  <field name="id" type="integer" key="true"/>
  <field name="message" type="text"/>
  <field name="created_at" type="datetime"/>
</table>

Expected output: The list view shows 50 records per page with pagination controls. Database queries include LIMIT and OFFSET clauses.

Common Mistakes

1. Using custom queries without key fields: Every table needs at least one key field. For aggregated queries, use a Composite key or a generated unique value.

2. Not parameterizing custom queries: Direct string interpolation in queries creates SQL Injection risks. Always use the curly brace parameter syntax.

3. Forgetting that custom queries are read-only: AppML cannot generate INSERT, UPDATE, or DELETE for custom query tables. Use them only for reporting and display.

4. Overusing joins in list views: Each joined column adds a LEFT JOIN to the query. Too many joins slow down the database for large datasets.

5. Not setting page_size for large tables: Without pagination, AppML attempts to load all records at once, causing memory issues and slow rendering.

Practice Questions

1. How do you define a custom SQL query in AppML?

Use the <query> element inside the <table> definition with the raw SQL statement.

2. Why are custom query tables automatically read-only?

AppML cannot determine the underlying table structure to generate INSERT, UPDATE, and DELETE statements from arbitrary queries.

3. How do you reference a URL parameter in a custom query?

Use curly brace syntax: {parameter_name}. AppML substitutes the value safely.

4. What is the advantage of using database views with AppML?

Views encapsulate complex joins and aggregations in the database while AppML treats them as simple tables.

5. How does the page_size attribute affect performance?

It limits the number of records per query, reducing memory usage and database load.

Challenge

Create a database view that joins customers, orders, and order_items to show total spending per customer. Configure it as an AppML data source with pagination and sorting. Add a filter for minimum spending amount.

Frequently Asked Questions

Mini Project

Create a sales dashboard with two datasources: a direct table for entering new orders and a custom query view that shows monthly revenue, top products, and customer statistics using aggregation functions.

What's Next

Continue to AppML Views to learn how to customize the display of your data with different view types and layout options.