- Working with the Database Tool Window
- Setting up a database connection
- Refactoring the Database
- Creating a Table
- Deleting a Tale, Index or Column
- Inserting and updating data
- Using the database console
- Generating a UML database diagram
- Database development with DDL data source
Working with the Database Tool Window
Database support is provided through the Database Tool Window on the right-hand side of the IDE. Use the View | Tool Windows | Database menu or Find Action, by pressing Ctrl+Alt+A (or _*Alt+CMD+A on Mac OS X) and search for "Database" to open it.
From the Database tool window, we can connect to various data sources and view database structure, modify it by refactoring tables and columns, manage data stored in tables by simply editing or adding values, write arbitrary SQL queries with code completion and syntax highlighting, and run SQL queries from code or the built-in SQL editor. We can generate UML diagrams showing relations between tables, copy records as INSERT statements, get smart completion when writing joins on tables and so on.
But first things first: let's setup a database connection.
Setting up a database connection
From the Database Tool window, we can use the green + icon in the toolbar or press Alt+Insert (CMD+N on Mac OS X) to create a new data source. A data source can be a "real" data source, connected to a database system, or a "DDL" data source which is a collection of files that describe tables, indexes and such and can be exported to a database server later on. See #Database development with DDL data source for more on DDL data sources.
If we want to collect to a database, we can connect to it by first picking the type of database. We can connect to Google Cloud SQL, DB2, Derby, H2, HSQLDB, MySQL, an ODBC connection, Oracle, PostgreSQL, SQL Server (and Microsoft Azure Database), SQLite an Sybase.
For every database system, different options will be available for configuring the database connection. Typically these will be the server, database name, username and password, but many drivers provide additional options for configuring the connection. PhpStorm does not ship with all database drivers installed, but it does provide a handy way of downloading them when needed: click the "Download .... driver files" link next to the warning about missing drivers to download them.
Here's another example for MySQL connecting to a local development database:
Under the Schemas & Tables tab, we will have to specify which schemas and tables we want to be able to manipulate and generate code completion information for.
We can test the settings using the Test Connection button, which will tell us if it succeeded (or not) in connecting to the database.
When the connection succeeds, we can save our settings. This will also trigger PhpStorm to download schema information from the database and display it in the database tool window. We can expand schemas, tables, columns and indexes and get more details on the database structure.
Refactoring the Database
Creating a Table
We can use the New | Table context menu (Alt+Insert or CMD+N on Mac OS X) to create a new table. A dialog will open in which we can give the new table a name and specify the columns that should be created.
Columns can be edited from the UI and PhpStorm will generate the DDL code for us. However if we wanted to add additional statements to the DDL, we can do so by editing them in the SQL preview editor. Completion is available by pressing Ctrl+Space. Let's make our id column auto increment (in the MySQL dialect).
Clicking OK will create the table in our database.
Deleting a Tale, Index or Column
To delete a table, index or column from our database, we can navigate to it and press Delete (or use the Delete context menu). This will show us a preview of the SQL statement that is about to be executed. It could be a DROP TABLE, an ALTER TABLE or something similar that gets generated.
Clicking OK will perform the change.