# What package to use for simple SQL query in different types of database

**URL:** <https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868>\
**Category:** Data\
**Tags:** mysql, postgresql\
**Created:** [March 24, 2021, 2:01pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868 "2021-03-24T14:01:37Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Laco\_Kovac](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/laco_kovac/32/20684_2.png) [@Laco\_Kovac](https://discourse.julialang.org/u/Laco_Kovac)\
**Post date:** [March 24, 2021, 2:01pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/1 "2021-03-24T14:01:37Z")

</div>

I need to execute simple SQL (e.g. SELECT \* FROM table) and fetch the returned data, but the database could be 1 of 4 types: PostgreSQL, MySQL, MariaDB and MS SQL Server.

Ideally, I would like to use a package which deal with such database types using unified interface.

I saw an example of Python code using [SQLAlchemy Python toolkit](https://www.sqlalchemy.org/) for roughly the same task.

There used to be [Julia wrapper](https://juliapackages.com/p/sqlalchemy) for that, but it seems to me that it is not maintained and doesn’t work any more. I saw also [stackoverflow topic about PyCall](https://stackoverflow.com/questions/54376411/is-there-a-straightforward-way-to-use-sqlalchemy-in-julia), but I would like to avoid that.

What simple package I could use for multi-database connection & simple SQL?

Thank you.

---

<div class="post-metadata">

**Author:** ![Skoffer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skoffer/32/378_2.png) [@Skoffer](https://discourse.julialang.org/u/Skoffer)\
**Post date:** [March 24, 2021, 2:10pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/2 "2021-03-24T14:10:12Z")

</div>

There is an [Octo.jl](https://github.com/wookay/Octo.jl) which can be useful.

Also, if all you need is to execute simple queries which are known beforehand and they do not differ between databases, you can use `DBInterface` (connections strings are fake of course, you should refer to corresponding manuals).

```julia
using ODBC
using DBInterface

pg_conn = ODBC.Connection("Driver=postgresw.so;User=abc;Password=123")
mysql_conn = ODBC.Connection("Driver=mysql.so;User=abc;Password=123")

pg_res = DBInterface.execute(pg_conn, "SELECT * FROM tbl1")
mysql_res = DBInterface.execute(mysql_conn, "SELECT * FROM tbl1")

```

If you know the resulting data structure beforehand, you can use [Strapping.jl](https://github.com/JuliaData/Strapping.jl) to convert the result to a needed structure with `Strapping.construct`.

There is no package that I am aware of, but you can easily utilize multiple dispatch to do something like this

```julia
abstract type AbstractDB end
struct Postgres{T} <: AbstractDB 
  conn::T
end
struct MySQL{T} <: AbstarctDB 
  conn::T
end

execute(db::AbstractDB, query) = DBInterface.execute(db.conn, query)

pg_db = Postgres(ODBC.Connection("Driver=postgres.so"))
my_db = MySQL(ODBC.Connection("Driver=mysql.so"))

function get_data(db)
  execute(db, "SELECT * FROM tbl1")
end

function get_data(db::Postgres)
  execute(db, "SELECT * FROM myschema.tbl2")
end

```

Of course, it’s not exactly `SQLAlchemy" because it do not generate sql queries. It’s a totally different thing, I am afraid.

---

<div class="post-metadata">

**Author:** ![TheCedarPrince](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/thecedarprince/32/17323_2.png) [@TheCedarPrince](https://discourse.julialang.org/u/TheCedarPrince)\
**Post date:** [March 24, 2021, 2:39pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/3 "2021-03-24T14:39:48Z")

</div>

I think @cce is working on a package that might work for what you are thinking of - was it DataKnots.jl Clark? I cannot recall…

---

<div class="post-metadata">

**Author:** ![Skoffer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skoffer/32/378_2.png) [@Skoffer](https://discourse.julialang.org/u/Skoffer)\
**Post date:** [March 24, 2021, 2:58pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/4 "2021-03-24T14:58:22Z")

</div>

I have found [https://github.com/MechanicalRabbit/DataKnots.jl](https://github.com/MechanicalRabbit/DataKnots.jl) and [https://github.com/MechanicalRabbit/DataKnots4FHIR.jl](https://github.com/MechanicalRabbit/DataKnots4FHIR.jl), but it looks like they do not have database functionality yet.

---

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [March 24, 2021, 3:23pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/5 "2021-03-24T15:23:26Z")

</div>

Maybe [https://github.com/GenieFramework/SearchLight.jl](https://github.com/GenieFramework/SearchLight.jl), but I have not tried it myself yet.

---

<div class="post-metadata">

**Author:** ![Skoffer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skoffer/32/378_2.png) [@Skoffer](https://discourse.julialang.org/u/Skoffer)\
**Post date:** [March 24, 2021, 3:30pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/6 "2021-03-24T15:30:52Z")

</div>

In their [docs](https://github.com/GenieFramework/SearchLight.jl/blob/master/docs/src/index.md) they say “At the moment SearchLight does not work outside Genie.”

---

<div class="post-metadata">

**Author:** ![quinnj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/quinnj/32/11_2.png) [@quinnj](https://discourse.julialang.org/u/quinnj)\
**Post date:** [March 25, 2021, 2:38pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/7 "2021-03-25T14:38:26Z")

</div>

Yeah, using DBInterface.jl is the closest thing we have right now, but it doesn’t do any translation of queries between SQL dialects. If all your queries will be SQL 92 compliant, then you should be fine; but if it’s some kind of more general query executor, I’m afraid Julia doesn’t have fancy sql dialect translation yet.

---

<div class="post-metadata">

**Author:** ![cce](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/cce/32/460_2.png) [@cce](https://discourse.julialang.org/u/cce)\
**Post date:** [March 27, 2021, 1:23pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/8 "2021-03-27T13:23:37Z")

</div>

We’re working on this currently, we’re going to call it FunSQL.

[https://github.com/MechanicalRabbit/FunSQL.jl](https://github.com/MechanicalRabbit/FunSQL.jl)

Here is a prototype cohort query against an OHDSI database…

[https://github.com/MechanicalRabbit/ohdsi-synpuf-demo/blob/master/cohorts/1770674.jl#L101](https://github.com/MechanicalRabbit/ohdsi-synpuf-demo/blob/master/cohorts/1770674.jl#L101)

This uses a prototype implementation

[https://github.com/MechanicalRabbit/FunSQL.jl/blob/prototype/src/FunSQL.jl](https://github.com/MechanicalRabbit/FunSQL.jl/blob/prototype/src/FunSQL.jl)

We’ve got the semantics the way we want. The next steps are to get a v.1 release out with bare minimal functionality (this is now master). Then we’ll get SQLite/PgSQL to work as alternative dialects (v.2). Then we’ll start to add SQL functions and their translations. Documentation will follow shortly, as will comprehensive examples using OHDSI. We’ll be spending next 6+ months making this smooth. We’ll be submitting a Julia Con talk for the SQL construction technique. It’s rather novel – it makes SQL construction modular, which makes it “fun”.

---

<div class="post-metadata">

**Author:** ![brett\_knoss](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/brett_knoss/32/13050_2.png) [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)\
**Post date:** [September 27, 2021, 3:53am UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/9 "2021-09-27T03:53:59Z")

</div>

When is it more reliable to use SQL vs DataFrames? Currently I have DataFrames nested in dictionaries, and it seems to work, but I’m not sure that this is the most efficient solution.

I also use an XLSX to save initial conditions, and to transfer those to another file.

---

<div class="post-metadata">

**Author:** ![ImreSamu](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/imresamu/32/20677_2.png) [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)\
**Post date:** [September 27, 2021, 4:47am UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/10 "2021-09-27T04:47:35Z")

</div>

> [@brett\_knoss](#):
>
> When is it more reliable to use SQL vs DataFrames?

IMHO: no silver rule.

My preference for SQL:

- **“handling data larger than fits into Client RAM”**
- I want to move the “business logic process” to the SQL server
- Complex business logic: expected to heavy performance tunning on the Server side
- Complex business logic: with some special data domain
  - [PostGis](http://postgis.net/) _“PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.”_
  - [pgRouting](https://pgrouting.org/) _“pgRouting extends the PostGIS / PostgreSQL geospatial database to provide geospatial routing functionality.”_
  - [MobilityDB](https://mobilitydb.com/) _“MobilityDB is implemented as an extension to PostgreSQL and PostGIS. It implements persistent database types, and query operations for managing geospatial trajectories and their time-varying properties.”_

If you want to use Julia on the SQL side …

- [https://gitlab.com/pljulia/pljulia](https://gitlab.com/pljulia/pljulia) “PL/Julia Procedural Language Handler for PostgreSQL”

---

<div class="post-metadata">

**Author:** ![brett\_knoss](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/brett_knoss/32/13050_2.png) [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)\
**Post date:** [September 27, 2021, 4:49am UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/11 "2021-09-27T04:49:44Z")

</div>

Could you elaborate on complex business logic?

---

<div class="post-metadata">

**Author:** ![ImreSamu](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/imresamu/32/20677_2.png) [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)\
**Post date:** [September 27, 2021, 5:37am UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/12 "2021-09-27T05:37:25Z")

</div>

> [@brett\_knoss](#):
>
> Could you elaborate on complex business logic?

IMHO:

- not a simple “Select” .. AND hard to reimplement and frequently change in “all client” languages. ( Javascript,Julia, Python) ( + testing + version controll )

### example:

this is a simple business logic ( random github search results )  
not so hard to reimplement is julia - but hard to create “Synchronized Implementation” in multiple languages ( SQL + Julia + Javascipt ) .. so the best practice … create a primary version in SQL .. and call from Julia/Python/Javascript/…

> <https://github.com/iuri/polrn/blob/e27bbbded4b663d5188ad43c32b03f37c67b77c3/packages/intranet-cost/sql/postgresql/intranet-cost-create.sql#L647-L660>

so this type of code fragments is part of the business logic

- `v_cost_type_id in (3700,3702,3730,3732)`

### example 2

IF you have to create a complex CASE/WHEN or IF/THEN/ELSE codes ..  
THEN this is probably a business logic.

> <https://github.com/iuri/polrn/blob/e27bbbded4b663d5188ad43c32b03f37c67b77c3/packages/intranet-cost/sql/postgresql/intranet-cost-create.sql#L370-L392>

---

<div class="post-metadata">

**Author:** ![ImreSamu](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/imresamu/32/20677_2.png) [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)\
**Post date:** [September 27, 2021, 5:45am UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/13 "2021-09-27T05:45:10Z")

</div>

business logic 2.

a little complex example:

a normal select but …

- with multiple `CASE WHEN`
- multiple input table ( `LEFT JOIN` , `INNER JOIN` )

[https://github.com/paulalcabasa/Oracle-SQL-Files/blob/e6d3dd27ce55179ae6834c667eebfedf9caedc50/Finance%20System/VAT%20MONITORING%20QUERY.sql#L123-L183](https://github.com/paulalcabasa/Oracle-SQL-Files/blob/e6d3dd27ce55179ae6834c667eebfedf9caedc50/Finance%20System/VAT%20MONITORING%20QUERY.sql#L123-L183)

---

<div class="post-metadata">

**Author:** ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)\
**Post date:** [June 14, 2024, 12:18pm UTC](https://discourse.julialang.org/t/what-package-to-use-for-simple-sql-query-in-different-types-of-database/57868/14 "2024-06-14T12:18:25Z")

</div>

> [@Laco\_Kovac](#):
>
> PostgreSQL, MySQL, MariaDB and MS SQL Server

I know this is a few years late, but TidierDB.jl will give you one front end for the 4 databases you mentioned in addition to Oracle, DuckDB, Clickhouse, AWS Athena, Snowflake and Google Big Query. Your query is written with Tidier.jl syntax and then converted to the appropriate sql and executed on the selected backend.