Hacker News Reader

Comment by wehadfun | Hacker News Reader

I feel like Microsoft SQL server is the easiest to use and has the best tooling.

I would say that MySQL and Postgres are generally ok to use I slightly perfer MySQL tooling.

Oracle seems to be the most different one but once I got use to it it was ok.

I'm sorry but I just can't take MSSQL Server seriously when it cannot export valid CSV. It does not escape commas for CSV or tabs for TSV, and has no option to. If they had their own flat file export that could be re-imported I could forgive it because I could write my own library for that format, but there simply is no way to cleanly export data from MSSQL. I had to write a SQL query that was ~2000 lines of 80 columns in order to export a database to CSV and properly escape each field manually, and it took FOREVER.

Not sure if you're trolling here but... have you tried bcp?

"The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files."

https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Thank you, yes I did try it, bcp does not escape commas or newlines in a field.

A missing small feature here or there is kind of a lame reason to not take a large and well-respected product seriously. That said, bcp and PowerShell (piping the output of Invoke-Sqlcmd through Export-CSV) are among the more common ways that can be done with SQL Server.

If they can't get a fundamentally simple feature right, why would I give them the benefit of the doubt that other features are thought out? bcp does _not_ escape commas or newlines in a field, you have to write sql to replace() those characters on select.

Exporting data incorrectly isn't a missing small feature. It's data corruption.

> I'm sorry but I just can't take MSSQL Server seriously when it cannot export valid CSV.

“Valid CSV” is a dubious phrase, since the closest thing CSV has to a spec is an RFC that tried to map out the space of the wide variety of inolementations then existing.

Anyhow, SQL Server is a database server; there are a wide variety of ETL tools that will export from the server to any common (or not, really) format you like, including just about any flavor of CSV/TSC you might be interested in.

If you can't write data to a file, and then read it back in, it's not valid.

Not just exporting though. I've seen automated systems that attempt to import csv files directly through SQL Server, and it would always break at quoted fields and fields with newlines. And nobody could figure out why it broke all the time, and fields would be out of order or shifted or missing.

I wasn't able to convince people to fix it but I ended up writing a Go utility to reliably import/export csv into sql server using Bulk Insert for my own use (and sanity). And it ended up being faster than other methods to boot.

Compare that with creating a table right from csv file (using fdw) in postgres without importing data. Very neat

Polybase does this for SQL Server and is the recommended way to.load data into Azure SQL DW.

Looks good, setup is quite a hassle. but still not as straightforward as postgres file fdw

There is no such thing as “Valid CSV”. It’s an ambiguous format, with dozens of variants.

SQL server supports CSV with exactly the same senantics as Excel. Which is what people expect 99.9% of the time, because most CSV data goes to or from Excel in the real world.

If you’re DB-to-DB imports and exports, use a sane file format with a sane delimiter such as the Unicode INFORMATION SEPARATOR and RECORD SEPARATOR characters which were inherited from ASCII.

>>I feel like Microsoft SQL server is the easiest to use and has the best tooling.

Agreed. SQL Server Management Studio is fantastic and is one of the main reasons I enjoy working with MSSQL.

Unfortunately Postgres severely lacks in the tooling department. PgAdmin 3 used to be good, but PgAdmin 4 is simply horrendous. It makes me dread interacting with Postgres.

pgAdmin 3 still exists and afaik remains compatible with newer Postgres releases. You can still use it.

Postage, a tool developed by a family of software devs, was gaining popularity but recently became unmaintained without explanation (afaik). [0]

I started using IntelliJ DataGrip on a trial basis and it's good, but I probably won't pay for it. Sick of paying monthly subscription fee for every little tool I need from JetBrains, especially when I put down a project and don't need that tool for another x months.

Used DBeaver briefly but it's so many clicks just to set up a primary key that I shelved it for now. Will probably come back to it when DataGrip trial is over.

Not a conventional management tool but pgModeler [1] is a cool project IMO. Open-source, but they put a limit on the Windows binaries they distribute to try to get people to fund development. Can build from source yourself, install on Linux, or probably find free third-party builds elsewhere.

I think that most devs are just sticking with pgadmin3.

[0] https://github.com/workflowproducts/postage

[1] https://pgmodeler.com.br/

You can just buy jetbrains' tools, you don't get updates, but you can always use them. (Specifically, if you subscribe even once you get the latest version, and can use it forever, even if you unsubscribe)

Sad to see postage becoming unmaintained shortly after the first time I heard about it.

That said it seems there is little to gain except nice words for smaller open source efforts.

PostreSQL has great tooling, maybe not the GUI-UX experience you are familiar with MSSQL on Windows.

psql is great for inspecting schema and running ad-hoc queuries. SQL scripts can also be piped through it for cron-sql jobs etc.

pg_dump + psql + ssh to easily copy databases between hosts

pg_bench for benchmarking.

Lots more: https://www.postgresql.org/docs/current/static/reference-cli...

Zfs snapshots and send/recv are even easier for making backups or copying databases.

A filesystem snapshot is only reliable if you stop the database to do it, or if there is some sort of cooperation between the database and the tool that triggers the FS snapshot creation.

File system and LVM snapshots are atomic, so if you cannot get a working database from that your database won't survive a power failure either. You can also do a file system backup of PostgreSQL without atomic snapshots but then you will need to inform PostgreSQL of the backup and then copy files in the right order. The pg_basebackup tool and various third party tools handle this for you.

I use DataGrip if I want to use a GUI, but the vast majority of time its just so simple to work from the command line.

SSMS may well be fantastic, but as the vast majority of our dev team uses Macs, we have been converting our SS databases whenever possible to PostgreSQL over the years and have found only performance benefits, in addition to the (obvious) cost benefits.

We are (much slower than I'd like lol) going through the same migration. We've been using DataGrip for some time for both. I used to run Navicat and occasionally spin up a Windows VM for SSMS, but since moving to DataGrip, found that it does everything I need for both database engines.

SSMS is not fantastic, IMO.

JetBrains' DataGrip is significantly better, and works with all the extant SQL implementations.

We can definitely debate the SQL implementations themselves, but DataGrip makes tooling a non-issue.

I'm sure DataGrip is better for writing queries in. I really doubt it's better for managing SQL Agent jobs, doing backup/restore, or for managing users.

Does DataGrip let you do Kerberos authentication?

> Does DataGrip let you do Kerberos authentication?

It allows you to manually specify every authentication parameter if you wish to, but the simple login page only supports password or client-cert auth.

I've been using DataGrip with both SQL Server and Postgresql for over a year, and am very happy with it.

I have yet to find anything quite as nice as SSMS for Postgres, but Navicat for PostgreSQL is pretty good. It's not free, but its not expensive and has features I feel are worth paying for such as being able to diff schemas or data across DBs on different servers.

Reply on news.ycombinator.com