Raw SQL queries
For table pages in Kottster, you can define custom SQL queries to fetch data directly from your database. This is useful for complex queries or when you want to optimize performance by fetching only the necessary data.
To set up custom SQL queries, create a page using the visual builder. Choose the "Raw SQL Query" as a fetch strategy.
Basic SQL queries
Here are examples of simple SQL queries for different database systems:
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESCSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESCSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESCSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESCPagination support
If pagination is being used, your query needs to be modified to use :limit and :offset parameters:
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offsetSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offsetSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT :limit OFFSET :offsetSELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
OFFSET :offset ROWS
FETCH NEXT :limit ROWS ONLYCount queries for pagination
When using pagination, you also need to provide a count query to calculate the total number of records. This is required for proper pagination controls:
SELECT COUNT(*)
FROM users
WHERE status = 'active'SELECT COUNT(*)
FROM users
WHERE status = 'active'SELECT COUNT(*)
FROM users
WHERE status = 'active'SELECT COUNT(*)
FROM users
WHERE status = 'active'Column configuration
When using raw SQL queries, Kottster automatically detects the columns based on the properties of the records returned.