dbgate/dbgate

Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

DbGate - (no)SQL database client

DbGate is cross-platform database manager. It's designed to be simple to use and effective, when working with more databases simultaneously. But there are also many advanced features like schema compare, visual query designer, chart visualisation or batch export and import.

DbGate is licensed under MIT license and is completely free.

• Try it online - demo.dbgate.org - online demo application
• Download application for Windows, Linux or Mac from dbgate.org
• Run web version as NPM package or as docker image

Supported databases

• MySQL
• PostgreSQL
• SQL Server
• Oracle (experimental)
• MongoDB
• Redis
• SQLite
• Amazon Redshift
• CockroachDB
• MariaDB

Features

• Table data editing, with SQL change script preview
• Edit table schema, indexes, primary and foreign keys
• Compare and synchronize database structure
• ER diagram
• Light and dark theme
• Master/detail views, foreign key lookups
• Query designer
• Form view for comfortable work with tables with many columns
• JSON view on MongoDB collections
• Explore tables, views, procedures, functions, MongoDB collections
• SQL editor
  • execute SQL script
  • SQL code formatter
  • SQL code completion
  • Add SQL LEFT/INNER/RIGHT join utility
• Mongo JavaScript editor, execute Mongo script (with NodeJs syntax)
• Redis tree view, generate script from keys, run Redis script
• Runs as application for Windows, Linux and Mac. Or in Docker container on server and in web Browser on client.
• Import, export from/to CSV, Excel, JSON, NDJSON, XML
• Free table editor - quick table data editing (cleanup data after import/before export, prototype tables etc.)
• Archives - backup your data in NDJSON files on local filesystem (or on DbGate server, when using web application)
• Charts, export chart to HTML page
• For detailed info, how to run DbGate in docker container, visit docker hub
• Extensible plugin architecture
• Perspectives - nested table view over complex relational data, query designer on MongoDB databases

How to contribute

Any contributions are welcome. If you want to contribute without coding, consider following:

• Tell your friends about DbGate or share on social networks - when more people will use DbGate, it will grow to be better
• Write review on Slant.co or G2
• Create issue, if you find problem in app, or you have idea to new feature. If issue already exists, you could leave comment on it, to prioritise most wanted issues
• Create some tutorial video on youtube
• Become a backer on GitHub sponsors or Open collective
• Where a small coding is acceptable for you, you could create plugin. Plugins for new themes can be created actually without JS coding

Thank you!

Why is DbGate different

There are many database managers now, so why DbGate?

• Works everywhere - Windows, Linux, Mac, Web browser (+mobile web is planned), without compromises in features
• Based on standalone NPM packages, scripts can be run without DbGate (example - CSV export )
• Many data browsing functions based using foreign keys - master/detail, expand columns, expandable form view

Design goals

• Application simplicity - DbGate takes the best and only the best from old DbGate, DatAdmin, DbMouse and SQL Database Studio
• Minimal dependencies
  • Frontend - Svelte
  • Backend - NodeJs, ExpressJs, database connection drivers
  • JavaScript + TypeScript
  • App - electron
• Platform independent - runs as web application in single docker container on server, or as application using Electron platform on Linux, Windows and Mac

How to run development environment

Simple variant - runs WEB application:

yarn
yarn start

If you want more control, run WEB application:

yarn # install NPM packages

And than run following 3 commands concurrently in 3 terminals:

yarn start:api # run API on port 3000
yarn start:web # run web on port 5001
yarn lib # watch typescript libraries and plugins modifications

This runs API on port 3000 and web application on port 5001
Open http://localhost:5001 in your browser

If you want to run electron app:

yarn # install NPM packages
cd app
yarn # install NPM packages for electron

And than run following 3 commands concurrently in 3 terminals:

yarn start:web # run web on port 5001 (only static JS and HTML files)
yarn lib # watch typescript libraries and plugins modifications
yarn start:app # run electron app

How to run built electron app locally

This mode is very similar to production run of electron app. Electron doesn't use localhost:5001.

cd app
yarn

yarn
yarn build:app:local
yarn start:app:local

How to create plugin

Creating plugin is described in documentation

But it is very simple:

npm install -g yo # install yeoman
npm install -g generator-dbgate # install dbgate generator
cd dbgate-plugin-my-new-plugin # this directory is created by wizard, edit, what you need to change
yarn plugin # this compiles plugin and copies it into existing DbGate installation

After restarting DbGate, you could use your new plugin from DbGate.

Logging

DbGate uses pinomin logger. So by default, it produces JSON log messages into console and log files. If you want to see formatted logs, please use pino-pretty log formatter.

dbgate's Issues

Export filtered table result

Variants:

• Allow SQL condition in export configurator
• Export configurator - allow to write query (ability to select query or source tables). Impl: Source cfg - "Database - tables or views" or "Database - SQL query"

Query shows 2 rows

select OBJECT_ID('dbo.Album') - should return 1 row, shows 2 rows

Code completion improvements

• Show only, when letter is typed (not after "," , enter ...)
• Add table names to offered items
• (NTH: parse common table epxressions)

Missing rows in query result

Sometimes 1 row

select * from (
select distinct
ProtocolOffer.id_ProtocolOffer,
(SELECT SUM(OfferItem.TotalPrice) from OfferItem where OfferItem.id_Offer = Offer.id_Offer and OfferItem.IsHidden = 0) as PriceSum,
(SELECT SUM(AssignedValue._Value) from AssignedValue where AssignedValue.id_ProtocolOffer = ProtocolOffer.id_ProtocolOffer and AssignedValue.IsHidden = 0) as AssignedPriceSum
from ProtocolOffer
INNER JOIN Offer ON ProtocolOffer.id_Offer = Offer.id_Offer and Offer.IsHidden = 0
INNER JOIN AssignedValue ON ProtocolOffer.id_ProtocolOffer = AssignedValue.id_ProtocolOffer and AssignedValue.IsHidden = 0
-- group by ProtocolOffer.id_ProtocolOffer
where ProtocolOffer.IsHidden = 0
) insql
where insql.PriceSum <> insql.AssignedPriceSum and insql.AssignedPriceSum > 0

DropAll - query dismissed

after running DropAll, refreshing DB structure, query dismissed

dbgate.org

Install sample instance on dbgate.org

DataGrid: Row count is nout shown, when loaded and =0

Datagrid freeze

Open Tender on olddb (on MVPN), filter id_Tender=71643, open detail tenderround

Query session

Keep query session between queries - this doesn't work in MSSQL:

create database TEST
GO
use TEST
GO
create table TEST_TBL (col int)

TEST_TBL is created in master database

Add connection fails

Add connection ends with white page

Related table view

In the same way as in original dbgate

Create database - handle error

When I try to create database with ctx menu, I have no permission, connection is switched to error state, with error mesage "Error creating database". Show error message instead, connection should retain in OK state,

Missing scrollbar reset after refresh

Open table, scroll down, the filter few rows, , rows are not visible (try to scroll fixes it)

Add join wizard to SQL editor

Like in DatAdmin

Export query result

Preconfigured JSL source - could be directly query data file

Incorrentlty shown columns

Hide all columns + add some columns from related table

Improve MySQL support

incremental analysis
execute query
SQL objects

Simplify type system

Remove this https://github.com/dbshell/dbgate/blob/master/packages/types/dbtypes.d.ts
+remove commonType from ColumInfo

to dataType store also sizes (eg. NVARCHAR(25) ), remove length, precision, scale
instead of commonType create commonTypeFunctions - eg. isBoolean, isDate, isString, getStringLength, getPrecisionScale,

when converting table from one DB system to another, some type converters must be implemented. But this is not neccessary, could be quite simple (eg. regex or supported types, everything other is NVARCHAR(MAX))

Dark mode bugs

• JSON data viewer
• white border around textboxes
• home page on demo.dbgate.org
• view table structure page

Export import test matrix

From	To	Result
MS SQL	CSV	OK
MS SQL	JSONL	OK
MS SQL	Excel	Not implemented
MS SQL	MS SQL	OK
CSV	MS SQL	OK
JSONL	MS SQL	OK
Excel	MS SQL	OK
Postgres	CSV	OK
Postgres	JSONL	OK
MySQL	CSV	OK
MySQL	JSONL	OK
CSV	MySQL	Missing method
JSONL	MySQL	Missing method
CSV	Postgres	Missing method
JSONL	Postgres	Missing method

Filter selected data

In data grid, filter based on selected values (shortcut: Ctrl+F)

Code completion - view columns

Show view columns (in the same way as table columns)

Open referenced table in column menu

Use FK

Query loading deadlock

DB :DEV

select [Supplier].*, [contactCount], [regionName], [countryName], [contact1firstName], [contact1surname], [contact1email], [contact1id], [contact1isActive], [contact1titleAhead], [countryKey], [blockedSupplierAuthor], [blockedSupplierReason], [blockedSupplierDescription], [blockedSupplierDate], [blockedSupplierCompanyName], [LastJournalCreatedView].[createdOn], [LastJournalCreatedView].[createdBy], [LastJournalCreatedView].[createdByName], [LastJournalChangedView].[changedOn], [LastJournalChangedView].[changedBy], [LastJournalChangedView].[changedByName], [LastJournalCreditCheckUpdateView].[creditCheckUpdated], [comp_maxTenderValue].[currentPrice] as [maxTenderValue], [comp_maxTenderValue].[tenderId] as [maxTenderValueId], [comp_maxTenderValue].[divisionId] as [maxTenderDivisionId], (select count(distinct Tender.id) 
          
        from DemandedSupplier 
        inner join Tender on DemandedSupplier.tenderId = Tender.id
        inner join TenderAdditionalDataViewMaterialized on TenderAdditionalDataViewMaterialized.id = Tender.id
        
        inner join Project on Tender.projectId = Project.id
        left join DemandedSupplierRound on DemandedSupplierRound.demandedSupplierId = DemandedSupplier.id
        where DemandedSupplier.supplierId = Supplier.id
        and Tender.isDeleted = 0
        and currencyId=(SELECT id FROM Currency WHERE code='CZK')
        and exists (select * from SupplierBranch inner join DemandedBranch on SupplierBranch.branchId = DemandedBranch.branchId 
          where DemandedBranch.tenderId = Tender.id and SupplierBranch.supplierId = Supplier.id  and SupplierBranch.isApproved=1 )
         and TenderAdditionalDataViewMaterialized.createdOn >= '2018-07-01T08:52:44.889Z' and DemandedSupplierRound.offeredPrice is not null
        ) as tenderCount, (select max(Tender.realizationStart)
        
        from DemandedSupplier 
        inner join Tender on DemandedSupplier.tenderId = Tender.id
        inner join TenderAdditionalDataViewMaterialized on TenderAdditionalDataViewMaterialized.id = Tender.id
        
        inner join Project on Tender.projectId = Project.id
        left join DemandedSupplierRound on DemandedSupplierRound.demandedSupplierId = DemandedSupplier.id
        where DemandedSupplier.supplierId = Supplier.id
        and Tender.isDeleted = 0
        and currencyId=(SELECT id FROM Currency WHERE code='CZK')
        and exists (select * from SupplierBranch inner join DemandedBranch on SupplierBranch.branchId = DemandedBranch.branchId 
          where DemandedBranch.tenderId = Tender.id and SupplierBranch.supplierId = Supplier.id  and SupplierBranch.isApproved=1 )
         and TenderAdditionalDataViewMaterialized.createdOn >= '2018-07-01T08:52:44.889Z' and DemandedSupplierRound.offeredPrice is not null
        ) as tenderMaxRealizationStart, (select count(distinct Tender.id)
        
        from DemandedSupplier 
        inner join Tender on DemandedSupplier.tenderId = Tender.id
        inner join TenderAdditionalDataViewMaterialized on TenderAdditionalDataViewMaterialized.id = Tender.id
        
        inner join Project on Tender.projectId = Project.id
        left join DemandedSupplierRound on DemandedSupplierRound.demandedSupplierId = DemandedSupplier.id
        where DemandedSupplier.supplierId = Supplier.id
        and Tender.isDeleted = 0
        and currencyId=(SELECT id FROM Currency WHERE code='CZK')
        and exists (select * from SupplierBranch inner join DemandedBranch on SupplierBranch.branchId = DemandedBranch.branchId 
          where DemandedBranch.tenderId = Tender.id and SupplierBranch.supplierId = Supplier.id  and SupplierBranch.isApproved=1 )
         and TenderAdditionalDataViewMaterialized.createdOn >= '2018-07-01T08:52:44.889Z' and DemandedSupplier.currentPrice is not null and Tender.hasWinner = 1
        )
            as winTenderCount, (select max(Tender.realizationStart) 
        
        from DemandedSupplier 
        inner join Tender on DemandedSupplier.tenderId = Tender.id
        inner join TenderAdditionalDataViewMaterialized on TenderAdditionalDataViewMaterialized.id = Tender.id
        
        inner join Project on Tender.projectId = Project.id
        left join DemandedSupplierRound on DemandedSupplierRound.demandedSupplierId = DemandedSupplier.id
        where DemandedSupplier.supplierId = Supplier.id
        and Tender.isDeleted = 0
        and currencyId=(SELECT id FROM Currency WHERE code='CZK')
        and exists (select * from SupplierBranch inner join DemandedBranch on SupplierBranch.branchId = DemandedBranch.branchId 
          where DemandedBranch.tenderId = Tender.id and SupplierBranch.supplierId = Supplier.id  and SupplierBranch.isApproved=1 )
         and TenderAdditionalDataViewMaterialized.createdOn >= '2018-07-01T08:52:44.889Z' and DemandedSupplier.currentPrice is not null and Tender.hasWinner = 1
        ) as winTenderMaxRealizationStart, (select sum(value) from RealizedValueView 
         inner join SupplierBranch on RealizedValueView.supplierId = SupplierBranch.supplierId and SupplierBranch.branchId = RealizedValueView.branchId   and SupplierBranch.isApproved=1 
         where RealizedValueView.supplierId = Supplier.id and RealizedValueView.currencyCode = 'CZK' 
         and RealizedValueView.realizationDate >= '2018-07-01T08:52:44.889Z' 
          ) as sumRealizedValue, (SELECT COUNT(*) FROM SupplierBranch WHERE SupplierBranch.supplierId = Supplier.id AND SupplierBranch.isDeleted = 0   and SupplierBranch.isApproved=1 ) as branchCount, (SELECT MIN(ratingNumber) FROM SupplierBranch WHERE SupplierBranch.supplierId=Supplier.id AND SupplierBranch.ratingNumber IS NOT NULL   and SupplierBranch.isApproved=1 ) AS ratingNumber from [Supplier] left join [SupplierInfoView] on [Supplier].[id] = [SupplierInfoView].[id] left join LastJournalCreatedView on [Supplier].id=LastJournalCreatedView.refId 
      and LastJournalCreatedView.tableId=1 left join LastJournalChangedView on [Supplier].id=LastJournalChangedView.refId 
      and LastJournalChangedView.tableId=1 left join LastJournalCreditCheckUpdateView on [Supplier].id=LastJournalCreditCheckUpdateView.refId 
        and LastJournalCreditCheckUpdateView.tableId=1 outer apply (
    select top 1 TenderAdditionalDataViewMaterialized.currentPrice, Tender.id as tenderId, Project.divisionId
    from DemandedSupplier inner join Tender on DemandedSupplier.tenderId = Tender.id 
    inner join TenderAdditionalDataViewMaterialized on TenderAdditionalDataViewMaterialized.id = Tender.id
    inner join Project on Tender.projectId = Project.id
    where DemandedSupplier.supplierId = Supplier.id and TenderAdditionalDataViewMaterialized.createdOn >= '2018-07-01T08:52:44.889Z'
      and TenderAdditionalDataViewMaterialized.currentPrice is not null and TenderAdditionalDataViewMaterialized.currencyCode = 'CZK'
      
    order by TenderAdditionalDataViewMaterialized.currentPrice desc) comp_maxTenderValue
     where [Supplier].[isDeleted] <> 1 order by [ratingNumber] desc, [id] asc

Cleanup aux files

JSL data (query results)
Temporary exported files

Grid - not scrollbars not visible

Table Tender in SeVR

Set NULL error

SET NULL On start_migrace (1 row) - failed to display, failed to save

Save MySQL data

Error when saving more changes - not able to parse multiple commands separated by semicolon
Solution - separate with GO statement handled by DbGate

Detail view has not horizontal scrollbar

eg tables Tender(master, Project(detail)

Missing part of query result

run on evrapi:UAT. First result should shor lot of rows, someties there is only one or zero rows

SELECT
  Protocol.id_Protocol AS id
--   ,
--   Protocol.SelectionReason AS note,
--   Tender.id AS tenderId,
--   Protocol.CreatedBy,
--   Protocol.CreatedOn,
--   Protocol.ChangedBy,
--   Protocol.ChangedOn
FROM
  [evr_release]..Protocol
  INNER JOIN [evr_release]..TenderRound ON Protocol.id_TenderRound = TenderRound.id_TenderRound
  INNER JOIN Tender ON Tender.originalId = TenderRound.id_Tender
WHERE
  Protocol.id_Protocol IN (
    SELECT
      max(Protocol.id_Protocol) AS id
    FROM
      [evr_release]..Protocol
      INNER JOIN [evr_release]..TenderRound ON Protocol.id_TenderRound = TenderRound.id_TenderRound
    GROUP BY
      TenderRound.id_Tender
  )
  --AND Tender.id = 71643
  
  
    SELECT
      max(Protocol.id_Protocol) AS id, TenderRound.id_Tender
    FROM
      [evr_release]..Protocol
      INNER JOIN [evr_release]..TenderRound ON Protocol.id_TenderRound = TenderRound.id_TenderRound
    GROUP BY
      TenderRound.id_Tender  
      --having id_tender=71643

Run multiple statement in query window

When separated by GO, after finishing first statement is finished progress. (tested on MySQL, probably would be the same on all engines)

Improve PostgreSQL support

incremental analysis
execute query
SQL objects

Don' refresh table data, when database structure is changed

Export doesn't work with compiled electron

Bad order of query results, 3 lines instead of 2

select 'SEVR ALL' as label, Currency.Name, sum(RealizedValue._Value) from evr_release..RealizedValue
INNER JOIN evr_release..OrderItem ON RealizedValue.id_OrderItem = OrderItem.id_OrderItem
INNER JOIN evr_release..Orders ON OrderItem.id_Order = Orders.id_Order
INNER JOIN evr_release..Currency ON Orders.id_Currency = Currency.id_Currency
group by Currency.Name

go

select 'NEVR ALL' as label, Currency.name, sum(RealizedValue.[value]) from RealizedValue
INNER JOIN Currency ON RealizedValue.currencyId = Currency.id
group by Currency.name

go

select 'SEVR INC' as label, Currency.Name, sum(RealizedValue._Value) from evr_release..RealizedValue
INNER JOIN evr_release..OrderItem ON RealizedValue.id_OrderItem = OrderItem.id_OrderItem
INNER JOIN evr_release..Orders ON OrderItem.id_Order = Orders.id_Order
INNER JOIN evr_release..Currency ON Orders.id_Currency = Currency.id_Currency
where not exists(select * from RealizedValue new where new.originalId = RealizedValue.id_realizedValue)
group by Currency.Name

go

select 'NEVR INC' as label, Currency.name, sum(RealizedValue.[value]) from RealizedValue
INNER JOIN Currency ON RealizedValue.currencyId = Currency.id
where RealizedValue.originalId is null
group by Currency.name

Context menu on tabs

Close, Close all, Close all but this etc

Swap axes data view

Implement without infinite loading, only for small number of rows

Arrow navigation between filters and table data

Recently closed tabs

Instead of opened tabs widget
Add to this list, when closing tab
Remove from this list after some period (1 week?). Also remove queries from local storage after one week

Transform column names when importing CSV=>DB

Some column name transform will be necessary

Now there are 2 problems:

• limit of column name length (63 postgres, 64, mysql+mssql)
• trim spaces from column names

To be specified, how this change could be implemented. Some posibilties:

• cleanup column names in source
• TableWriter creates some transform stream doing this
• in DB layer

probably transform stream will be the best (and without changing plugins)

Master/detail error

OPen Tender in evr_release, filter id_Tender=764, view detail TenderRound (from id_LastTenderRound), then close detail, open detail TenderRound (from depended tables), there is only one row in detail, should be 3 (remains filter from first view, after removing filter maually is OK)

Show error of database connection

Show in tooltip in statusbar and in Tables widget, instead of empty list

Master/detail error

SupplierContact, show detail Supplier - incrorrect scroll sizes

Ensure ctx menu in viewport

Missing scrollbars in teble info view

CTX menu Table/Open structure
When there is lot of columns, foreign keys are not visible

Filter parser - logical values

allow 1 (synonym for TRUE), 0 (synonym for FALSE)

Add error boundaries

https://reactjs.org/docs/error-boundaries.html

• tab content
• widget content

Connection to MSSQL using NTLM

Is supported by tedious

Incorrect info about result rows

Result rows are loaded correctly, only info about result rows is not updated correctly

evrapi_PROD

SELECT
  [dbo].[Supplier].[id],
  [dbo].[Supplier].[name],
  [dbo].[Supplier].[dunsNumber],
  [dbo].[Country].[key] as countryKey,
  MAX([dbo].[Journal].[date]) as updatedCreditCheck
FROM
  [dbo].[Supplier]
  INNER JOIN [dbo].[Country] ON [dbo].[Supplier].[countryId] = [dbo].[Country].[id]
  INNER JOIN [dbo].[Journal] ON [dbo].[Supplier].[id] = [dbo].[Journal].[refId]
WHERE
  ([dbo].[Journal].[tableId] = 1)
  AND ([dbo].[Journal].[methodId] = 5)
  AND ([dbo].[Supplier].[isDeleted] = 0)
GROUP BY 
  [dbo].[Supplier].[id],
  [dbo].[Supplier].[name],
  [dbo].[Supplier].[dunsNumber],
  [dbo].[Country].[key]

Cannot insert from clipboard into empty table

Try copy a few lines from table migrace_projektu