There doesn't seem to be any PopSQL users around here so I'll share my experience with it.
Currently, we're a team of 5 on a paid plan and we're loving the tool. We mainly use it to:
- Share queries to extract some kind of data from our databases
- Quickly run queries to answer a quick question
- My favorite use case: We create queries around bugs we've noticed in our data. We add "TODO:" in front of the query's name. We'll then move the query to a "Done" folder once we get the expected result.
Just to make sure someone doesn't get the wrong impressions from your comment:
Redash is 99% open source (and I'm going to close this gap this month[1]), mature and actively maintained. The friendliness is subjective, but we're not trying to please everyone :-)
[1] Sometimes it's easier to prototype new things in the SaaS version, but everything reaches open source eventually. There is practically one feature that wasn't open sourced until now, and I'm going to add it to the open source version now.
We use and like redash, because we can have it trigger zapier tasks when there are new results on a query.
Makes cross-integration when testing new things great, and saves time in development being able to hack together a stop-gap solution this way.
Would people pay for a desktop tool like this? How important is sharing? I had built something a year ago (on top of PyQt) but shelved it for lack of interest.
I might if it wasn't tied to a cloud service. Sharing not that important for me unless it can be done without the need for getting a 3rd party involved.
It also seems to have some serious bugs. For example, none of their SQL Parameters examples[1] work with a Postgres connection. I just downloaded it this morning to give it a try after reading this topic.
Or, we could just go all meta, in a Postgres shell:
CREATE TABLE queries IF NOT EXISTS (
name varchar(64),
version varchar(64),
query text,
description text
);
NOTIFY chat 'Alice: Bob, please insert that query into the new queries table, and then NOTIFY "chat" with the name and version of it';
LISTEN chat;
Asynchronous notification "chat" with payload "Bob: See update-rank, v. borked-1" received from server process with PID 8448.
Asynchronous notification "chat" with payload "Bob: I have it set to update in a temp table, so we don't have to reset the real table" received from server process with PID 8448.
SELECT queries.query FROM queries WHERE name="update-rank" AND version="borked-1"
INTO query;
\echo query
-- "Looks sane, let's run it"
DO $$ EXECUTE query INTO result $$; -- Something like that, I'm guessing.
\echo result
PL/PgSQL's EXECUTE is not to be confused with the EXECUTE that is used with stored procedures.
Bonus points if you edit the query by shelling out to sed or butterflies.
PopSQL co-founder here, if you choose to share your database host, port, and name with your team, then we'll store that on our servers, but never your database username and password. If you choose to get a link to share your results, the results are uploaded from your computer directly to S3. Hope that helps!
It seems to store the database URL on the server (popsql) but the username and password are stored in the Mac Keychain, any "team members" must enter the database username and password against when attempting to use the connection.
I like the GUI, I like the landing page, I like the fact that it has native versions for the 3 main platforms... Congratulations for the team or person behind this! Great work
Postgres user here. pgadmin4 lead me to try alternative clients. Recently, I've found DBeaver useful. I'd give popsql a try if it didn't require google account information.
I want so badly to like DBeaver. I'm actively using it, but on Linux the rendering always gets screwy with me. Almost every time I start the program I've got to change themes just to get it usable.
It's fine to require people to pay for your software. I still think it's a good idea to release at least a basic demo version as open source though. With close binary blobs for all I know it could upload all my personal files or delete them for good, drop all database tables, install a backdoor, etc. I don't trust random people on the Internet to run their code on my computer.
Please add Oracle support when you have an opportunity (I was going to give it a quick try, but unfortunately it doesn't appear to have support for Oracle just yet and that's the primary database our internal systems run off of).
Being an electron app, can anyone tell me how it behaves when viewing 200, 400, records at a time? I attempted to open PopSQL but I don't really want to sign up for an account just to try the thing out.
I tried out TeamSQL a while back and it choked pretty hard at about 250.
PopSQL co-founder here. Regarding the greyed out chart, we rely on your results being in a specific format to render a chart. Check out this help article for more details on that: https://intercom.help/popsql/how-to-create-charts. We have plans for advanced charting, query parameterization, and dashboarding!
I've recently started working with a SME (<100 users) who took this approach after abandoning the vendor's reporting recommendations for their Line of Business application (apparently it was just crap).
Other areas of the business cannot be without these reports under any circumstances. Negotiation is not an option.
Since about 2012 they've had various employees generating reports, alerts and data warehouses as objects in the database. Naturally some of these have been superseded. Very often the old objects were left behind "just incase". Almost all of the original authors have left and documentation has been lost or just didn't exist in the first place.
Many of these objects are dense and difficult (because SQL was the wrong tool for the job, or because they go many layers deep like matryoshka dolls).
Demands for changes to reports are frequent (weekly), and some reports functionally overlap, but produce wildly different results for different areas of the business based on various "rules".
Honestly, the database is a fucking mess (approximately 1900 objects relating to reports, alerts and data warehouses - some of which are dolls going many many levels deep., There's a huge sense of shame and fear over trying to regain control.
I totaly understand that this was entirely a human problem. With more restraint this wouldn't have happened. Both inside and out side of the IT team. However, a tool like PopSQL, metabase, etc. I believe would've helped with;
1. Ad-hoc queries that didn't really need to be full objects
2. Discoverability through prettier annotations, etc. - reduction of reports being duplicated
3. Auditing - last access/run times, etc. would be much easier to find to reduce the cruft
4. If the tool is more appropriate it could off load some of the things that SQL is not good at
There are undeniably good reasons for storing reports, alerts and data warehouses (developer maintained objects) in version control. If the report creation/alteration requests don't come often, then I'd argue it's ideal.
However living in the real world, I personally feel it needs to be balanced in conjunction with tools like PopSQL, metabase, etc. Right tool for the right job.
I've seen this same story at every job ive ever had or contracted for. Fortune 100's down to mom and pops.
I think metabase/redash/etc is a great fit for the development of reports and the kinds of things that dont need to be anything more than a sql query. IME fast turnaround can be huge for lots of businesses.
I agree with all of your points. A lot of the problem is organizational and political and historical - but the right tool can make a big difference.
a decade ago I would have had a lot of ideas for you but as time goes on the more I have learned to embrace excel.
Theres two directions you could be coming from though - if its a problem of the users producing data and storing it in excel instead of your database thats more of an application problem.
But if its a matter of excel being where your users want to look at and work with the data - I say embrace that. Use your metabase/tableau/redash to provide them with that data and let them get it out in excel and do whatever they want. Give them a little time and then let them show you what theyre doing with it.
IME about half the time theyre actually doing things with it that you cant really help them with unless you spent a ton of time. The other half you can take what theyre doing and either apply those changes into the query itself, saving them time and sharing that work with others - or teach them a better/easier/faster way of achieving whatever it is theyre after. I use it as a long-tail way of gathering requirements a lot of times - let me give you all the data you might need for this and you figure out how it can be useful for you.
Are you really arguing that "you already have 1 deployment headache, why not add more?" One of the signs of professionalism is simplicity-- reducing moving parts and things that can go wrong.
so is your not your sql code in the same way the non sql code is ? Ignoring source control for you database is highly unprofessional and in my opinion just lazy.
Currently, we're a team of 5 on a paid plan and we're loving the tool. We mainly use it to:
- Share queries to extract some kind of data from our databases - Quickly run queries to answer a quick question - My favorite use case: We create queries around bugs we've noticed in our data. We add "TODO:" in front of the query's name. We'll then move the query to a "Done" folder once we get the expected result.