> because db isn't object oriented.

You're right. It's better. It's a relationally consistent model! :)

> u can't reuse code. searching for code is extremely limited. it is probably the worst environment to write code.

It sounds like you have a really terrible workflow and tools for doing database work. No wonder why you're angry. First, stop working directly off the database and set up some deployment/test scripts you can use from the command-line, then you can use whatever editor you like. If you're not cheap or your employer isn't cheap, use a good editor like this: https://www.jetbrains.com/datagrip/ It has nice refactoring tools and all sorts of analysis tools you can use.

I haven't used it, but Datagrip seems to have a fair amount of what you're looking for. There is a trial version, but I don't think there is a freeware version. Also, Toad Data Modeler looks like it fits the bill as well. Comes in free and paid editions. I used it several versions/years back, but haven't tried the more recent versions.

Can't speak from experience, but I've heard good things about DataGrip from JetBrains. The Features page says that meets several of the requirements listed by u/DharmaPolice:

> If the DBMS has a JDBC driver you can connect to it via DataGrip. It provides database introspection and various instruments for creating and modifying objects for the supported engines: PostgreSQL, MySQL, Oracle Database, SQL Server, Azure, Amazon Redshift, SQLite, DB2, H2, SybaseExasol, Apache Derby, MariaDB, HyperSQL, Snowflake, Cassandra, ClickHouse, Greenplum, Apache Hive, Vertica, MongoDB

It provides an object browser, data editor, diagram builder, syntax highlighting, a bunch more stuff I'm not gonna write out, and is cross-platform (Windows, Mac, Linux).

They offer a 30-day free trial, which is good cuz it's a bit pricey at $90/year for an individual subscription, or $200/person/year for organizations. In both cases, you can pay a bit more for monthly billing (1 year = 10 months), and the price drops substantially in the 2nd and 3rd years. You can also get it bundled with 14 other JetBrains products for about 3x the DG price.

If you're cool with not having the latest version forever, they offer a perpetual fallback license: once you've paid for a year, you get permanent use of the version available at the beginning of that year, including bug fixes.

If you're willing to try a different IDE, check out DataGrip. I prefer their 'intellisense' over SSMS built-in and RedGate (which I used for years) - also has code formatting, join hints, and decent dark themes (see Material UI EAP), and a ton more... if you're paying for it out of pocket (individual license) it's $89 USD, which is pretty reasonable.

Depends - we make heavy use of Python and Node.js so we also had interest in the WebStorm and PyCharm products, so for us the all products pack was a no brainer. Most of the functionality of DataGrip is also within a database plugin for PyCharm as well - so if you happen to need a Python IDE you can get the database IDE as a bit of a freebie. I think it also comes with the PHPStorm and RubyMine as well, but can't be 100% sure. It's not quite the same IDE setup as DataGrip, but the majority of the functionality is there I believe.

We also use SQL Server, MySQL, SQLite and PostgreSQL all in our environment - and having a single tool to query all of those from Windows/Mac/Linux is very much worth it for us.

There's a ton of other integrations they do within their IDEs as well, like support for Docker, remote Python, etc.

So - tl;dr, if you have an interest in their other products in any way then you'll probably find a license of some sort that is worthwhile for you. They also have discounts and freebies for certain situations.

There are a couple of GUI tools you can use https://www.jetbrains.com/datagrip/ A paid tool by jetbrains for all platforms

https://www.heidisql.com/ A Windows only tool

https://www.sequelpro.com/ A Mac only tool

A lot of these a really easy to use allowing multiple connections, tabs and live edits.

Datagrip even has live templates an autocomplete. Saves a lot of time

The four I've used have different strengths, which I'll outline:

closed source

• If you happen to already have any of the jetbrains IDEs, you probably already have all of the functionality of datagrip built-in. Which I think is the best in terms of efficient/usability in terms of things like auitocomplete and working on a bunch of queries stored in a single text file etc. It's missing a number postgres specific features, but it's good for development, especially if you're already using the IDE for your app code. Otherwise you can use the EAP version for free https://www.jetbrains.com/datagrip/nextversion/

open source

• pgAdmin is the most featureful in terms of postgres specific features, but I thought the interface in both v3 and v4 seemed pretty clunky and/or buggy. I only ever use this for dba/sysadmin type stuff where you need full coverage of all postgres features.
• dbeaver - works on lots of DBs, even sqlite.
• heidisql - by far the easiest to use, but postgres functionality is very very limited, to the point I'd only use it to run and display queries.

Unfortunately, aside from DataGrip or Navicat you're pretty stuck on Linux for SQL GUIs, they all look like the 90s (and those two are only marginally better - though I've only used Navicat on OSX, so for all I know it's trash on Linux)

How about SQuirreL?

Also also recommend DataGrip by JetBrains but it isn't free. Very sophisticated application for professional use.

SqlWorkbenchJ. It's multi db, and the maintainer is very responsive. I use it for DB2.

Jetbrains DataGrip. Made by the Resharper people.

https://www.jetbrains.com/datagrip/features/generation.html

If I understand correctly then there's not a command for exactly what you want, but you can just grab the underlying query out from inside the DDL statement that gets generated.

I don’t know if it’s available in your country but this is a feature of Datagrip. Once you connect your database, run the query you’re interested in and then you can download.

Datagrip is a paid service and other services might be free but I know this feature works because I use it.

Webstorm - Love the fact that mostly everything is built-in (jest, debugger, 3way compare...) and most plugins are officially supported (prettier, eslint), docker... and many other things you'll learn along the way. Never been disappointed.

The trick is it's very difficult to go back to vscode... I guess your colleagues will confirm.

One thing that I love too is datagrip (https://www.jetbrains.com/datagrip/). So in general for the price I take any edition like phpstorm that bundles both for a little extra. Their ultimate bundle is too expensive fwiw.

You can find all available features on our site: https://www.jetbrains.com/datagrip/features/. Everything we ship with DataGrip as a stand-alone product is available in Rider.

At my previous company we used Datagrip https://www.jetbrains.com/datagrip/download/#section=windows

At the current company we several tools, Mode Analytics is the most popular https://app.mode.com/signin

Sorry for the second reply, but I didn’t want you to miss it. JetBrains does have free licenses for a large subset of projects, they gave me a free RubyMine license a few years ago.

https://www.jetbrains.com/community/opensource/#support

And kind of buried in the pricing is the Individual Subscription price, $8.90 per month for DataGrip.

https://www.jetbrains.com/datagrip/buy/#personal?billing=monthly

Not sure why no one has recommended DataGrip yet. Very good value for money, especially if you work with multiple vendors - MySQL and Postgres, for example.

Jetbrains tools are all excellent.

If I am remembering the assignment correctly:

I just copied and pasted each table separately into Notepad++ and used regex to format the data into something useable. Then I saved all of it and moved it into queries into a SQL IDE called DataGrip. I did not bother using that garbage sql tool they want you to use, I spun up my own MySQL server locally and did it all with that.

https://www.jetbrains.com/datagrip/

From there the assignment was pretty straightforward.

It's not free but if you can get your work to swing the license fee, I highly recommend DataGrip or CLion which is great for Rust dev with IntelliJ-Rust (which can now debug on MSVC!) and comes with DataGrip basically built-in.

It's still Java but I've personally found it to be extremely reliable.

Anki also has a Debug Console if you don't fancy getting your hands dirty with a SQLite browser (If you do, I'd highly recommend DataGrip): https://www.jetbrains.com/datagrip/

Instructions for opening the console:

https://docs.ankiweb.net/#/misc?id=debug-console

list = self.col.db.list("select nid from cards group by nid having count(nid) > 1 and min(reps) = 0 and max(reps) > 0")
asString = map(lambda nid: "nid:" + str(nid), list)
pp(" ".join(asString))

('nid:1217 nid:1249 nid:1270 nid:1375 nid:1383 nid:1394 nid:1411 nid:1425 '
 'nid:1492 nid:1527 nid:1558 nid:1647 nid:1697 nid:1839 nid:1926 nid:1951 '
 'nid:1980 nid:2030 nid:2053 nid:2092 nid:2100 nid:2156 nid:2182 nid:2189 '
 'nid:2240 nid:2382 nid:2470 nid:2787 nid:2890 nid:2925 nid:2993 nid:3080 '
 'nid:3116 nid:3122 nid:3195 nid:3537 nid:3561 nid:3762 nid:3850 nid:4003 '
 'nid:4024 nid:4448 nid:4516 nid:4656 nid:4675 nid:4683 nid:4804 '
 'nid:1388810901034 nid:1391998575800 nid:1392536189078 nid:1400094543109 '
[snipped for brevity]

Do you mean Oracle's SQL Developer? That's only needed if you will be working with Oracle DBs. Oracle also owns MySQL but they are vastly different products and SQL Developer does not apply here.

MySQL has a free workbench tool that's ok though I find the UI kind of clunky. I prefer JetBrains DataGrip It's a paid product ($89 annually for individuals), but they offer it for free for students and teachers

> I still do a lot of Javascript on a daily basis

GoLand includes all the features found in WebStorm, our Web development IDE, https://www.jetbrains.com/webstorm/, and DataGrip, our database focused IDE, https://www.jetbrains.com/datagrip/.

Hopefully, that should reduce the need to switch between editing environments and improve your experience while working with a mix of technologies thanks to better integration among these.

pgadmin and phpmyadmin are both web-based; if that's what you're going for, this thing is the only one I'm aware of: https://www.phpliteadmin.org/demo/

Personally I paid for datagrip, and have been very happy with it; I use it for all my database needs, not just sqlite.

IDE's of decent quality will usually build an internal model of all of the possible tables and column combinations.

For instance I can do like SELECT u and it'll show <schema>.<table>.user in a sense dropdown (along with any other tables having that) but if in my SQL script I placed USE fooSchema; it'll constrain the potential options to just those in that schema. I can also go out of my way and do SELECT FROM user; and then hit "u" where I would put my columns and it constrains options to the defined table.

It just requires keeping an active open connection to the DB so that the metadata can be sync'd and populated.

IDEA's DataGrip https://www.jetbrains.com/datagrip/ does a decent job and has been my tool of choice for a bit.

&#x200B;

I agree though that schema and table selection should of been first; ie. FROM user SELECT name; makes more sense than SELECT name FROM user;

You can quickly check the table if you scroll to the bottom of this page (https://www.jetbrains.com/idea/)

I will go through the topics for what ultimate have that community does not:

• Perforce (you don't really need this because it's usage is mostly for legacy projects and today everyone for VCS use Git.)

• javascript, typescript (This is the support that you need when you also work on the frontend stuff, HTML, CSS, Js, Ts and also for Python and Ruby code completion and other issues that can occur for example syntax errors, runtime possible flaws, quick fixes, intelligent refactoring suggestions..)

• Java EE , Spring.. (A ton of framework support for frontend: Angular, AngularJS, React, React Native, Nodejs, Flask, Django, Spring, Spring boot, Java EE with a lot of technology specific suggestions..)

• Database Tools ( Same functionality as of https://www.jetbrains.com/datagrip/ with refactorings, support for all DBMS, Syntax highlighting)

• Code duplicate detection

• Deployment (This is the biggest reason why to use Ultimate, because community only supports Docker and Docker compose but the support for Tomcat in Java projects is crucial.)

Build Tools (Community mostly lacks the frontend build tools like Node Package manager (npm), Gulp, Grunt, Webpack.)

Never use any ide before. What is the reason to use ide like goland instead of normal vscode with the go extension? Im just wondering from the features page, it list capabilities of handling JS and DB as well. I thought jetbrain have other IDE specialized for that like datagrip and webstorm. Any significant difference when using goland to write JS and interact with DB instead of using those particular specialized IDE?

If you are asking about best one and it doesn't matter it is commercial, then I'd say the SQL support in JetBrains IDEs is one of the best. I used it as part of IntelliJ IDEA (Java IDE), if you already use JetBrain's IDE for you preferred language it's good chance it's already integrated there or there's a standalone version called DataGrip.

To point out features I like there:

• supports all DBs I needed, even the SAP/Hana (via JDBC driver),
• SQL editor supports autocomplete for SQL statments and for column/table names (very handy),
• it's fast (had seen it doing big SQL query in SAP/Hana side-by-side with DBeaver and it was notably faster),
• supports CSV export out-of-box that can be easilly configured.

On the other hand, if you don't want to spend money on it and you'll work with MySQL, I'd suggest to take look at MySQL workbench. I didn't use it as pure SQL client, however it can do it's job I'd say. The best thing about it is the ERA diagram model supprot---i.e. you can draw ERA model and get SQL generated for you (or reverse engineer existing schema back to diagram).

That came from some included plugin from JetBrains Data Grip. It isn't very user friendly for doing anything custom with the graphs but it is quick and works for in a hurry stuff - as long as your tables have all the keys and stuff in place it seems to get it right most the time.

Just write the query in Vim and run it in SSMS. That's what it's for. Or write a quick script to execute your .sql file against some test data and hit that from your console.

If you absolutely need Vim bindings inside your DB management software there's always DataGrip with IdeaVim.

We don't force you to write a plugin, of course.

If we don't support anything in PostgreSQL, any claim is fair. So, thank you for this. When we write that we support PostgreSQL, it doesn't mean that provide the full support because we need to keep up with the strongly developing DBMS, and we try to! Anyway, there are many possibilities in PostgreSQL we don't support.

Please, note, that in our What's New page we always mark features, which are specific for the particular database. If you check the current one, you'll see that all of them are related to PostgreSQL. So, we do care about PostgreSQL support and we hope we'll make our product better for you so you will be able to replace all the others.

You are using Oracle SQL Developer? The features you want to use are probably not that well implemented for PosgreSQL. You probably want to use PgAdmin III.

I use DataGrip. It works really well, but it's not free.

I support this statement (partially)! In my opinion it would be best if everyone tries to learn new stuff at the most basic level possible, like using just the CLI or just a simple text editor for your first coding projects.

But when it comes to interfaces for DBs in, my expirience as a strong visual person, it can slow your learning progress down if you cannot display the relations between tables in a graphic manner.

Therefore I would suggest that you handle all your queries in a CLI like "Sqlite.org/cli.html" but use something more advanced to get the bigger picture once in a while (try JetBrains DataGrip which has 30d trial and can connect to many RDBMS).

Try to resist the temptations of advanced tools for some time!