Introduction
Starting v9.0, you can use the Database Console to execute SQL queries via the OpenSpecimen app. This eliminates the need to log in to the DB VM to run SQL queries.
Only super admins can access the database console.
Ensure email settings are configured.
Ensure the correct email ID is mentioned on your user profile and “Disable notifications” is set to “No.”
When to use the Database Console?
Accessing the database should be done only in extreme situations when you cannot do the same operation via the User Interface. However, it is unavoidable at times. E.g., reverting a deleted object, resetting a token sequence, debugging a slow query, etc.
Which type of SQLs are allowed?
Allowed
DESCRIBE, SHOW CREATE, SHOW ON TABLES, TRIGGERS, PROCEDURES
INSERT, UPDATE, DELETE records in TABLES (DML statements)
Explain plan (to debug slow queries)
Not allowed
CREATE, DROP, ALTER, TRUNCATE TABLES, or any other objects.
How to access the Database Console?
Navigate to the ‘Extras’ → Database Console.
How to start a new session?
A. Click on the ‘New Session’ button:
B. Enter the reason to connect → Next:
C. You will receive an OTP in your email. Enter the OTP in the pop-up and click on ‘Verify’:
D. On verification, an email is sent to all super admins that you are accessing the database console.
How to execute SQLs?
Paste the SQL and click on the Run button.
Displays the output of the SQL
Shows the history of SQLs that you have executed in this session.
Audit: How to view the history of SQLs run?
You can download all past sessions and SQLs run by any super admin in the past. Click on the “Download” button to get a CSV file with all the information in a session.
