Manage Your Database Schema in IntelliJ IDEA 12

Posted on by Andrey Cheptsov

As you know, IntelliJ IDEA provides built-in database tools with support for many SQL dialects, such as Oracle, PostgreSQL, MySQL, Microsoft SQL Server and others.

A while ago we shared this video with a quick overview of how to set up a connection to a database, use smart completion (even for SQL queries within Java), on-the-fly code analysis, navigation (between queries and database), search (for tables and fields), and more cool features.

Now, please enjoy a new video with even more exciting features available in IntelliJ IDEA 12 for working with databases:


Manage Your Database Schema from the IDE

Now you are able to define tables, columns, indexes and foreign keys simply from the Database tool window via the shortcut (Ctrl+N) or the context menu.

Create New Table

The new table wizard brings code completion and SQL preview with support for all listed dialects, so you can define a new table for any database in seconds.

Modify Column

If you modify a column (Cmd+F6), the IDE will automatically show you how your changes affect the project and will apply the appropriate changes to the code.

Transaction Control

The table editor and query console support manual transaction control, so you can commit more than one change at once or roll it back safely whenever you want.

Data Export

Data from the table editor or query results may be exported to a file or clipboard in the following formats: CSV, TSV, HTML, SQL Insert/Update statements.

CSV Editor

If you open a CSV file in the editor, the IDE will offer you to edit data in a table.

Quick Documentation

The Database tool window also provides the Quick Documentation action via Ctrl+J for any selected element from the database. The popup window shows the SQL definition for selected item and the first 10 rows (for tables and views).

As databases are part of most projects today, it was about time for us to help developers work with them more productively. Find these and many other features for database development in the latest release of IntelliJ IDEA.

Feel free to share your feedback with us in our discussion forum and in our issue tracker.

Develop with Pleasure!

Comments below can no longer be edited.

23 Responses to Manage Your Database Schema in IntelliJ IDEA 12

  1. Erik Pragt says:

    December 19, 2012

    Pretty cool. Is there also a possibility to, instead of creating DDL, create liquibase migrations?

  2. Sergey says:

    December 19, 2012

    @Erik a brand new migration action is available from the database diff dialog.

  3. Chris Jenkins says:

    December 19, 2012

    Is there sqlite support? I like to mock databases before implementing them in Android.

  4. Andrey Cheptsov says:

    December 19, 2012

    @Chris Yes, SqlLite is supported!

  5. Wes says:

    December 19, 2012

    Any intention to add UML/ERD abilities to Intellij for databases?

  6. Daemon says:

    December 20, 2012

    How about adding an option to tunnel through SSH when connecting? I know there are ways to get it working, but it really needs to get built into the UI.

  7. Martyn says:

    December 20, 2012

    very nice is there a way to create a table with the primary key auto incrementing?

    • Eugene van der Merwe says:

      June 17, 2015

      I have the same question, how do you create a column (in this case MySQL) that auto increments? I cannot seem to find this in the GUI…

      • Andrey Cheptsov says:

        June 17, 2015

        This option has been added in 14.1 as far as I remember.

  8. Neville Ridley-Smith says:

    December 20, 2012

    I’m having difficulty getting the csv editor to work.
    When I specifically save a csv file from the database browser it comes up with the csv editor. If I open up a csv file otherwise, it just uses the standard text editor.
    I’ve tried removing .csv from the Text filetypes but that didn’t help.

  9. ritesh says:

    January 2, 2013

    @jetbrains..I think one feature that would immensely help is autocomplete table names in console (CTRL + Space) – any plans of getting it or is it simply something i am missing.

  10. scott says:

    January 10, 2013

    I second @ Daemon’s request regarding an SSH feature. I currently use Navicat for database related tasks which has an SSH tunnel feature out of the box.

  11. Fouad Loucif says:

    March 3, 2013

    I’m Using IntelliJ Idea 12, in the database designer I’m connecting an SQLite database but designer shows all my fields as TEXT except the decimal ones, is there any issue to let the designer retreives the right types of my fields?

  12. guest says:

    May 16, 2013

    from http://www.sqlite.org/datatype3.html
    most used data type stored in sqlite3 is just TEXT:
    CHARACTER(20)
    VARCHAR(255)
    VARYING CHARACTER(255)
    NCHAR(55)
    NATIVE CHARACTER(70)
    NVARCHAR(100)
    TEXT
    CLOB

    DATETIME stored as TEXT if its ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).

  13. David Barton says:

    May 20, 2013

    Hi,

    Maybe I’ve missed it – but I couldn’t find an easy way to save the text of a query I right in the SQL console to a file.

    So for example. I load up the SQL Console, I write my query and execute it. I want to save it for later … Seems like a pretty standard user case, but the only way I can seem to do it at the moment is by creating a new SQL file and then copy and pasting my query into it. Any ideas or is this a missing feature?

  14. Mike Mugge says:

    September 11, 2013

    This tools loses all usefulness to me when I’m developing in a schema other than the public schema. This is because double clicking on a function to edit it will bring it up in the IJ12 editor with the hosting schema names circumcised from the DDL. That means that I have to go through the entire function, including the function name, and add back the schema qualifier. For example, if I am editing a named “bar” function in schema “foo”, IJ12 renders it as CREATE OR REPLACE FUNCTION bar ()...Obviously whats going to happen here is not what is intended as running the code will create a new function in the public schema. Choosing the “foo” schema as the Default Schema is not going to fix the issue since that for indicating the default schema to search info for code completion only, which is highly unfortunate since it’s extremely simple for, and obvious that, IJ should issue a simple SET search_path TO [default schema user selected in "Make Default"]C’mon guys.

  15. Jose Miguel Loor says:

    January 21, 2015

    If i only have the public schema in the database, the schema list is empty; because of that i cannot even use the db console; any suggestion ??

  16. Dennis says:

    November 3, 2015

    Is it me, or is the CSV view gone from IDEA 15? 🙁

  17. tai says:

    February 3, 2016

    hi how i can set up my connection before use database i need define a mysql db server but i dont know how i could please

  18. O Bloody Hell says:

    April 20, 2017

    The FIRST thing you need is to have the ULTIMATE, as opposed to the free edition.

    IDEA has no support for SQL in the community edition. :-/

    This is ridiculous. It cannot be justified to leave that large a hole in an IDE, not even for free. The learning curve on a new tool is too high to be unable to use it to perform basic actions such as DB support.

    *sigh* switching back to Eclipse. >:-(

  19. andrew says:

    April 25, 2017

    Wow, that is something I have never reallt noticed before. Good work…

  20. kurumsal bakım anlaşması says:

    October 15, 2018

    Thank You Post

Subscribe

Subscribe for updates