Azure Data Studio: An Open-Source GUI Editor for Postgres | Hacker News Reader

Azure Data Studio: An Open-Source GUI Editor for Postgres

azure.microsoft.com

307 pointscraigkerstiens101 comments

Wow, well done Microsoft, I am looking forward to trying this out. PgAdmin is an embarrassment to the incredible Postgres community - it’s SQL editor is virtually useless, so slow and buggy. Probably because PSQL is the favoured option for most of the contributors, I’m not a huge fan of command line editing, I much prefer tools like Sublime Text, so I use Sublime Text 3 for SQL editing and it’s wonderful - blazing speed, rock solid, quite happy to display a few million rows, such queries would have no chance in Pgadmin. Also has a pg specific syntax highlighting plugin.

I'm quite happy with https://dbeaver.io/

Maybe give it a try?

Dbeaver just has one glaring issue in my mind -it has a VERY slow pace of development and issue managment. They had something like 900 issues open on their github the last time i saw it with around 200 of them having no replies at all. I was trying it out for sql server though so i don't know if it does better or not for postgres

The large number of open issues is a valid complaint although that is more of an indicator of popularity. As for the pace of development I’d challenge you to name another SQL IDE that releases updates twice a month. (Note: The exception was the last release cycle which took 3 weeks rather than 2 but that was for the 6.0 release)

When the new version of PgAdmin came out, I kept using the previous native version, much better than their UI reboot.

Pgadmin 4 is just an absolutely shocking piece of software.

Shocking, as in? Could you elaborate?

They moved from an UI written in C++ to a Python process that launches a Web based UI.

I wouldn't mind a web based UI if it didn't mean less functionality, more bugs, and a worse user experience.

pgAdmin3 doesn't connect to newer PG versions though.

MySQL and SQLite are logically the next databases for Azure Data Studio to target.

Then it will become the swiss army chainsaw for DBdevs and DBA's.

I am happy with electron apps when quality is at the level of VScode.

Buy more RAM ;)

For anyone else who's curious: yes, it's an Electron app (source at: https://github.com/Microsoft/azuredatastudio).

Think I'll stick to DataGrip personally, though I'd be interested to see some performance benchmarks to disprove my own comprehension of the quality of apps built primarily in HTML/JS/Electron.

VS Code is often very favorably remarked on for its good, consistent performance. Azure Data Studio shares a lot of dependencies/components directly with VS Code and has what appears to be a very similar build infrastructure. Microsoft seems to have very good quality in their HTML/JS/Electron apps.

Good quality Electron apps? I guess the Skype team didn't get that memo... :(

Seriously, even if performance of JS has been dragged kicking and screaming in to the 21st century now, the huge memory footprint of these abominations feels utterly wrong to me.

I've been suffering with the abomination that is pgAdmin 4 for a while now - I'd bet money that Azure Data Studio uses less memory than it.

...and yes, it phones home:

https://docs.microsoft.com/en-us/sql/azure-data-studio/usage...

God forbid they learn which features people use more and maybe improve them?

Surely you don't use gmail or any other webmail or a web service?

The Daily Telegraph once put heat monitors under everyone's desk. There was an outcry among the employees about this, and the explanation from the Telegraph was it was part of their HVAC system, and allowed the air conditioner and heater to smartly determine which parts of the building needed more heating and cooling due to more human activity going on in those parts.

Under the most charitable interpretation of the above, the Telegraph meant what it said, and only ever intended to use the data thus collected to optimize HVAC output to only those areas with enough human activity to warrant it.

But, you see, once they had the data about who was at their desk and who wasn't, it behooves them to act on it.

Except the privacy policy clearly states what Microsoft users this data for. You can go around flailing your hands in constant paranoia or you can actually give an evidence of wrongdoing and sue the shit out of them.

But why wait for evidence when posting "Micro$oft is evil" is more fun?

Also, the first thing you see when you launch for the first time is a notification about this functionality and simple opt-out instructions. I guess ideally it would be opt-in but I definitely wouldn't classify this as sinister.

VS Code seems to have driven a lot of improvement to features most important to users based on this data.

> But why wait for evidence when posting "Micro$oft is evil" is more fun?

But that’s not at all what they said. The argument they used applies to anyone collecting data. Just like in the example they used.

And while you are right about the privacy policy stating what the data is used for, what is there to prevent companies from changing their privacy policy at any point in time? And if they change it, are they required to inform you that the privacy policy has changed?

> And while you are right about the privacy policy stating what the data is used for, what is there to prevent companies from changing their privacy policy at any point in time? And if they change it, are they required to inform you that the privacy policy has changed?

For Europeans there is the GDPR.

This is also one of the things that AFAIK should be covered by the GDPR (contrary to the cookie banners with opt out that I personally expect will get their punishments soon.)

Gmail is hosted so my interactions are necessarily sent to the server. I don’t begrudge apps examining the data necessary to perform their actions. (However spying on e.g. how I move the mouse is too far.)

Desktop apps are offline and so this is just gross. Make the app good by having a vision and employing domain experts, not trying to drive button click metrics or whatever.

They do spy on how you move the mouse though. But it's all to detect "automated traffic", you see.

I have never seen this work.

The ribbon was built in large part based on telemetry. Say what you will, but it does it's job very well, provided you approach it with an open mind.

You mean later versions? Because I'm not aware they A/B tested it in the market before they launched the first one.

They did a lot of usability testing on the first iteration as well. They didn't test it in the market, since it didn't exist in a product yet, but they tested it. Jensen Harris [−π] has a large number of blog posts on the development and design history of that particular UI and a lot of information on why they have done things a certain way.

[−π]: https://blogs.msdn.microsoft.com/jensenh/2008/03/13/table-of...

The first version I can remember was amazingly bad.

I remember the file menu operations being hidden behind the round logo in the upper left corner.

I like the ribbons, at least in MS products.

Yeah, it's a tricky interface. It works best in applications with a ton of commands and it really needs a good UX team to pore over it and do user group studies. I've seen some (IMO) failed versions from smaller software vendors.

The Microsoft ones are pretty good, for me at least :)

Try out application insights before hating it.

It's a developers tool

I'm not that type of "developer". I don't do that to my users, and I wouldn't want that as a user either.

Remember, this behaviour (even if it is explicitly mentioned in the EULA) used to be called spyware and tools were created to detect and remove such things. Then the big companies caught on and spun it in the "but it's for the users!" direction, and now it's harmless? I see the "if you're using this and Azure you should trust Microsoft already" point, but this is presumably software that isn't for just interacting with Azure.

Given the sort of data that usually resides in databases, an application for interacting with them that will occasionally make connections out into the Internet just triggers suspicion regardless.

just edit your hosts file if the paranoia is this strong. no data logging for you M$!

or use something else even easier

It seems that you can opt out by adding, '"telemetry.enableTelemetry": false' to your setting files[1].

[1]https://github.com/Microsoft/azuredatastudio/wiki/How-to-Dis...

From the license, this is "shared source" not OSI open source: https://github.com/Microsoft/azuredatastudio/blob/master/LIC...

The PostgreSQL extension is fully MIT licensed: https://github.com/Microsoft/azuredatastudio-postgresql/blob... and https://github.com/Microsoft/pgtoolsservice/blob/master/Lice... for the backing service.

It looks like the VSCode extension version is missing the connection and object explorer that the standalone app contains. That's unfortunate.

A few other issues:

Creating a connection from the command palette can not use a non-default port. You'll need to edit the settings file by hand to change that.

Currently no support for SSH tunnels (unless you launch a tunnel yourself from a terminal). I've opened a GitHub issue for that support.

When running a query it keeps asking me for a username for the connection, even though the username is configured in the connection profile.

I currently use datagrip, but I'd much prefer to stay within the vscode interface rather than switching between the two, as I'm much more familiar with the vscode keyboard shortcuts and command palette. It's never really obvious which connection a datagrip "console" is attached to.

I appreciate your efforts to put down missing functionality. Products evolve over time, cant expect everything in first cut and compare it with product which is staying for quite sometime

I don't really see how people are supposed to connect to databases without SSH tunnel support. Are you supposed to be connecting to your development localhost database or be inside corporate network where direct connection to the database is allowed?

My assumption is that they were building for MSSQL Server first, where AD/Windows Authentication is still the most popular authentication scheme.

Most people use a VPN of some sort when connecting to remote databases

Our DB servers don’t have any ports listening on the internet, not even SSH.

TLS can be enforced for DB connections (both pg and mssql support this), but that’s still prone to credential stuffing and the client machine isn’t usually authenticated. Lots of regulations generally require some extra security layer protecting connections to the DB.

New to the Microsoft team I got an early look at this and was super excited. Personally I'm biased towards psql [1], but admittedly there are many that prefer a GUI interface [2] when working with their database. Postgres is a great and powerful database, but as a community we're behind in this aspect. While I don't think we need a million editors for a database that there isn't a clear winner when it comes to Postgres makes this a welcome addition and I'm excited to see how it helps make Postgres more approachable for others.

1. http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...

2. https://www.softwareandbooz.com/postgresql-for-a-sql-server-...

https://github.com/dbcli/pgcli is my favorite terminal client for postgres. It has smart completion, syntax highlighting, and more.

Quick summary of the thread for those joining us

* This looks like a neat tool. Will check this out and compare it to DataGrip, Dbeaver etc.

* It's electron. This is terrible.

* There's analytics code in there. This is terrible.

Azure Data Studio also stores your credentials in plaintext if you tick 'Remember' (on Linux at least – it does not use the system keychains)

It also supports GSSAPI out of the box, unlike java-based alternatives (at least under linux, where java doesn't support ccache other than FILE, and under macos, where java needs monkeying with java.login.config).

Please report an issue here: https://github.com/Microsoft/azuredatastudio/issues

There is one already — as a feature request to implement keychains.

The Electron-hating is getting a bit tired and predictable - yes, it's very easy to create Electron apps that perform terribly (e.g. Slack), but it's also possible to create performant, cross-platform apps.

Microsoft's VS Code is a good example - I've also been using Azure Data Studio for SQL Server for a while now, and have found it to be performant, extensible and nice to work with (it's surplanted SSMS for me). I've been hoping Microsoft would add Postgres support to Data Studio for a while, so I'm very pleased to see this.

Microsoft's VS Code is a good example, when compared against other Electron apps.

When compared against native applications, not at all.

I use it sometimes, because I have to, that is all.

> When compared against native applications, not at all

I disagree. Compared to Visual Studio, for example, VS Coe flies.

I realise they have very different feature sets though, so it might not be the best comparison - but I even switched from a native Windows text editor (Notepad2) to VS Code because startup time is almost as fast as Notepad2, and in-use it's just as performant (sometimes moreso, such as regex on a large file) but with far more features. And it's cross-platform, so I can use the same VS Code I'm used to on Windows and my MBP.

Naturally it is not the best comparison, I would be really impressed to see VS Code fly with all the capabilities that VS has to offer.

Notepad++ and Sublime are instantaneous versus VSCode.

Don't have Sublime installed, but just tested VSCode vs Notepad++ on my computer. While Notepad++ was, maybe, half a second faster opening to a empty text file than VSCode, VSCode was a couple of seconds faster opening a large SQL file.

> The Electron-hating is getting a bit tired and predictable - yes, it's very easy to create Electron apps that perform terribly (e.g. Slack), but it's also possible to create performant, cross-platform apps.

well if you know postgres, you will know that at first there was pgadmin3, which was really fast and a really good/decent application.

however somebody tought it is a good idea to redo everything and build some kind of new application, which is a client/server application and the client is actually a javascript application which is hosted in a desktop application or directly in the browser. However the desktop application is just garbage.

Yes, I have to agree with this - pgAdmin 3 was a decent tool, whereas pgAdmin 4 is... an abomination.

> There's analytics code in there. This is terrible

Sorry, this is another point I disagree with. As a developer I can appreciate the need to understand how an application is used, and to get diagnostic data when things go wrong. What is terrible is when you can't opt-out of telemetry, and when what is sent is completely opaque - that's not the case here.

Yep, electron and analytics code is a no go for me. The analytics is especially bad as it would compromise what I do at work.

Since I have to work with both postgres and ms sql on a daily basis I'm usually doing the database things in Dbeaver and move the code over to R/Python when I'm happy with the sql code.

> The analytics is especially bad as it would compromise what I do at work.

You can disable the analytics with one line of config.

Maybe. And after the next update, you'll have to make sure it's still set that way, and that they haven't added a new telemetry option that might do what it says.

I've been using VS Code, that does the same thing. From what I see the same option has been working the same way since they added it.

How does it compromise what you do at work?

This looks nice but as I expected it's built with Electron. I personally use https://tableplus.io/ . It's native and has great UI and features.

For me DataGrip. How do these 2 compare to it?

Table plus appears to me MAC only. A shame. But I’m happy with datagrip.

It is on windows too:

https://tableplus.io/windows

Oh cool. I couldn’t find that link on my phone and all I saw was OS X download and screen shots so I assumed OS X only. I’ll give it a go tomorrow. Thanks.

You can easily tell if you just give TablePlus a try with its demo.

Starts up very quickly, doesn't have bunch of options and stuff but I think it has enough to be far more intuitive. I'd say the interface is almost comparable to SequelPro just not as polished. (Font sizing, space padding and column sizing. Small stuff but it does feel awkward to me.)

I like that you can use it in its own app and also as a plugin for VSCode.

Microsoft really turned it around by supporting developers, IMO.

Took a while for me to find the VSCode extension. Here it is: https://marketplace.visualstudio.com/items?itemName=ms-ossda...

They never stopped supporting developers on Windows.

You are of course right. I was talking from my perspective. I don’t use Windows but I think Office 365 is the best deal ever, and I appreciate general tools like VSCode, keeping github on track and not changing things, etc.

The VSCode plugin supports to "Write queries with IntelliSense". This sounds fantastic. But Ctrl+Space (Trigger Suggest) does not suggest anything useful for me. In what situations does this work?

I'd say JetBrains is about to get nervous with these free offerings on top of VSCode.

I use Azure Data Studio for SQL Server already and it's fantastic. Super lightweight, fast, and a really well-thought-through interface. It's honestly the SQL administration app that I prefer out of all that I've used, free or otherwise, and I'm very happy MS are taking it to more backends.

I've never seen on HN anyone ever mentioned "SQL Workbench/J": works across many database systems, code completion, import/export, scripting, console mode, the list just goes on and on...

www.sql-workbench.eu

I am a big fan of SQL Workbench/J. I have mentioned it in a few threads[0][1] on SQL tools but have also been surprised it isn't more popular.

[0]https://news.ycombinator.com/item?id=18934949 [1]https://news.ycombinator.com/item?id=18934436

I've been using ADS for a couple months now at work (since we've switched to a new warehouse management system that uses SQL Server for its database) and it's been pretty great as a cross-platform replacement for SSMS. The built-in support for CSV and JSON dumps has been incredibly handy, too. It's both among the few Electron (I think?) apps I like and among the few Microsoft products I like.

Being able to use the same tool for Postgres would be awesome (we use both Postgres and Redshift here, too); I've long felt that SQL Server's a god-awful DB with awesome tooling while PostgreSQL's an awesome DB with god-awful tooling, and now I'm looking forward to finally getting the best of both worlds.

I wonder if it's possible to create extensions for arbitrary ODBC and/or JDBC connections? If so, then that'd be yet another thing I could manipulate with ADS (our ERP exposes its database via a presumably-proprietary DB protocol for which they provide their own ODBC and JDBC connectors). It'd be awesome to have a properly-good one-stop shop for all my SQL-finagling needs.

Now if only I could integrate all this with Emacs somehow...

EDIT: alas, it does not seem to want to install correctly on Slackware, whether via ADS' extensions manager or by manually downloading/installing the VSIX package. Seems to hang on "Starting pgsql service", and any attempt to actually do anything throws a giant "Provider doesn't exist. id: PGSQL" error (and attempting to run queries anyway just throws "Error: No Handler Registered").

I’ve been using Postico (https://eggerapps.at/postico) on OSX for the last few years and been enjoying it: clean look, ability to edit and explore schemas, ability to export results to a variety of formats (JSON, CSV, etc.), and good cadence with releases.

The main thing it can’t do well is export/import large databases but that’s what ‘pg_dump’ and friends are for.

The title was a bit misleading for me, giving me the impression that Azure Data Studio is a new tool specifically for Postgres. The reality seems to be that Azure Data Studio is an existing tool which already supports MS SQL databases, and preview support for Postgres has been added. Which makes more sense to me than Microsoft creating a new tool just for Postgres.

Looked really interesting. I'm working on a SQL-intensive project using Postgres at the moment, and a better way of editing the numerous scripts would be great.

Downloaded it, got it connected to my localhost Postgres server. Opened a script file, nice syntax highlighting, yay :)

Syntax error at or near "drop". Oh dear. Ah well, back to Code and psql it is...

Why does it have "Azure" in its name? Whoever is in charge of naming things at Microsoft is doing a poor job.

For consistent branding around all tools and technologies that could feasibly be used to build applications on top of Azure

That's still not good naming: Azure has so much services you could practically run anything on it. If keeping the Azure name to products actually running in the cloud it makes sense, otherwise it's just brand dilution.

That's not a super strong argument.. Visual Studio -> Azure Code Studio, TypeScript -> AzureScript, Windows -> Azuredows.. (Only a little tongue in cheek )

Originally it was built to manage SQL server instances on Azure.

This looks really nice, but it's functionality is similar enough to Google Data Studio (also connects to data sources) but different enough (an editor instead of a dashboard) that it's going to cause confusion.

Really like it so far.

I was a heavy pgAdmin3 user at $lastjob and the fact that clicking some elements would freeze it wasn't great. This looks to cache all details on connection.

The data view doesn't really work with very long text fields, maybe allowing users to edit those in a panel or modal could help UX.

Also, it doesn't really handles schemas, just prepend those to the table name, instead of treating it like a "folder". Schemas have permissions so we need to "see" those.

With the great hole that pgAdmin4 left, and with DataGrip being more on the heavy side, I think this app can grab a good share of users.

Good work!

My database server has 200+ databases and I had to stop using Datagrip because it would freezea the time. Super excited to check this out.

Turn off auto sync then. Sync only the portion of the tree you want synced.

I've recently been having good luck with this VS Code extension that adds a Postgres tab for exploring schemas and running queries: https://marketplace.visualstudio.com/items?itemName=ckolkman...

For vim users, there is an awesome plugin to talk to postgres and a ton of other databases called dbext.vim https://github.com/vim-scripts/dbext.vim Setting it up takes a little effort though.

I hope someday they will support PostGIS geometry viewer - like:

http://www.bostongis.com/blog/index.php?/archives/272-pgAdmi...

Does this work on local postgres servers??

Yes.

“The Postgres server can be hosted on-premises, in a virtual machine (VM), or from the managed service of any cloud provider.”

I thought DBeaver is also there!

Can this do PL/pgSQL?

Yes, you can create functions with plpgsql as the language.

Does a datpac already exist?

Data-tier Application Component Packages

Does it have the excellent perf analysis that sql server express's client comes with?

Reply on news.ycombinator.com