# Interface FunSQL with LibPQ using DBInterface
**URL:** https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635
**Category:** General Usage
**Tags:** package
**Created:** [May 30, 2023, 10:35pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635 "2023-05-30T22:35:01Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [May 30, 2023, 10:35pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/1 "2023-05-30T22:35:01Z")
I am using LibPQ.jl to interface Julia with Postgres as follows:
```julia
using FunSQL
using DBInterface
dbread = "Test2"
user = "postgres"
password = "password"
conn = LibPQ.Connection("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")
```
This is working well.
To interface with DBInterface, required for FunSQL according to the documentation, I should do something like:
```julia
DBInterface.connect(::Type{LibPQ.Connection}, "dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD") =
LibPQ.Connection("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")
ERROR: syntax: ""$("dbname=")$dbread$(" user=")$DATABASE_USER$(" password=")$DATABASE_PASSWORD"" is not a valid function argument name around REPL[43]:1
Stacktrace:
[1] top-level scope
@ REPL[43]:1
julia>
```
What am I doing wrong?
---
### Author:  [@fatteneder](https://discourse.julialang.org/u/fatteneder)
#### Post date: [May 30, 2023, 10:59pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/2 "2023-05-30T22:59:45Z")
> What am I doing wrong?
You are trying to define a function, but the function arguments you specified are wrong (`"dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD"` on the left side is neither a variable name or a type).
About the actual problem: I guess you are reading to this part of `FunSQL.jl`s docs: [Examples · FunSQL.jl](https://mechanicalrabbit.github.io/FunSQL.jl/stable/examples/#Database-connection-with-LibPQ.jl)
IIUC then what the docs are trying to tell you is is to copy this code here into your project
```julia
using LibPQ
using DBInterface
DBInterface.connect(::Type{LibPQ.Connection}, args...; kws...) =
LibPQ.Connection(args...; kws...)
DBInterface.prepare(conn::LibPQ.Connection, args...; kws...) =
LibPQ.prepare(conn, args...; kws...)
DBInterface.execute(conn::Union{LibPQ.Connection, LibPQ.Statement}, args...; kws...) =
LibPQ.execute(conn, args...; kws...)
```
and then use the `FunSQL.jl` package like it is described in the usage section, but substitute all `FunSQL.DB{LibPQ.Connection}` in all places where you would use `FunSQL.DB{SQLite.DB}` (or so).
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [May 30, 2023, 11:30pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/3 "2023-05-30T23:30:18Z")
Okay I copied the code just as you copied it from the docs. Then I put in
```julia
const conn = DBInterface.connect(FunSQL.DB{LibPQ.Connection}, "dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")
```
And lo and behold it worked!
Thanks for your help.
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [July 5, 2023, 3:10pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/4 "2023-07-05T15:10:23Z")
Now that I have the database open and can interact with it, I am having problems closing the Database. I am not sure where to go from here.
```julia
julia> close(conn)
ERROR: MethodError: no method matching close(::FunSQL.SQLConnection{LibPQ.Connection})
or
julia> DBInterface.close(conn)
ERROR: MethodError: no method matching close(::FunSQL.SQLConnection{LibPQ.Connection})
or
julia> DBInterface.close!(conn)
ERROR: MethodError: no method matching close!(::LibPQ.Connection)
```
---
### Author:  [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)
#### Post date: [July 5, 2023, 4:51pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/5 "2023-07-05T16:51:32Z")
Hey @Jake , I am about to finish adding DBInterface support to LibPQ via this PR: [https://github.com/iamed2/LibPQ.jl/pull/271](https://github.com/iamed2/LibPQ.jl/pull/271) (the original author went missing and I am in the process of rebasing and opening a new PR). If need be, I think the forked version of LibPQ with this functionality may fix your issue.
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [July 5, 2023, 8:00pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/6 "2023-07-05T20:00:31Z")
Thanks. I am not proficient with loading pull requests but was able to look over the source code of the changes made to LibPQ. I then added the new function ‘DBInterface.close!’ that was not included in the [FunSQL manual](https://mechanicalrabbit.github.io/FunSQL.jl/stable/examples/#Database-connection-with-LibPQ.jl). For reference for others looking at this my solution until the new LibPQ is released is below. This is similar to the [PR](https://github.com/chris-b1/LibPQ.jl/blob/dbinterface/src/dbinterface.jl).
```julia
using DBInterface
using FunSQL:
FunSQL, Agg, Append, As, Asc, Bind, CrossJoin, Define, Desc, Fun, From,
Get, Group, Highlight, Iterate, Join, LeftJoin, Limit, Lit, Order,
Partition, Select, Sort, Var, Where, With, WithExternal, render
using LibPQ
# bring libraries and helper functions into scope for integrating FunSQL and LibPQ
DBInterface.connect(::Type{LibPQ.Connection}, args...; kws...) =
LibPQ.Connection(args...; kws...)
DBInterface.prepare(conn::LibPQ.Connection, args...; kws...) =
LibPQ.prepare(conn, args...; kws...)
DBInterface.execute(conn::Union{LibPQ.Connection, LibPQ.Statement}, args...; kws...) =
LibPQ.execute(conn, args...; kws...)
DBInterface.close!(conn::LibPQ.Connection) = close(conn)
```
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [September 15, 2023, 9:56pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/7 "2023-09-15T21:56:37Z")
> [@TheCedarPrince](#):
>
> Hey @Jake , I am about to finish adding DBInterface support to LibPQ via this PR: [https://github.com/iamed2/LibPQ.jl/pull/271](https://github.com/iamed2/LibPQ.jl/pull/271)
@TheCedarPrice do I still need to copy the code from the FunSQL manual to bridge DBInterface with LibPQ? I note that DBInterface is now a dependency of LibPQ, but when I try to duplicate the FunSQL commands for SQLite but substitute LibPQ for SQLite, I get an error. (Edited for clarity)
```julia
julia> using LibPQ
julia> using FunSQL
julia> conn = DBInterface.connect("dbname=$dbread user=$DATABASE_USER password=$DATABASE_PASSWORD")
ERROR: UndefVarError: `DBInterface` not defined
Stacktrace:
[1] top-level scope
@ REPL[28]:1
julia>
```
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [January 14, 2025, 7:07pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/8 "2025-01-14T19:07:29Z")
The code posted [here](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/6) works fine and without error when used from the REPL.
In my use case I have a package MyPackage with the Module also called MyPackage that includes a submodule called MySubModule. MySubModule uses this code through an include statement. When I try to precompile my package it fails with the error shown below, but it executes fine. I suspect that the PR referred to by @TheCedarPrince means I don’t need all this code but have not been able to sort out what I need to do.
```julia
julia> using MyPackage
Precompiling MyPackage...
Info Given MyPackage was explicitly requested, output will be shown live
WARNING: Method definition connect(Type{LibPQ.Connection}, Any...) in module LibPQ at C:\Users\jakez\.julia\packages\LibPQ\i4yBe\src\dbinterface.jl:5 overwritten in module MySubModule at C:\Users\jakez\.julia\dev\MyPackage\src\general\FunPQ.jl:13.
ERROR: Method overwriting is not permitted during Module precompilation. Use ` __precompile__ (false)` to opt-out of precompilation.
? MyPackage
[Info: Precompiling MyPackage[8dcd7bc5-9351-4069-b6f5-927426629c70] (cache misses: include_dependency fsize change (8))
WARNING: Method definition connect(Type{LibPQ.Connection}, Any...) in module LibPQ at C:\Users\jakez\.julia\packages\LibPQ\i4yBe\src\dbinterface.jl:5 overwritten in module Mcc172Acquire at C:\Users\jakez\.julia\dev\MyPackage\src\general\FunPQ.jl:13.
ERROR: Method overwriting is not permitted during Module precompilation. Use ` __precompile__ (false)` to opt-out of precompilation.
┌ Info: Skipping precompilation due to precompilable error. Importing MyPackage [8dcd7bc5-9351-4069-b6f5-927426629c70].
└ exception = Error when precompiling module, potentially caused by a __precompile__ (false) declaration in the module.
julia>
```
When I comment out the code below the `using LibPQ` statement, MyPackage precompiles but does not execute giving this error.
```julia
julia> foo()
ERROR: MethodError: no method matching connect(::Type{LibPQ.Connection}, ::String)
The function `connect` exists, but no method is defined for this combination of argument types.
Closest candidates are:
connect(::Type{FunSQL.SQLConnection{RawConnType}}, ::Any...; schema, dialect, cache, kws...) where RawConnType
@ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:77
Stacktrace:
[1] connect(::Type{FunSQL.SQLConnection{…}}, args::String; schema::Nothing, dialect::Nothing, cache::Int64, kws::@Kwargs{})
@ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:82
[2] connect(::Type{FunSQL.SQLConnection{LibPQ.Connection}}, args::String)
@ FunSQL C:\Users\jakez\.julia\packages\FunSQL\FhUx7\src\connections.jl:77
```
The [FunSQL documentation](https://mechanicalrabbit.github.io/FunSQL.jl/stable/examples/) indicates that I still need the commands that won’t precompile.
I am not able to figure out what I should be doing to allow precompilation. Help is appreciated.
---
### Author:  [@jebej](https://discourse.julialang.org/u/jebej)
#### Post date: [January 16, 2025, 12:45am UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/9 "2025-01-16T00:45:21Z")
> [@Jake](#):
>
> `connect(Type{LibPQ.Connection}, Any...) in module LibPQ`
It looks like you are overwriting (i.e. replacing) the LibPQ function definition for `connect`, which is probably not what you want to do.
---
### Author:  [@Jake](https://discourse.julialang.org/u/Jake)
#### Post date: [January 16, 2025, 8:10pm UTC](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/10 "2025-01-16T20:10:47Z")
I am wondering if the [FunSQL documentation](https://mechanicalrabbit.github.io/FunSQL.jl/stable/examples/#Database-connection-with-LibPQ.jl) is old documentation and needs to be updated based on [the comment](https://discourse.julialang.org/t/interface-funsql-with-libpq-using-dbinterface/99635/4) from @TheCedarPrince and I should be doing something differently. But I have not been able to figure out what.