Skip to content

Database Query Tool

Database Query Tool

Article ID RC-CC-17 — Control Center: Database Query Tool
Domain Control Center (Admin)
Applies To REDCap administrators
Requires Any supported version
Verified Against REDCap v17.5.6 (Standard) — checked live on a test instance, read-only: the disabled notice and its default only, not the enabled tool; v17.3.7 (LTS) — changelog review only
Prerequisite REDCap super-user administrator access
Version 1.4
Last Updated 2026-11
Author See KB-SOURCE-ATTESTATION.md
Related Topics RC-CC-16 — Control Center: Database Activity Monitor; RC-CC-15 — Control Center: Top Usage Report
Synonyms control center database query tool; run read-only sql against the redcap database; query redcap mysql from the browser; ad-hoc sql lookups for troubleshooting; which sql statements are allowed in the query tool; investigate data with a database query; admin sql query interface redcap; read-only database access without command line; enable the database query tool; database query tool is disabled; query tool only shows 500 rows; database query tool results cut off at 500; return more than 500 rows from the query tool; raise the query tool row limit; export database query tool results to csv; download sql query results from the control center

1. Overview

The Database Query Tool allows REDCap administrators to run read-only SQL queries directly against REDCap's MySQL/MariaDB database from the browser. It is accessible under "Dashboards & Activity" in the Control Center sidebar and is intended for investigative queries, support troubleshooting, and ad-hoc data lookups without requiring command-line database access.


Off by default. A new 17.5.6 installation has the tool disabled (database_query_tool_enabled = 0). Both the Database Query Tool and Recent Errors menu links open a notice saying the tool is currently disabled, with the query to run in the MySQL database:

UPDATE redcap_config SET value = '1' WHERE field_name = 'database_query_tool_enabled';

Reload the page afterward. This needs someone with database access; there is no setting for it in the Control Center. The rest of this article describes the tool once enabled, and was not re-checked on 17.5.6.


2. Query Restrictions

Only read-only query types are permitted. Queries must begin with one of:

  • SELECT
  • SHOW
  • EXPLAIN

Any other query type (INSERT, UPDATE, DELETE, DROP, ALTER, etc.) is rejected. This restriction protects the database from accidental or unauthorized modification.


3. Query Context

A "Use query context" option allows the query to be run in the context of a specific project or scope, which may affect certain query behaviors or variable resolution. This is optional and depends on the nature of your query.


4. Entering and Running Queries

Administrators type their SQL into the query text box and execute it. Results are displayed in a table directly on the page, and the Export CSV button downloads them as a CSV file (see Common Questions). Multiple query rows can be added for running several queries in sequence.

Results are capped at 500 rows by default. A query with no LIMIT clause returns at most 500 rows, and the query still runs without an error, so it's easy to miss that a longer result was cut off. If a query returns exactly 500 rows, assume the list is incomplete. To get more, end the query with your own LIMIT and a higher number, for example LIMIT 100000. An explicit LIMIT replaces the 500-row default, and results run past 500 rows.

Example queries:

  • SELECT project_id, project_name FROM redcap_projects WHERE status = 0
  • SELECT COUNT(*) as record_count FROM redcap_data WHERE project_id = 123
  • SELECT * FROM redcap_log_event WHERE user = 'username' LIMIT 100

4.1 Built-In System Query: Recent Errors

The Control Center sidebar includes a Recent Errors menu item under "Dashboards & Activity." Despite appearing as a distinct navigation link, it is not a separate tool — it simply opens the Database Query Tool with a pre-canned SQL query already loaded and executed:

select * -- Recent Errors (retained for 30 days)
from redcap_error_log 
order by error_id desc

This query returns all rows from redcap_error_log, sorted newest first. The comment in the query (-- Recent Errors (retained for 30 days)) documents the retention window: REDCap automatically purges error log entries older than 30 days.

Because Recent Errors is just a convenience shortcut into the Database Query Tool, you can modify the query once it loads — for example, adding a WHERE clause to filter by error type or a LIMIT clause for large logs.


5. Custom Query Management

The tool supports saving, organizing, and reusing named custom queries, which is helpful for frequently-run investigative queries:

  • Add or edit custom queries — through an in-page dialog where each query has a name and SQL text
  • Organize queries — arrange queries into folders or groups for easier navigation
  • Export custom queries (CSV) — download all custom queries as a CSV file for backup, documentation, or sharing
  • Import custom queries (CSV) — upload a CSV to add or modify custom queries in bulk

Custom Query CSV Format

The CSV format for importing/exporting includes columns for: - Query name/title (descriptive label) - SQL text (the query statement)

All imported queries must begin with SELECT, SHOW, or EXPLAIN. Imports containing other query types are rejected with an error message.

Custom Query Best Practices

  • Use descriptive names for your custom queries (e.g., "Count of Active Projects", "User Login History")
  • Include comments in SQL text to document the purpose of complex queries
  • Regularly export your custom query collection for backup
  • Share useful queries with your team via CSV import

Note (15.2.1): CSV exports of a saved custom query's results include the query title in the filename, so a folder of exports remains identifiable rather than becoming a set of near-identical generic filenames.


6. Database Table Reference

The right sidebar lists all REDCap database tables, providing a quick reference when composing queries. This reference covers all core REDCap tables, including:

  • redcap_projects — project metadata (title, status, dates)
  • redcap_metadata — field/instrument definitions (data dictionary)
  • redcap_dataX — stored data entry values. Numbered, not singular — a project's data lives in a specific table such as redcap_data8
  • redcap_log_eventX — system audit log and activity records, likewise numbered, e.g. redcap_log_event10
  • redcap_user_information — user accounts and profile data
  • redcap_alerts — alert configurations and definitions
  • redcap_data_access_groups — DAG (Data Access Group) definitions
  • redcap_auth — authentication records and session data
  • redcap_surveys — survey configurations
  • redcap_surveys_participants — survey participant records
  • redcap_surveys_queue — survey queue entries
  • redcap_events — events in longitudinal projects
  • redcap_arms — arms in longitudinal projects
  • redcap_instruments — instrument/form definitions
  • And many others covering every aspect of REDCap's data model

Critical — data and logging tables are numbered, and which one a project uses varies. REDCap distributes projects across several redcap_dataX and redcap_log_eventX tables for performance. A query written against redcap_data alone will return nothing or the wrong project's rows. To find the tables a given project actually uses, open Control Center → Edit Project Settings for that project — the data table and logging table are named at the top of the page, alongside a Move Project Data tool. See RC-CC-24 — Control Center: Edit Project Settings.

The set grows over time: 16.0.0 added two further data tables and three further log_event tables. A query that enumerated the tables correctly before an upgrade may miss projects created afterwards, so do not hard-code the list.

Click any table name in the sidebar to view its column structure and field definitions.


7. Common Use Cases

The Database Query Tool is helpful in these scenarios:

  • Looking up a specific user's account details, permissions, or login history
  • Counting records, projects, or users that meet specific criteria not exposed in the UI
  • Verifying data integrity (e.g., checking if a specific field value exists across records)
  • Troubleshooting support tickets by querying log data
  • Generating ad-hoc reports not available through the standard reporting interface
  • Auditing data changes by querying the audit log table
  • Finding orphaned records or incomplete data structures
  • Analyzing project configurations across multiple projects

8. Performance Considerations

Because the Database Query Tool provides direct database access, be mindful of performance:

  • Avoid large, unfiltered queries — SELECT * FROM redcap_log_event without a WHERE clause can put significant load on the database server, especially if the audit log is large
  • Use LIMIT — especially when exploring unfamiliar data, start with LIMIT 100 or LIMIT 10 to test before retrieving thousands of rows
  • Expect at most 500 rows — the tool shows 500 rows by default (section 4), so for full lists check whether you hit the cap
  • Index awareness — queries that filter on indexed columns (e.g., project_id, user_id) will perform much better than those filtering on unindexed fields
  • Avoid joins on large tables — joining redcap_data with redcap_log_event without specific filtering can be slow
  • Run exploratory queries during off-peak hours if possible

9. Access and Safety

This tool requires super-user administrator access. Because it provides direct database access:

  • Queries should be composed carefully to avoid unexpected performance impacts
  • REDCap's read-only enforcement prevents accidental data modification
  • Only users with full administrator privileges can access this tool
  • Consider documenting queries used for troubleshooting in case the same issue occurs later


11. Common Questions

Q: What is the "Recent Errors" menu item in the Control Center? "Recent Errors" is a shortcut that opens the Database Query Tool with a pre-loaded query against redcap_error_log, sorted by most recent error first. It is not a standalone tool — it is simply a pre-canned SELECT * query with a 30-day retention window documented in the SQL comment. Once the page loads you can edit the query normally, for example to filter on a specific error type.

Q: Can I run UPDATE or DELETE queries to fix data? No. The Database Query Tool only accepts read-only query types (SELECT, SHOW, EXPLAIN). Any attempt to run INSERT, UPDATE, DELETE, DROP, ALTER, or other write-based queries will be rejected. This restriction prevents accidental data modification. If you need to modify data, use the REDCap UI or contact your database administrator.

Q: How do I know which table contains the data I am looking for? The Database Query Tool displays a sidebar with all REDCap database tables. Click on any table name to view its column structure and field definitions. Review the table documentation to understand which tables contain your target data. For example, user information is in redcap_user_information, and data values are in redcap_data.

Q: Can I save queries I write frequently? Yes. The tool supports saving named custom queries. Add or edit custom queries through the in-page dialog, give each query a descriptive name, and save it. You can then reuse that query without re-typing it. You can also organize queries into folders and export/import them as CSV files.

Q: What is "Use query context" and when should I use it? The query context option allows a query to be run within the scope of a specific project. This may affect how certain variables or calculations are resolved. Use this option if you are running a query that depends on project-specific settings or variables. For most queries, this is optional.

Q: Why does my query stop at exactly 500 rows? The Database Query Tool shows at most 500 rows by default when the query has no LIMIT clause. A result of exactly 500 rows is almost always cut off. Add your own LIMIT with a higher number at the end of the query (for example LIMIT 100000) and run it again. An explicit LIMIT replaces the default, so the full result comes back.

Q: How do I export the results of my query? The tool has an Export CSV button for query results. Click it after the query runs to download the results as a CSV file. Results of an unsaved query download with a generic, timestamped filename such as query_tool_export_2026-11-18_1755.csv; results of a saved custom query include the query's title in the filename. If you run the same query on several servers, rename the files or add a column that identifies the server, because the generic filenames don't say where the data came from.


12. Common Mistakes & Gotchas

Running very large or unfiltered queries without a LIMIT clause. A query like SELECT * FROM redcap_log_event with no WHERE clause or LIMIT can retrieve millions of rows, which will freeze your browser, consume significant memory, and put load on the database server. Always start with a LIMIT clause (e.g., LIMIT 100) when exploring unfamiliar data, and use specific WHERE clauses to narrow results.

Treating a 500-row result as complete. The tool shows 500 rows by default, and the cut is easy to miss. Notification lists, user audits and inventories are the usual victims: people past row 500 are missing. For any query meant to return a complete list, end it with an explicit LIMIT well above the number of rows you expect (for example LIMIT 100000). To be sure, check the total with SELECT COUNT(*) on the same FROM and WHERE.

Forgetting to filter on indexed columns. Queries that filter on indexed columns (such as project_id or user_id) will perform much faster than queries that filter on unindexed fields. If a query takes a very long time, try restructuring it to filter on indexed columns first, or run the query during off-peak hours when database load is lower.

Importing custom queries with non-SELECT statement types in CSV. When importing custom queries from a CSV file, all queries must begin with SELECT, SHOW, or EXPLAIN. If your import includes UPDATE, DELETE, or other write-type queries, the entire import will be rejected. Verify all queries in your CSV before importing, or test the import in a non-production environment first.