Databases

The Interview platform allows you to create custom databases and attach them to questions to assess a candidate’s data processing skills.

• Creating a custom database
• Adding a database to a question
• Accessing the database in an interview

⚠️Note that this custom database feature is currently not available for use with multi-file frameworks like React or Angular. For these environments you can add the database file directly to the file directory when you create the question.

Creating a custom database

You can upload custom databases and attach them to questions to test a candidate’s ability to manage, write, and edit database queries either using SQL or a language ORM/adaptor.

✅ Interview’s custom databases are available for MySQL and PostgreSQL.

To start, navigate to your Interview dashboard and click Databases in the left-side navigation bar to go to the Custom Databases page. Then click Create Database.

Add a Title, Description, and select MySQL or PostgreSQL from the Language dropdown.

Below that you’ll see the SQL script that will create the sample database. You can edit this as needed to create your desired database; alternatively you can copy and paste SQL from an existing database into the text box for further customization.

✅ The easiest way to get the raw SQL from an existing database in order to upload it to Interview is with mysqldump for MySQL, or pg_dump for PostgreSQL. You can upload up to 5 MB of raw SQL.

Verify the details and click Save to create the database.

You’ll see a notification that your database is initializing.

❗If you see an error message, it means your SQL is structured incorrectly. Please correct the errors and try again. 

Once the database is initialized, you’ll see the verified Database Summary and Table Schema.

In the Databases tab on the dashboard you’ll now see your new database in the list of available custom databases.

Add a database to a question

Custom databases must be associated with a question from the Question Bank. You can do this in Step 2 – Add Code Elements of question creation or editing by simply selecting your database from the Custom Database dropdown menu.

When you load the question into a pad, the database will be available for querying either directly through MySQL/PostgresSQL, or through an ORM/adaptor for languages that have them.

Accessing the database in the interview

Once you’ve created your question and custom database, the database will automatically be accessible when you create the pad. There are two ways for a candidate to access it within the pad:

1. Via an ORM or Adaptor
2. Via SQL

Via an ORM or Adaptor:

Interview provides common ORM/adaptor libraries so you can directly query databases from non-database language environments. For example, you can now have candidates load SQL records and interact with them in Python, or show their ActiveRecord skills within the Ruby environment.

This feature is available for the most commonly used languages in Interview, preconfigured with the adaptors listed below. Additional languages may be added based on customer needs.

• Java – JDBC adaptor
• Javascript – Sequelize, TypeORM
• Python 2 & 3 – SQLAlchemy
• C++ – MySQL Connector, libpqxx (PostgreSQL)
• Ruby – ActiveRecord, Sequel
• C# – Dapper, Entity Framework, MySQL Connector, Npgsql

To setup data access through an adaptor, you’ll first need to select one of the supported languages listed above from the language dropdown.

Once you do this, you’ll see a new Database adapters menu appear next to the language; this menu auto-loads the database adaptor.

Click on the dropdown, and choose whether you’d like to use the MySQL or PostgreSQL adaptor. Selecting an adaptor automatically loads example adaptor/ORM code in the left hand pane (1). You can see your database schema in the right hand pane under the Database tab (2):

❗Loading the DB adaptor code will comment out all existing code, including any question code. Best practice is to load the question at the start of the interview, and you may need to rearrange code a little once you load the adaptor. This approach does provide the most flexibility in candidate interview language choice or preferred DB adaptor.

✅ If you do not have a database attached to a question when you select an adaptor package, the pad will load a sample database for you.

Keep in mind that the adaptor package is just helper code. As mentioned above, you also have the option to paste in the adaptor code when setting up a question. This lets you skip the package loading step, as the database will always be loaded with the question, not the package.

This does require you to select a particular language and adaptor for the candidate to use when setting up the question, so there is less flexibility at interview time.

Via SQL:

Candidates can write and run SQL queries against the database at interview in the code editor. Once you select your SQL flavor (MySQL or PostgreSQL), there is no further configuration required.

Keep in mind…

• When you set up your own custom database, the original DB will always be saved and never edited; a copy is loaded for each new interview session.
• Edits – such as row inserts, updates and deletes – are stateful during a specific interview session and persist between subsequent code execution.
• You can reset the DB back to its original unmodified state during a specific interview by pressing the Reset button in the top right corner.