Auto-Generated APIs Overview

GoPie automatically generates REST APIs for every dataset you create, providing instant programmatic access to your data without writing any code.

How It Works

When you upload a dataset or connect to a database, GoPie:

Analyzes the schema to understand data structure
Generates REST endpoints for CRUD operations
Creates OpenAPI documentation automatically
Applies security policies based on your permissions
Optimizes queries for performance

Generated Endpoints

For each dataset, GoPie creates a comprehensive set of REST endpoints:

Base URL Pattern

https://api.gopie.io/api/v1/datasets/{dataset_id}/data

Available Endpoints

Method	Endpoint	Description
`GET`	`/`	List all records with pagination
`GET`	`/{id}`	Get a specific record
`POST`	`/`	Create a new record
`PUT`	`/{id}`	Update a record
`PATCH`	`/{id}`	Partially update a record
`DELETE`	`/{id}`	Delete a record
`POST`	`/query`	Execute custom queries
`GET`	`/aggregate`	Perform aggregations
`GET`	`/export`	Export data in various formats

Example Usage

Let's say you upload a dataset called "customers" with the following schema:

{
  "columns": [
    { "name": "id", "type": "integer", "primary_key": true },
    { "name": "name", "type": "string" },
    { "name": "email", "type": "string" },
    { "name": "created_at", "type": "timestamp" }
  ]
}

GoPie automatically generates these APIs:

# Get all customers with pagination
curl -X GET "https://api.gopie.io/api/v1/datasets/ds_customers/data?page=1&limit=20" \
  -H "Authorization: Bearer YOUR_TOKEN"

# Response
{
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]",
      "created_at": "2024-01-15T10:00:00Z"
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 150,
    "pages": 8
  }
}

# Get specific customer
curl -X GET "https://api.gopie.io/api/v1/datasets/ds_customers/data/1" \
  -H "Authorization: Bearer YOUR_TOKEN"

# Response
{
  "id": 1,
  "name": "John Doe",
  "email": "[email protected]",
  "created_at": "2024-01-15T10:00:00Z"
}

# Create new customer
curl -X POST "https://api.gopie.io/api/v1/datasets/ds_customers/data" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Jane Smith",
    "email": "[email protected]"
  }'

# Response
{
  "id": 151,
  "name": "Jane Smith",
  "email": "[email protected]",
  "created_at": "2024-01-20T14:30:00Z"
}

# Update customer
curl -X PUT "https://api.gopie.io/api/v1/datasets/ds_customers/data/1" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "John Doe Updated",
    "email": "[email protected]"
  }'

Advanced Features

Filtering

Use query parameters to filter results:

# Filter by email domain
GET /api/v1/datasets/ds_customers/data?filter[email][contains][email protected]

# Multiple filters
GET /api/v1/datasets/ds_customers/data?filter[created_at][gte]=2024-01-01&filter[name][starts_with]=John

Supported Operators

Operator	Description	Example
`eq`	Equals	`filter[status][eq]=active`
`neq`	Not equals	`filter[status][neq]=deleted`
`gt`	Greater than	`filter[amount][gt]=100`
`gte`	Greater than or equal	`filter[created_at][gte]=2024-01-01`
`lt`	Less than	`filter[amount][lt]=1000`
`lte`	Less than or equal	`filter[age][lte]=65`
`contains`	Contains substring	`filter[name][contains]=smith`
`starts_with`	Starts with	`filter[email][starts_with]=admin`
`ends_with`	Ends with	`filter[email][ends_with][email protected]`
`in`	In array	`filter[status][in]=active,pending`
`nin`	Not in array	`filter[status][nin]=deleted,archived`
`is_null`	Is null	`filter[deleted_at][is_null]=true`

Sorting

Control the order of results:

# Sort by created date (descending)
GET /api/v1/datasets/ds_customers/data?sort=-created_at

# Multiple sort fields
GET /api/v1/datasets/ds_customers/data?sort=-created_at,name

Field Selection

Specify which fields to return:

# Only return specific fields
GET /api/v1/datasets/ds_customers/data?fields=id,name,email

# Exclude fields
GET /api/v1/datasets/ds_customers/data?exclude=created_at,updated_at

Aggregations

Perform aggregations on your data:

# Count by status
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=status&agg=count

# Sum of amounts by month
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=date_trunc(month,created_at)&agg=sum(amount)

# Multiple aggregations
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=customer_id&agg=count,sum(amount),avg(amount)

Custom Queries

Execute SQL queries directly:

curl -X POST "https://api.gopie.io/api/v1/datasets/ds_customers/data/query" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM customers WHERE created_at > :date",
    "parameters": {
      "date": "2024-01-01"
    }
  }'

Custom queries are subject to security restrictions. Only SELECT queries are allowed, and they must respect row-level security policies.

Relationships

GoPie automatically detects and handles relationships between datasets:

One-to-Many Relationships

# Get customer with their orders
GET /api/v1/datasets/ds_customers/data/1?include=orders

# Response includes related data
{
  "id": 1,
  "name": "John Doe",
  "email": "[email protected]",
  "orders": [
    {
      "id": 101,
      "customer_id": 1,
      "amount": 99.99,
      "created_at": "2024-01-10T10:00:00Z"
    }
  ]
}

Many-to-Many Relationships

# Get products with categories
GET /api/v1/datasets/ds_products/data/1?include=categories

# Filter by relationship
GET /api/v1/datasets/ds_products/data?filter[categories.name][eq]=Electronics

Data Types and Validation

GoPie enforces data types and validates inputs based on your schema:

Type Mapping

Schema Type	API Type	Validation
`integer`	`number`	Must be whole number
`decimal`	`number`	Decimal with precision
`string`	`string`	Max length enforced
`boolean`	`boolean`	`true` or `false`
`date`	`string`	ISO 8601 date
`timestamp`	`string`	ISO 8601 datetime
`json`	`object`	Valid JSON
`array`	`array`	Type-checked elements

Validation Rules

{
  "email": {
    "type": "string",
    "format": "email",
    "required": true,
    "unique": true
  },
  "age": {
    "type": "integer",
    "minimum": 0,
    "maximum": 150
  },
  "status": {
    "type": "string",
    "enum": ["active", "inactive", "pending"]
  }
}

OpenAPI Documentation

Every generated API comes with OpenAPI 3.0 documentation:

Access Documentation

# Get OpenAPI spec
GET /api/v1/datasets/{dataset_id}/openapi.json

# Swagger UI
GET /api/v1/datasets/{dataset_id}/docs

Example OpenAPI Spec

openapi: 3.0.0
info:
  title: Customers Dataset API
  version: 1.0.0
paths:
  /api/v1/datasets/ds_customers/data:
    get:
      summary: List customers
      parameters:
        - name: page
          in: query
          schema:
            type: integer
        - name: limit
          in: query
          schema:
            type: integer
      responses:
        '200':
          description: List of customers
          content:
            application/json:
              schema:
                type: object
                properties:
                  data:
                    type: array
                    items:
                      $ref: '#/components/schemas/Customer'

Client SDK Generation

GoPie can generate client SDKs for your APIs:

// Auto-generated JavaScript client
import { CustomersAPI } from '@gopie/sdk';

const api = new CustomersAPI({
  token: 'YOUR_TOKEN'
});

// List customers
const customers = await api.list({
  page: 1,
  limit: 20,
  filter: { email: { contains: '@company.com' } }
});

// Create customer
const newCustomer = await api.create({
  name: 'Jane Smith',
  email: '[email protected]'
});

# Auto-generated Python client
from gopie_sdk import CustomersAPI

api = CustomersAPI(token='YOUR_TOKEN')

# List customers
customers = api.list(
    page=1,
    limit=20,
    filter={'email': {'contains': '@company.com'}}
)

# Create customer
new_customer = api.create(
    name='Jane Smith',
    email='[email protected]'
)

// Auto-generated Go client
import "github.com/gopie/sdk-go"

client := gopie.NewClient("YOUR_TOKEN")
api := client.Datasets("ds_customers")

// List customers
customers, err := api.List(gopie.ListOptions{
    Page: 1,
    Limit: 20,
    Filter: map[string]interface{}{
        "email": map[string]string{"contains": "@company.com"},
    },
})

// Create customer
newCustomer, err := api.Create(gopie.Customer{
    Name: "Jane Smith",
    Email: "[email protected]",
})

Performance Optimization

Automatic Optimizations

GoPie applies several optimizations automatically:

Index Creation: Creates indexes on frequently queried columns
Query Caching: Caches repeated queries for 5 minutes
Connection Pooling: Maintains connection pools for efficiency
Result Pagination: Enforces pagination to prevent large transfers

Manual Optimizations

You can hint at optimizations:

# Use cursor-based pagination for large datasets
GET /api/v1/datasets/ds_logs/data?cursor=eyJpZCI6MTAwMH0&limit=50

# Request compressed responses
GET /api/v1/datasets/ds_customers/data
Accept-Encoding: gzip

# Use field selection to reduce payload
GET /api/v1/datasets/ds_customers/data?fields=id,name

Security

Authentication

All generated APIs require authentication:

# Bearer token
Authorization: Bearer YOUR_TOKEN

# API Key
X-API-Key: YOUR_API_KEY

Row-Level Security

APIs respect row-level security policies:

-- Only see your organization's data
CREATE POLICY org_isolation ON customers
  USING (organization_id = current_setting('app.org_id'));

Rate Limiting

Generated APIs have rate limits:

Read operations: 1000 requests/minute
Write operations: 100 requests/minute
Aggregations: 100 requests/minute
Custom queries: 50 requests/minute

Webhooks

Configure webhooks for data changes:

{
  "url": "https://your-app.com/webhook",
  "events": ["record.created", "record.updated", "record.deleted"],
  "filters": {
    "amount": { "gt": 1000 }
  }
}

Best Practices

Use field selection to reduce payload size
Implement pagination for large datasets
Cache responses on the client side
Use webhooks instead of polling
Monitor rate limits in response headers
Use appropriate indexes for filter fields
Batch operations when possible

Limitations

Maximum 10,000 records per request
Custom queries limited to SELECT operations
Transactions not supported across API calls
Some complex data types may have limited support

Next Steps

Explore Endpoints documentation for detailed API reference
Learn about OpenAPI Specifications
Review Best Practices for API usage

Auto-Generated APIs Overview

On this page