Easiest and most complete package for PostgreSQL right now (Feb 2022)?

General Usage
21

You can’t do that since they are not Postgres commands, strictly not “common” [to all databases]!

Those commands are implemented by psql, which is just another client (a REPL, of sorts), and it does it by translating into a proper SQL query.

A workaround is: select * from table_name limit 0;

which may give you the info you need (see link above for other ways, giving more complete info). That is standard SQL (“limit” wasn’t for a while, I believe is by now, so this trick should work on all databases up to recent standards).

A shorter version:

TABLE table_name limit 0;

TABLE is a synonym in PostgreSQL for “select * from” and needs not be upper case. I think it’s not standardized (I believe it’s taken from MySQL), so I would advice to not use in in production code, if you ever want to move to another (only) standard conforming database.

No, didn’t work in mine, and likely never will. You likely have MySQL in mind. These things are different between databases. And why I say “never” because, it means something different in PostgreSQL (I’m not sure also an extension there or my SQL standard, never used this):

That seems like an (intentional) feature of DataFrames package. So it it configurable I would look there. Note, it wouldn’t just “cut off” data, just for display purposes.

I wouldn’t know about docs of ODBC.jl (or LibPQ.jl), but LibPQ library is probably the fastest for Postgres; all (likely) use it directly or indirectly, so if you are e.g. using ODBC you would have a small overhead. The point of ODBC is to be able to switch to another database without changing your code, so it provides some abstraction. If you are just learning, I wouldn’t worry too much, and even some (time-series) databases provide the PostgreSQL API, so LibPQ.jl would still work, but that’s the exception.

What would be possible is to provide an SQL/PostgreSQL REPL mode, and if you’re lucky you would nerd-snipe someone to do it. If it’s an interesting problem enough… I’m not sure it is, but yes, would be useful (I doubt it should be part of LibPQ, because it could/should? work across databases, but LibPQ or that REPL mode could implement \d).

That’s a problem with open source (not always). Docs are best written by new users, you can help, make a PR, as they know the problems that need describing (writing docs does not qualify as nerd-sniping? so devs might overlook to do or improve them). Users like me, very familiar with Postgres, might not need any or much help. I at least used LibPQ without any problems. In that particular case, the docs might be minimal, because such users would know to look at the upstream docs, and many might rather use the alternatives, e.g. ODBC.

1 Like
22

Good idea! But ReplMaker.jl makes this so easy that it’s possible to implement it and jump off the road before the truck squashes you flat :laughing:

julia> using ReplMaker, LibPQ, DataFrames

julia> let
           conn = LibPQ.Connection("")  # Insert your connection string here
           function libpq_eval(s)
               quote
                   LibPQ.execute($conn, $s) |> DataFrame
               end
           end
           initrepl(libpq_eval,
                    prompt_text="LibPQ> ",
                    prompt_color = :blue,
                    start_key=')',
                    mode_name="LibPQ")
       end
REPL mode LibPQ initialized. Press ) to enter and backspace to exit.
"Prompt(\"LibPQ> \",...)"

LibPQ> create table foo (x text, y int);

LibPQ> insert into foo values ('hi', 1);

LibPQ> insert into foo values ('ho ho', 2);

LibPQ> select * from foo
2×2 DataFrame
 Row │ x        y      
     │ String?  Int32? 
─────┼─────────────────
   1 │ hi            1
   2 │ ho ho         2
12 Likes
23

As long as you don’t try to implement all the utility commands of psql, that is. Ugh, now I’m trying to ignore the fact this doesn’t support things like \d :sweat_smile:

1 Like
24

@Palli Thank you for the long post. I was mostly aware of the things you mentioned. I wasn’t aware that psql was a separate utility with its utility commands, but I should’ve been, hah.

I don’t know if I’d be of much help now for documentation. Shouldn’t I first know much better how things work? Maybe later I could help out somewhere.

About that "nerd-sniping’ business, I haven’t heard of that before. Now that I’ve seen the term, I translate it to sneakily getting people to do things for you. Well, I assure you I’m not here to “nerd-snipe” anyone. Just trying to get things working, is all, and looking around for the best options.

25

I’m not sure I agree. Every little bit helps. I, and hopefully you now, know these things, but then time-pressure for stuff you really should be working on, or want to work on, comes in… I didn’t mean nerd-sniping applies to docs, I don’t think it does, not nerdy enough(?), considered a boring while it IS also an important part. Also for open source. For commercial (and those terms aren’t always contradictory), that’s something people get paid to do. Nerd-sniping is about what people (well nerds) are eager to do for free, in a sense to show off. Most recent and best example I remember is the million times speedup of a Julia (wordle) program (see the recent Rust vs Julia thread). I think it’s a record for a Julia program (under discussion on Discourse), maybe for any language, I would like to know of a larger speedup, or more helpful lang community.

It seems like I already (partially) nerd-sniped someone, i.e. @c42f. Even just having an SQL REPL with only pure SQL would help, or maybe with this on psql command \d. You can ask me, I can dig up the corresponding PG-specific SQL, or actually the cross-database SQL (see information-schema). PG implemented the latter, but it wouldn’t work for as old PG, not too important, on the plus side, cross-database, in case the others do implement recent SQL standard…

26

Anyway, FYI the standard SQL way is (in case I can nerd-snipe you or someone again*):

select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';

Then I guess it’s best to get the result to a DataFrame to get it to display (same or similar code). The SQL isn’t too bad. It used to be horrible, at least if you wanted to take everything into account, \d for views not just tables etc. and you can look at the underlying view to see the gory details.

I believe this may be the first time I’ve nerd-sniped someone, or at least after I heard of the term itself (not too long ago, while I’ve seen it happen here on Discourse for years). Maybe I nerd-sniped someone before, but this would be the first time I intentionally used the term. Will it lead to a (registered) package?

Not sure how sneaky it needs to be, so not sure it applies in my case.

27

sorry about the DESCRIBE mistake, I was doing it from memory - I should have checked myself.

28

Yes, if you mean there are things in life I must do, then yes, I sure am busy. Actually, I’m probably way too busy, come to think of it, for years even. By the way, tell you the truth, I’ve hardly used LibPQ.jl up until now. I let it rest for a while, so I don’t really know much of it right now. I reverted back to psql, and was considering ODBC.jl, because of its greater generality.

Anyway, part of my plan is to find work as a data analyst (and probably some related functions) using SQL and settle with it nicely. (But I really prefer Julia.) If that’s done, I will probably have much more time and energy to be a better contributing member for this community, which I’d like, but first some things need to be taken care of.

I agree that an SQL REPL would be nice.

About that nerd-sniping business being sneaky or not, you might be right. Somehow it seemed like someone could be online and present some issue very nicely, make it interesting, with secretive motives, trying to bait the person into doing it, even taking into consideration that person’s pride or thirst for glory.

29

It seems I scared you away from LibPQ.jl (which I believe is considered solid). Note, ODBC is more general, as a concept (it should help you switch between many databases, but most likely you never will or maybe to only one other, and even then there are most often some issues with switching), but I don’t know how good ODBC.jl the wrapper is (it might well be fine). If you’re thinking of using it to make switching easier later, then there are options to it, e.g. Octo.jl has a similar goal; has Postgres/LibPQ.jl as a backend, but you’re not tied to it as it has two other databases supported. It seems intriguing, and FunSQL.jl may be similar in that way.

30

Maybe. I threw together a new package SQLREPL.jl with a significantly generalized version of my code above. I also included the \d meta-command from psql based on the query you suggested. If you’d like to contribute queries for some more meta-commands that would be great.

@ouji If you’re looking for a psql-like experience but integrated into Julia this might actually be just what you need. Do read the tutorial and try it out:

It’s not (yet) a registered package, but you can install it easily using the REPL Pkg mode:

pkg> add https://github.com/c42f/SQLREPL.jl
7 Likes
31

Wonderful, thanks!

32

@Palli Well, not really scared me away from it, hah. I just put it on hold for now, because it was slowing me down from actually using SQL. If I take the time for it… But I was in the process of thinking of switching to another package. Yeah, my point was also that ODBC is more general, which might come in handy. Octo.jl does seem nice. It will probably support more SQL flavors later on.

@c42f Well, would you look at that. Impressive and quickly done. (It seems you’ve been nerdsniped by @Palli.) Thank you for your work. Looks like my being here has at least paid off somehow for others, hah.

33

Sure :slight_smile: I hope this package is useful to you! But if not, don’t worry about https://xkcd.com/356 too much — writing a small package like this is just a bit of fun and I might use it myself instead of bare psql. It could be useful to have an SQL REPL with a full programming language easily accessible on the client side.

4 Likes
34

I like the dbcli family of tools, which has suggestions, completion, highlighting, configuration, logs, etc.

https://www.pgcli.com/docs

1 Like
35

Great!

This is the first time I’ve for sure (I think) nerd-sniped someone, meaning to make a full package, even though not yet registered. You might expect a PR:

I brought down the startup of the package a bit with -O0 only and by about 50% with:

~/julia-1.7.0/bin/julia -O0 --compile=min

The code is short, so I don’t think you’re responsible, so the PR (for startup speed) might actually go to the dependencies.

36

Any guess as to when the package would be registered?

37

If you think it’s useful already and you’d like it to be registered, let’s just register it. I doubt I’ll personally have the time to add some of the fancy features of pgcli such as syntax highlighting and tab completion. But having good interop with the Julia session (eg, sharing local variables) can already be quite useful. And the Julia REPL has top notch text-editing and pretty printing capabilities, so we get those for free.

Whether this matters depends on your use case. Perhaps the upstream libs could precompile() a few more things. But a better option might be to have a command line wrapper script. Then if you only want to use the SQL repl, start with the wrapper script for speedy startup. Otherwise, launch it via the normal Julia session.

Here’s an example of a very similar wrapper from the RemoteREPL.jl package: https://github.com/c42f/RemoteREPL.jl/blob/main/bin/julia-r

38

All going well, this will become a registered package after the standard three day waiting period is up:

https://github.com/JuliaRegistries/General/pull/55077

5 Likes
39

Can anyone in addition to @Palli comment more on ODBC.jl, especially versus any other similar options if any? How good is ODBC.jl?

40

I use ODBC.jl for work to connect to an Oracle database and it works for me without issue. But I don’t have much experience with anything else for a comparison, since I used it because when I was working on R, I also used an ODBC connection with RStudio, so that’s why I knew how to use. I mostly read data from the database and haven’t had issues.

3 Likes