Working With Databases In JetBrains Rider
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.
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 :
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.
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.
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.
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.
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!
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.
When we get the SQL wrong, Rider will also give us hints on how to fix our issue.
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.
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.
Subscribe to a monthly digest curated from the .NET Tools blog:
