# Easiest and most complete package for PostgreSQL right now (Feb 2022)?
**URL:** https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920
**Category:** General Usage
**Tags:** postgresql
**Created:** [February 6, 2022, 10:04pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920 "2022-02-06T22:04:37Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 6, 2022, 10:04pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/1 "2022-02-06T22:04:37Z")
What is the most straightforward, easiest and most complete package to use for PostgreSQL?
This may pertain to documentation quality too.
More particularly, it should allow me to also parse SQL/PostgreSQL syntax embedded. For example,
```nohighlight
@sql select * from somedatabase #etc
```
When I searched this forum, there were a number of threads, and online I found information here and there, but it’s not clear to me what I should choose, keeping those criteria in mind.
I think I saw a few main options: ODBC.jl, LibPQ.jl, PostgreSQL.jl. Maybe Octo.jl. Can you clarify, keeping those criteria in mind?
---
### Author:  [@c42f](https://discourse.julialang.org/u/c42f)
#### Post date: [February 6, 2022, 11:29pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/2 "2022-02-06T23:29:00Z")
If you just need to talk to Postgres and are happy writing raw SQL code, `LibPQ.jl` is the library to use. This is fairly low-level and you’ll need to write SQL code as strings within your Julia source code. If you need to talk to many different databases, `ODBC.jl` is a good option to try.
For higher level user interfaces you’ve got several options. If you’re happy working directly with relational algebra and tabular data:
- [Octo.jl](https://github.com/wookay/Octo.jl) is a DSL for psuedo-SQL embedded within Julia code. I haven’t tried this, but the examples look nice.
- [FunSQL.jl](https://github.com/MechanicalRabbit/FunSQL.jl) is a _composable_ way of writing queries as Julia code in pipelined form. I really like this approach and syntax, but again, haven’t tried it for a serious project yet.
- If you just want to write SQL code but with safe string interpolations, my library [SQLStrings.jl](https://github.com/JuliaComputing/SQLStrings.jl) is a minimal approach to avoiding SQL string escaping problems. It can be combined with LibPQ or other SQL libraries quite easily.
If you want your database tables to be automatically mapped into Julia structs I think [SearchLight.jl](https://genieframework.com/docs/guides/Working-With-Genie-Apps.html#accessingdatabaseswithsearchlightmodels) from the Genie framework can do this. There’s probably other options too, for example using combining [StructTypes.jl](https://juliadata.github.io/StructTypes.jl/stable) together with some utility code.
Likely I’ve missed out on some tools here. Perhaps others can add extra suggestions.
---
### Author:  [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)
#### Post date: [February 6, 2022, 11:47pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/3 "2022-02-06T23:47:02Z")
I vote for FunSQL.jl + LibPQ.jl.
I use FunSQL to create queries in a generic way utilizing the package’s Julia-based DSL for writing generic and composable queries.
Then, I render the FunSQL query to correct PostgreSQL syntax.
Finally, I execute this rendered SQL using LibPQ.jl.
The documentation is top-notch, the examples there are great for learning, the project is continuing to be developed by @xitology and @cce (see them on the Julia Zulip here: [Zulip](https://julialang.zulipchat.com/#narrow/stream/284102-funsql.2Ejl)), and the underlying API is getting close to stabilizing to a 1.0 release.
I am using it for serious projects and I am beyond impressed with its flexibility and generality.
It is a key component of my workflow.
Hope that helps!
~ tcp 🌳
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 7, 2022, 9:00am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/4 "2022-02-07T09:00:09Z")
Thanks, @c42f. Very much appreciated. I had the impression LibPQ.jl did not parse SQL syntax, according to its github page. Did I read this wrongly, under their heading “Non-Goals” or did I misunderstand something? To be honest, I don’t know much about LibPQ, and LibPQ.jl doesn’t seem to have any documentation. PostgreSQL’s docs about LibPQ are not very helpful to me right now, I think.
@TheCedarPrince, thanks again. FunSQL.jl caught my eye before, and it looks very good, but I am really looking for straightforward SQL parsing as strings without too much fuss of having to install all sorts of things. Tell you the truth, I’m using raw SQL to get used to its syntax, but psql’s command line program is not very quick for me, so I was thinking to use raw SQL embedded in Julia for now.
---
### Author:  [@c42f](https://discourse.julialang.org/u/c42f)
#### Post date: [February 8, 2022, 12:25am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/5 "2022-02-08T00:25:19Z")
LibPQ itself doesn’t parse SQL syntax, but this is unnecessary because the postgres server does the parsing.
postgres gives reasonably good error messages, so this really isn’t a problem. For example:
```julia
julia> conn = LibPQ.Connection("postgres://postgres:$password@localhost:5432/postgres");
julia> LibPQ.execute(conn, "select blah where")
ERROR: SyntaxError: ERROR: syntax error at end of input
LINE 1: select blah where
^
```
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 10, 2022, 10:39pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/6 "2022-02-10T22:39:23Z")
Thank you. I might just give it a try.
What about security? Is there anything I should know about that?
---
### Author:  [@c42f](https://discourse.julialang.org/u/c42f)
#### Post date: [February 11, 2022, 1:13am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/7 "2022-02-11T01:13:04Z")
> [@Ouji](#):
>
> What about security? Is there anything I should know about that?
When using SQL as strings embedded in your program, never use string interpolations. The README in SQLStrings.jl describes why not and how to fix this:
> **[GitHub - JuliaComputing/SQLStrings.jl: Safe interpolations for SQL query strings](https://github.com/JuliaComputing/SQLStrings.jl#sqlstrings)**
>
> Safe interpolations for SQL query strings. Contribute to JuliaComputing/SQLStrings.jl development by creating an account on GitHub.
To expand the description there a bit, suppose you had the following query with unsanitized user input taken from some function `get_data_from_user()`:
```julia
name, age, class = get_data_from_user()
query = "INSERT INTO Students VALUES ('$name', $age, '$class')"
LibPQ.execute(connection, query);
```
Now suppose the user enters their name as the string
```julia
name = "Robert'); DROP TABLE Students; --"
```
along with `age = 15` and `class = "A"`
Now the query becomes (with newlines added for clarity)
```sql
INSERT INTO Students VALUES ('Robert');
DROP TABLE Students;
--', 15, 'A')
```
Hey presto! The `Students` table is now gone from the database.
`SQLStrings` exists to solve this problem by letting you write almost the exact same syntax but safely:
```julia
query = sql`INSERT INTO Students VALUES ($name, $age, $class)`
```
[Edit: Note the lack of quoting around `$name` in the `sql`...` ` version. This is easier to use and more correct.]
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [February 11, 2022, 1:41am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/8 "2022-02-11T01:41:07Z")
> [@Ouji](#):
>
> What about security? Is there anything I should know about that?
this is mostly a PostgreSQL Server Administrator task ( SSL; Grants; `scram-sha-256` ; … )
- [https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities](https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities)
I am also using some database tools for data discovery / testing connections / pre-testing SQL queries / checking table names , columns / visualisating geometries. My favorite:
[https://dbeaver.io/](https://dbeaver.io/) ( Free universal database tool and SQL client )
- open source : [GitHub - dbeaver/dbeaver: Free universal database tool and SQL client](https://github.com/dbeaver/dbeaver)
---
### Author:  [@c42f](https://discourse.julialang.org/u/c42f)
#### Post date: [February 11, 2022, 1:53am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/9 "2022-02-11T01:53:33Z")
> [@ImreSamu](#):
>
> this is mostly a PostgreSQL Server Administrator task
Yes there’s many aspects to security. It’s hard to provide firm guidance without knowing @Ouji’s use case better.
But given the nature of the questions here I assume they’re not a DB administrator, rather using the DB as a client?
From a DB client perspective
- If you’re writing an application, beware of data sanitization and SQL injection. Any of the above user-level tools should manage this for you (As appropriate for a low-level library, LibPQ expects you to manage this yourself).
- If you’re using the DB directly as an analyst and not exposing your code to input from the outside, you’ve got nothing much to worry about. (Other than the obvious - protect your password etc.)
> [@c42f](#):
>
> `SQLStrings` exists to solve this problem by letting you write almost the exact same syntax but safely
BTW, I should point out this isn’t just for security. It helps with correctness too by avoiding the need to quote the interpolations in your queries.
---
### Author:  [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)
#### Post date: [February 11, 2022, 2:00am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/10 "2022-02-11T02:00:05Z")
> [@c42f](#):
>
> Now suppose the user enters their name as the string
>
> ```julia
> name = "Robert'); DROP TABLE Students; --"
>
> ```
What a troublesome student that Bobby Tables! 😉
(For those of you who do not know the reference: [xkcd: Exploits of a Mom](https://xkcd.com/327/))
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 11, 2022, 11:56am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/11 "2022-02-11T11:56:09Z")
@c42f @ImreSamu Thank you. I am not a database administrator. My use for now is really general and mostly about just using raw SQL syntax in Julia as directly and as simply as possible, almost as though I’d be using psql directly.
The main reason for now to use Julia with SQL instead of some graphical interface or psql, is convenience. I like Julia’s terminal program much more than psql, and I intend to use Julia and SQL together at some point anyway.
@TheCedarPrince Heh. Are there any other jokes to be found like that?
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 11, 2022, 12:03pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/12 "2022-02-11T12:03:06Z")
I think I probably wouldn’t be using any string interpolations for the time being, but it’s good that you mentioned it.
I think it was you who mentioned that your SQLStrings.jl can be easily combined with LibPQ.jl. So in my case, seeing that I merely want to use raw SQL as directly and as simply as possible in Julia for now to get more used to SQL, what do you ultimately recommend? Should I just go with LibPQ.jl, and add your SQLStrings.jl later?
---
### Author:  [@c42f](https://discourse.julialang.org/u/c42f)
#### Post date: [February 11, 2022, 7:56pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/13 "2022-02-11T19:56:20Z")
> Should I just go with LibPQ.jl, and add your SQLStrings.jl later?
Sure, LibPQ is very usable by itself 👍
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 11, 2022, 8:15pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/14 "2022-02-11T20:15:28Z")
Thank you. Very much appreciated.
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 12, 2022, 4:18pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/15 "2022-02-12T16:18:47Z")
What about ODBC.jl? I went through their documentation, and it seemed simple enough to set up and use, but I fear it would be trouble. Overall, how does ODBC.jl compare to LibPQ.jl in terms of easiness, straightforwardness, and documentation?
@c42f I had some trouble with LibPQ.jl actually. Also, from what I’ve seen, there is hardly any documentation for it. I checked their docs, and I managed to finally connect. Would it be too much to ask for a simple example or examples showing how to initially connect, say, `bob` who would be a data analyst and only user?
Another thing is I don’t know how to execute the simple common commands using `\` which is straightforward in raw Postgres. (For example, `\d` to describe something.) I thought about using an escape character, but I haven’t yet come to it. So far it’s been impractical for me.
Even another thing is that if you pass a query into a `DataFrame`, the data in rows and columns (especially a `String`) is not shown fully horizontally, if long. How can I force that long data isn’t cut off, or even that it’s wrapped? Are there any handy functions already available?
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [February 12, 2022, 6:31pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/16 "2022-02-12T18:31:33Z")
> [@Ouji](#):
>
> LibPQ.jl … Also, from what I’ve seen, there is hardly any documentation for it. I checked their docs, and I managed to finally connect. Would it be too much to ask for a simple example or examples showing how to initially connect,
the [**“libpq — C Library”**](https://www.postgresql.org/docs/14/libpq.html) - has a lot of environment and connection variables And the `LibPQ.jl` only the wrapper code.
- so the environments var list : [PostgreSQL: Documentation: 14: 34.15. Environment Variables](https://www.postgresql.org/docs/14/libpq-envars.html)
So if you check the “test codes” in [/invenia/LibPQ.jl/blob/master/test/runtests.jl](https://github.com/invenia/LibPQ.jl/blob/master/test/runtests.jl)
and you will find
- `conn = LibPQ.Connection("dbname=postgres user=$DATABASE_USER")`
then you have to compare the connection values to the [`libpq-envars`](https://www.postgresql.org/docs/14/libpq-envars.html)
- `dbname=postgres`
- [www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-DBNAME](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-DBNAME)
- `user=$DATABASE_USER"`
- [www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-USER](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-USER)
probably you have to add to the connection string or to the environment variables the other important infos:
- [password](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-PASSWORD) connection parameter
- [host](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-HOST) connection parameter.
- [port](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-PORT) connection parameter.
If you have to use SSL … you have to set other SSL related info
- [sslcert](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-SSLCERT) connection parameter.
- [sslkey](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-SSLKEY) connection parameter.
- [sslrootcert](https://www.postgresql.org/docs/14/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT) connection parameter.
- …
So my best practice
- ASK the connection info from the Database Administrator
- test the connection info with `psql` command line tool - because the SSL connection has a lot of complexity - or some info from the Administrator is obsolete or wrong …
- [PostgreSQL: Documentation: 13: psql](https://www.postgresql.org/docs/13/app-psql.html)
- adapt the connection info to the Julia connection string
- `LibPQ.Connection("dbname=YY user=ZZ port=5432 )`
- ALternative : SET Environment variables : [PostgreSQL: Documentation: 14: 34.15. Environment Variables](https://www.postgresql.org/docs/14/libpq-envars.html) And `LibPQ.Connection` should use:
- `PGDATABASE` behaves the same as the dbname connection parameter.
- `PGHOST` behaves the same as the host connection parameter.
- `PGUSER` behaves the same as the user connection parameter.
- `PGPORT` behaves the same as the port connection parameter.
- …
in the LibPQ.jl FAQ doc there is a Docker based test - and you can test with your computer.
- [How do I test LibPQ.jl on my own computer?](https://invenia.github.io/LibPQ.jl/stable/pages/faq/#How-do-I-test-LibPQ.jl-on-my-own-computer?)
The postgres docker image doc is here → [Docker](https://hub.docker.com/_/postgres)
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 12, 2022, 7:09pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/17 "2022-02-12T19:09:16Z")
Thank you for your long and informative post. Very much appreciated.
Yes, I’d seen that about `dbname` and `user`, and a password was asked, and I managed to connect. I think the issues left are more of a practical kind. I asked for the example earlier, because somehow his example didn’t work at all for me.
Those environment variables you mention are pretty nice. I didn’t notice them before in the Postgres docs.
I think what’s left at least for now is those two other issues, but they probably don’t belong in this thread.
Maybe my approach to choosing to use SQL and Julia together has been wrong. Maybe I should’ve thought more about it before I went for LibPQ. So I’ll probably make a new and more general thread about that, which would also include which editor/notebook software to use.
---
### Author:  [@lawless-m](https://discourse.julialang.org/u/lawless-m)
#### Post date: [February 12, 2022, 10:11pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/18 "2022-02-12T22:11:17Z")
> [@Ouji](#):
>
> Another thing is I don’t know how to execute the simple common commands using `\` which is straightforward in raw Postgres. (For example, `\d` to describe something.)
Just like any other PostgreSQL client, you need to use the full command e.g. `DESCRIBE something;`
---
### Author:  [@domluna](https://discourse.julialang.org/u/domluna)
#### Post date: [February 13, 2022, 3:14am UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/19 "2022-02-13T03:14:38Z")
LibPQ.jl is great.
I’ve written a script that generates Julia structs that map to DB table record it’s pretty straightforward to do with just manipulating expressions once you have all the information, which types are nullable, etc. It’s for work so I can’t share it unfortunately.
FunSQL.jl looks super useful as well but I haven’t gotten to integrating it with the current setup.
---
### Author:  [@Ouji](https://discourse.julialang.org/u/Ouji)
#### Post date: [February 13, 2022, 9:49pm UTC](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920/20 "2022-02-13T21:49:50Z")
@lawless-m Thanks. I might try that.
@domluna It does seem great, yeah.
I was thinking of using ODBC.jl, because it’s more general. Thing is, I was probably “drowning” in options.
[Next page](https://discourse.julialang.org/t/easiest-and-most-complete-package-for-postgresql-right-now-feb-2022/75920.md?page=2)