Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

Writing an application, there's a massive chance that we have to work with a database. Whether it's performing some routine tasks like querying for a record or checking what that column was named again, database development where we have to create the schema structure or just developing PHP code against it, PhpStorm can help us. In this tutorial, we will see how.

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:

Icon

For most drivers, we can just enter the connection details and PhpStorm will generate the underlying database connection string for us. For some, we may have to enter a full JDBC connection string. Try searching the Internet for "<driver/database type> jdbc connection string" if you encounter such driver.

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.

Icon

When trying to delete a table or column that is referenced in an index or foreign key constraint, the IDE will warn us about this. We will have to manually delete the reference first.

Adding columns

Rename table or column

Inserting and updating data

Using the database console

Generating a UML database diagram

Database development with DDL data source

  • No labels