DBeaver 3.6.4
Universal Database Manager
Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MongoDB, etc.
DBeaver 3.6.4
April 11th, 2016
Brief changelist:
MySQL: database export wizard improved (+ DEFINER remove feature)
Oracle: metadata read performance improved
PostgreSQL: metadata read fixed for version before 9.x
PostgreSQL: temp/toast schemas access added
MongoDB: document update/delete fixed
SQLite: datetime columns read/update fixed
GemfireXD: driver updated
SQL keywords were updated
Misc UI bugfixes
1. General features
1.1. Connection manager
1.2. Metadata browser
1.3. SQL Editor
Scripts
Execute queries
Working with SQL
Miscellaneous
1.4. Data viewer/editor
1.5. Data/metadata search
1.6. Database structure compare
1.7. Data transfer (export/import)
1.8. ER Diagrams
1.9. Query Manager
1.10. Projects
1.11. Extra views
1.12. Driver manager
2. Database-specific features
2.1. MySQL
2.2. Oracle
2.3. DB2
2.4. SQL Server
2.5. PostgreSQL
2.6. Firebird
2.7. Vertica
2.8. Informix
2.9. WMI
2.10. MongoDB
2.11. Cassandra
3. Supported databases and platforms
3.1. Supported (tested) relational databases
3.2. Supported NoSQL databases
3.3. Supported OSes
1. General features
1.1. Connection manager
New database connection wizard can be opened by: clicking on “New Connection” the Database Navigator toolbar or by clicking on “New Connection” in main menu “Database” or by pressing CTRL+N and clicking on “Connection”.
In connection wizard you may choose appropriate database driver (or you can create a new one in driver manager).
Besides standard connection properties (like host, port, user credentials, etc) you can configure:
SSH tunnel.
SOCKS proxy.
Shell commands which will be executed before/after actual database connection.
Bootstrap SQL queries which will be executed in the database session right after connect.
DBeaver supports different connection types. Connection types differs by auto-commit mode and editors colouring. Standard connection types are “Development”, “QA” and “Production”. You can define your own connection types.
Existing connections can be copied by pressing CTRL+C + CTRL+V.
If you have big number of connections then you can organize them in folders. To create a folder choose some connection and click on “New Folder” in the Database Navigator toolbar. Then you can drag-and-drop other connections to this folder. To remove a folder just select it and press DEL. Note: connections in this folder won’t be deleted.
To open connection editor double-click on a connection or select it and press F4.
To delete a connection select it and press DEL.
1.2. Metadata browser
Database explorer view shows connections and their contents. Connection content represents database metadata structure. You can view existing tables, views, columns, indexes, procedures, triggers, storage entities (tablespaces, partitions, etc), security entities (users, roles, etc).
To open object editor double-click on object or select it and press F4.
Many metadata entities are editable. It depends on database driver – whether it supports some entity edit or not (see database-specific features below). Generally you can create/drop/modify most of metadata objects.
You can see objects’ DDL on a special object editor tab. Some drivers provide native DDL (generated by database engine) but for other drivers DBeaver generates standard SQL92 DDL according to object structure.
You can make object bookmark by selecting an object in the Database Explorer and pressing CTRL+D. Later you can open a bookmark by finding it in the Project Explorer folder “Bookmarks”.
To copy object name in the clipboard select this object and press CTRL+C. To copy fully qualified (with proceeding schema name) object name press CTRL+SHIFT+C. Also you can drag-and-drop objects to a text (or SQL) editor.
To refresh database metadata (if it was changed outside of DBeaver) select object or folder or entire connection you want to refresh and press F5. Or click on “Refresh” in context menu.
You can rename connections and database objects (if these objects can be renamed) by pressing F2 on them.
You can filter database objects by name. There are global filters (can be set in connection editor dialog) and local filters. To set filters: in Database Navigator view right click on a folder and choose “Filter …”. Then you can add include/exclude rules for underlying objects.
1.3. SQL Editor
Scripts
To open SQL editor for some connection select this connection in the Database Navigator view and press CTRL+L (you can do the same from context menu or main menu). DBeaver opens most recent (with the most recent modification time) SQL script. If you need to create new SQL script press CTRL+ALT+L.
You can see all your SQL scripts in the Project Explorer view folder “Scripts”. You can organize your scripts in folders, reassign database connections for particular scripts in the same Project Explorer view.
DBeaver uses SQL syntax highlighting which depends on database associated with a script. Different databases have different sets of reserved keywords, system functions.
Import/export scripts
Execute queries
There are a few ways to execute a query:
Standard query execute (CTRL+ENTER). Executes SQL query under cursor or selected text and fills results panel with query results.
Execute in a separate tab (CTRL+\). Similar to standard but creates new results tab.
Execute a script (ALT+X). Executes all queries in current editor (or selected queries) as a script. DBeaver parses queries one by one using statements delimiter (“;” by default) and execute them consequently. Script execution behaviour can be configured in SQL editor preferences.
Execute a script opening each query results in a separate tab (CTRL+ALT+X). The same as script execution but opens multiple result tabs. Each script query will be executed in a separate thread (i.e. all queries are executed simultaneously). Be careful with this feature – if you’ll execute a huge script with big number of queries it may lead to unexpected problems.
Execution plan. If database driver supports execution plan visualization then you can see execution plan of current query (under cursor) by pressing CTRL+SHIFT+E (or clicking on “Explain execution plan” in context menu or editor toolbar).
Select of current query row count can be performed by pressing CTRL+ALT+C.
You can directly export current query results to a file/table by clicking on “Export From Query” in the context menu. This feature is useful if you have some very long-running query and you don’t need to see it’s results in results panel.
Execution Log tab contains all queries executed in current SQL editor. You can configure Query Manager settings in global preferences.
Output tab contains all server-side database messages/warnings generated by a database when you execute queries. This feature supported only by a few database engines (Oracle, SQL Server and some other).
Working with SQL
Dynamic parameters bindings. You can use dynamic parameters in your SQL queries. Parameter format :name. When you execute a query which contains dynamic parameters DBeaver will popup dialog where you can fill parameters’ values. Also you can use anonymous parameters (?) but you will need to enable them in SQL editor preferences.
Auto-complete. To perform some object name auto-complete press CTRL+SPACE. DBeaver searches for possible completions objects in already loaded database metadata and in database system tables. Also DBeaver completes SQL keywords. Another auto-complete function is CTRL+SHIFT+SPACE, it searches for completion only within already entered identifiers.
To open current object (under cursor) definition press F4.
Hyperlinks. You can press CTRL and move mouse over SQL text. If DBEaver will recognize some identifier as a table/view name then it will be presented as a hyperlink which will navigate to an object editor.
Templates. Templates are predefined pieces of frequently used SQL code. To activate a template type its name and press TAB. Template may contain variables which will automatically filled with table/column names. You can view existing templates or declare new ones in SQL editor preferences. If you have many templates then you can use template names auto-completion (SHIFT+TABb>).
SQL formatting. To format SQL text select it and press CTRL+ALT+F.
Miscellaneous
SQL editor preferences can be opened by pressing ALT+ENTER.
To toggle (hide/show) results panel press F6.
To maximize/restore results panel double-click on results tab name.
Active database/schema selection. To change connection associated with current SQL editor or change active database/schema use combo boxes in the main toolbar.
Transactions management. You can toggle auto-commit mode and transactions isolation level in the main toolbar or main menu “Database”.
1.4. Data viewer/editor
In results viewer you can view and edit data. Results are produced by some custom SQL query in SQL editor. Or you can open Data tab in table editor to see table contents.
Record mode. To toggle record/grid mode press TAB key. In record model columns becomes rows and you see just on record. Convenient if your table has big number of columns.
Data presentations. To switch between different presentation use combo box in status bar or press CTRL+TAB.
Grid: standard presentation. Looks like a spreadsheet.
Plain-text: similar to Grid but all values are presented as plain-text (like in console applications).
JSON: represents documents in JSON format (available in EE version for document-oriented tables).
XML: represents documents in XML format (available in EE version for document-oriented tables).
Value view panel. If you work with long text data or BLOBs then it is convenient to toggle single cell value viewer/editor by pressing F7.
Value inline edit. To edit single cell value select it and press Enter. To open dialog with value editor press SHIFT+Enter.
Scrolling. If resultset has many rows then you can scroll it. By default DBeaver limits number of fetched rows by 200 (you can change this value in main toolbar or in preferences). Once you scroll to the last row of results DBeaver will fetch next segment (next 200 rows). You can disable this behaviour in preferences. Also you can manually fetch next segment or fetch whole resultset (be careful, in case of huge resultset it may lead to program hanging or out-of-memory errors).
BLOB/CLOB support. DBeaver has full support of CLOB/BLOB data types. You view values, edit them and save back to database. You can open CLOB/BLOB value in a separate editor (SHIFT+ENTER on selected cell value). You can save/load LOB value to/from regular files.
Images. DBeaver can recognize that some BLOB column keeps images (gif, png, jpeg, bmp). In that case DBeaver will show LOB contents as image. It is convenient to open value view panel (F7) and browse images.
Virtual keys. To be able to persist column value change table must have some unique key (primary key or unique index). Some databases (Oracle, DB2, PsotgreSQL) supports special virtual unique column which DBeaver can use to save changes. In other cases you can define a virtual key – a set of columns which forms unique combination of values. DBeaver asks user about virtual key definition when you try to save changes in a table without unique key.
Data filtering. You can apply custom filters to any table contents or query results. Type filter criteria in the input field above results panel. Criteria could be any SQL expression operating with any number of columns/values. Also you can apply per-field filters in filter settings dialog. Or you can make filter from cell value – choose Order/Filter in the context menu and then some field criteria.
Data ordering. You can order results by some column(s) – just click on the arrows icon after column name. By default DBeaver uses server-side ordering (i.e. query will be re-executed on server) but you can reconfigure it and make only client-side orderings.
Data search. CTRL+F shows standard search dialog. It will search only in already fetched rows. Also you can use find/replace feature.
Copy/paste. CTRL+C on selected cells will copy these cells into clipboard in TAB-delimited format. Then you can paste this selection in some spreadsheet editor (like Excel). CTRL+SHIFT+C shows “Advanced Copy” dialog where you can configure additional copy settings (copy with column names/row numbers, configure delimiter and choose values format). Pressing CTRL+V on a cell will set cell value (with appropriate data type conversion).
Data export. In the context menu you can choose “Export ResultSet” – it will show standard data transfer wizard (see below). Data will be exported with all applied filters and orderings.
SQL generation. You can generate SQL statements (SELECT/INSERT/DELETE) based on selected rows. This feature available from results panel context menu. Generated SQL will be inserted in the active SQL editor and in the clipboard.
1.5. Data/metadata search
Pressing CTRL+SHIFT+F shows global data/metadata search dialog (in Eclipse plugin version search is integrated in the standard Eclipse search dialog).
Full-text data search uses “SELECT * FROM … WHERE …” against all chosen tables/views to find data. Search results will be shown as filtered tables/views.
Metadata search tries to found rows in database system tables.
You can set an exact object name or a mask (rules are the same as for LIKE operator).
1.6. Database structure compare
After selecting multiple objects in Database Navigator you can perform objects structure compare (command available in the context menu or in main menu “Database”).
You can compare any objects – tables, schemas, entire databases, etc.
You can’t compare objects of different type (e.g. table and index).
Compare results is a report in HTML format.
1.7. Data transfer (export/import)
You can start data transfer wizard by choosing table(s) in Database Navigator and clicking on “Export Data” or “Import Data” in the context menu.
You can export data to file(s) or to another database table(s). It is possible to create a new table to export to.
Supported file formats: CSV, HTML, XML.
When you export data to another table you have to map all source table columns to target table columns. Also you have to specify target column types.
Data transfer wizard sets default target column names and data types. But sometimes you have to specify target type manually (for example if target database doesn’t support source database data type).
1.8. ER Diagrams
You can view automatically generated ER diagrams for a database/schema (diagram will contain all schema tables) or for a single table.
Single table diagrams includes all referenced/referencing tables.
It is possible to configure columns visibility (All, Keys, Primary Key)
Diagram can be exported (saved) in one of the following formats: GIF, PNG, BMP, GraphML.
It is possible to create a custom diagram (in the Project Explorer view or with CTRL+N). Custom diagram may contain any tables from any databases.
Editing custom diagrams you can create notes and table connections (relations).
1.9. Query Manager
DBeaver logs every query you execute. You can see all previously executed queries in the special view “Query Manager”. You can open it through main menu “Window”.
You can see query execution statistics (execution time, number of fetched/updated rows, errors, etc).
EE version persists all executed queries in the internal database so execution history is available after program restart.
You can configure logs visibility, monitor metadata reading queries, utility SQL, etc. QM configuration available in global preferences.
1.10. Projects
1.11. Extra views
Properties. Shows additional properties of object(s) selected in Database Navigator. May be used to view primary object properties without opening object editor.
Error Log. Shows all errors happened during you work. Includes SQL errors and internal DBeaver errors. Error Log viewer is extremely useful for bug reports.
1.12. Driver manager
You can define custom database drivers or change existing drivers using Driver Manager dialog. This dialog can be opened using main menu “Database” or from Database Navigator toolbar.
2. Database-specific features
2.1. MySQL
Enum/Set datatypes
Procedures/triggers view
Metadata DDL view
Session management
Users management
Catalogs management
Advanced metadata editor
2.2. Oracle
XML, Cursor datatypes support
Packages, procedures, triggers, indexes, tablespaces and other metadata objects browse/edit
Metadata DDL view
Session management
Users management
Advanced metadata editor
2.3. DB2
XML, Cursor datatypes support
Packages, procedures, triggers, indexes, tablespaces and other metadata objects browse/edit
Metadata DDL view
2.4. SQL Server
Views DDL.
Stored procedures source.
2.5. PostgreSQL
Execution plain explain.
Stored procedures source.
Views DDL.
Sequences.
2.6. Firebird
Views DDL.
Stored procedures source.
Sequences.
2.7. Vertica
Tables DDL.
Views DDL.
Stored procedures source.
2.8. Informix
Tables DDL.
Views DDL.
Stored procedures source.
2.9. WMI
2.10. MongoDB
2.11. Cassandra
3. Supported databases and platforms
3.1. Supported (tested) relational databases
MySQL
Oracle
PostgreSQL
IBM DB2
Microsoft SQL Server
Microsoft Access
Sybase
Java DB (Derby)
Firebird (Interbase)
Derby (JavaDB)
SQLite
Mimer
HSQLDB
H2
IBM Informix
Teradata
SAP MAX DB
Cache
Ingres
Linter
Vertica
ODBC
Any JDBC compliant data source
3.2. Supported NoSQL databases
MongoDB
Cassandra
3.3. Supported OSes
Windows (2000/XP/2003/Vista/7/8/10)
Linux
Mac OS
Solaris (x86)
http://dbeaver.jkiss.org
