Working With Databases In JetBrains Rider

Khalid Abuhakmeh

All of our IDE’s are built on the same core IntelliJ IDEA Community Edition. This means that when we improve one IDE, our other IDE’s usually also benefit from our improvements. Rider is no exception! I recently reached for DataGrip, our amazing database administration tool, only to remember that Rider has many of the same features.

Basic Overview

When starting Rider, we notice a Database tab hiding in the corner of our development environment. If the tab is not visible, it can be made visible using the View | Tool Windows | Database menu. A third option for opening the Database tab (or any other feature in Rider) is to use Go to Action or Search Everywhere (double-shift) and search for Database.

This tool window is our portal to database bliss, where we’ll be able to perform various database administration tasks.

Adding a New Database Server

We’ve done extensive work supporting many different database engines. Some of the immediately recognizable database engines are Microsoft SQL Server, PostgreSQL, MariaDB, and SQLite. Let’s add a database connection.

The first step is to click the button in the top navigation bar, hovering over Data Source, and selecting the database engine of our choice. In this example, we’ll be adding Microsoft SQL Server.

Adding a new database server connection

In the Data Sources and Drivers window, we can enter general connection settings like host, port, user, password, and the default database. Note that Rider will ask us to download a specific driver for our database engine.

We can test our connection settings by clicking the Test Connection near the bottom of the modal. On a successful connection, we should see the version of SQL Server prefixed with a green checkmark.

Now the real fun begins! In the next section, We’ll look at how we can leverage the features in Rider to develop our database-backed applications more efficiently.

Creating a New Database

Once we have a working connection, we can create a new database. Use the context menu on our new connection in the Database tab and select New | Database. 

We’ll see a modal with a preloaded SQL Script. We can change the name to whatever we like, and click the Execute button. In the screenshot, we called our database test :

Add a new database

What with existing databases? On to the next topic!

Adding an Existing Database

If we already have a database instance on our server, we can add it to the list of visible databases by clicking the grayed-out numbers by our connection.

Adding an existing database

We have the option to show all databases or only see the specific databases. The ability to toggle visibility also works at the database where we can show or hide database schemas.

Working with a Database

In the previous section, we connected to our SQL Server instance and created a new database named test. Rider has many SQL tasks available through context menus and actions. In this section, we’ll use the UI tools to create a Persons table and insert some data into our database.

We first need to be sure we have a database schema selected, after which we’ll create the table in the dbo schema.

Creating a new table in our database

Let’s insert some data into our table using the table view. We start by double-clicking the newly created Persons table to see the table view. We can then click the + toolbar button to insert a new row. Finally, we commit the transaction to our database.

Inserting a new row using the table view

Rider’s console shows a helpful dialog displaying the data source, schema, table, and first rows of our new table when we are writing our own SQL. When toggling Alt+Enter or Cmd+Enter, Rider is kind enough to ask us which scripts we’d like to execute. If our console has more than one SQL statement, we can cycle through each one using the up and down arrow keys on our keyboard.

SQL Console help dialog

That’s a cool console editor, but let’s see how we can take advantage of Rider in our code base.

Working with Databases in .NET Apps

Whether we use established ORMs like Entity Framework, micro-ORMs like Dapper, or roughing it with raw ADO.NET, Rider understands our code. In doing so, it can add tremendous value to any database project.

Starting with ADO.NET, we’ll see how Rider can identify SQL statements in a string literal. Before continuing, be sure the data source has a Database name specified.

static async Task Main()
{
   await using var connection = new SqlConnection(ConnectionString);
   var command = connection.CreateCommand();
  
   command.CommandText = "select * from Persons";
   command.ExecuteReaderAsync();
}

Immediately after qualifying as a SQL statement, we start to see syntax highlighting in our string literal. That’s right! SQL code completion inside a string!

SQL syntax highlighting in C#

Using Alt+Enter in our SQL string literal, we can see several context actions come available. To run our query without having to copy/paste, we can use the Run in Console action. Next, we select a session and can choose to attach to an existing console or start a new console. Let’s start a new console.

C# SQL execute in Console

When we get the SQL wrong, Rider will also give us hints on how to fix our issue.

SQL error in our C#

If we’re a Dapper user, the same experience applies. Remember, Rider is looking at string literals, and making the determination if the string contains SQL.

Rider can also supercharge our ability to write SQL statements. Since Rider has a connection to our data source, it also understands our schema. This gives us powerful code completion, and an ability to quickly navigate to the direct spot in the schema.

SQL autocomplete in C#

Conclusion

This post barely scratches the surface of what Rider can do for our SQL and .NET workflows. We can stay in one tool and accomplish all of our database administration needs. Rider empowers us to write, inspect, and execute SQL statements directly from our code against one or many data sources. Rider can help regardless of our data-access technology of choice.

Download Rider 2020.1 EAP and give it a try! Thanks for reading, and please leave comments.

Comments below can no longer be edited.

2 Responses to Working With Databases In JetBrains Rider

  1. Marcel Bradea says:

    March 8, 2020

    Guys Rider still doesn’t support Entity Framework 6 on .NET Core 3.x

    This is forcing us to spin up VMs and run Windows, Visual Studio just so that we can run simple database migration commands.

    This is a huge regression in terms of requiring us to use Visual Studio again after several years of purely with Macs+Rider.

    When is this getting fixed?
    https://youtrack.jetbrains.com/issue/RIDER-32813
    https://youtrack.jetbrains.com/issue/RIDER-19973

    This got marked as a dupe of a problem that has been left unprioritized over a year. Mutating your database is critical functionality! When can you prioritize this?

    btw EF Core is still far from being ready for production, so EF 6 is the only viable database to use in production for .NET today (and will likely remain the case for the next few years to come). If this is the reason this has gone ignored, it is a highly INVALID reason. EF 6 is very much still the go-to ORM being leveraged in most .NET codebases today.

  2. Louis St-Amour says:

    March 12, 2020

    For EF 6.3 or EF 6.4 support, it’s possible https://github.com/dotnet/ef6/issues/1053#issuecomment-582876786 might work? I don’t personally use EF right now myself, can’t confirm directly.

Subscribe

Subscribe to .NET Tools updates