Hello all, I have written an extremely minimal universal database API in the style of PEP249. It is so minimal, in fact, that by design you should not even have to import this package to implement it.
My goal was to provide an interface which allows you to change database back-ends by only changing your connection string and a single
import statement. The interface should be able to:
- Connect to the database using only a connection
- Execute a query from an
AbstractStringcontaining SQL code.
- Push or pull data using DataStreams.
Note that I have no intention of having any sort of SQL-like domain-specific query language.
You can find the interface here and an example implementation in JDBC here. (I’m probably still missing some important bits, but a large class of transactions should be possible with what is already here.)
Here is an example query using JDBC:
# create connection cnxn = Connection("jdbc:derby:jar:(toursdb.jar)toursdb") # create a cursor cursor(cnxn) # execute a query execute!(csr, "select * from airlines") # load into DataFrame or any other DataStreams sink df = load(DataFrame, csr) # or you can skip most of the above steps and go straight to df = load(DataFrame, cursor(cnxn), "select * from airlines")
This is of course very close to the existing database interfaces, so in most cases it will take very little work to get them to implement this.
I will have use of this for JDBC and Postgres, so I will commit to JDBC.jl and create a PostgreSQL.jl which adds this simple interface over LibPQ.jl. (I will contact the maintainers of the JuliaDatabases repositories to see if they are willing to let me use those, I am already an admin on JDBC.)
Things I’d still like to do:
- Uploading to database (using DataStreams sink objects).
- Possibly clean up or standardize object constructors so that users of the interface won’t have to change constructor code when swapping out back-ends (this is already possible, but I have to make some choices).
- I think I’d like to make a standard
tomlformat so that people could just stick a
tomlin their project with the appropriate connecton strings and instantly have an easy interface for their various databases.
- Somewhat more ambitious, automatically select a back-end based on configuration
toml. This is a little iffy because it would involve conditional loading or code-generation.
Any feedback is welcome. On the other thread there seemed to be a lot of interest in this, but I want to see if it is still there. My own situation has improved somewhat and increasingly I am only having to use Postgres, so the usefulness of this for my own personal case has diminished somewhat.