Golang gives the developer a very consistent interface that can be used across SQL implementations. Some details are not super straightforward, though. Here I go through the basics of connecting to a SQL database, creating a table and listing its contents.
If you’re not very into pre-made ORM (Object Relational Mapping), here you can find a configuration that allows you to connect to a Postgres Database and write your own accessor/writer methods that reads from and insert into a table.
To have it started we use Go’s database/sql package. It is the base to provide us a generic interface around SQL — while not being specific to a SQL implementor. On top of that, we need a SQL driver — in this case, a Postgres driver. For Postgres, I recommend github.com/lib/pq .
Having that, let’s jump to the code.
First we start with the imports. Aside from dabatase/sql and github.com/lib/pq that we already mentioned, it's also included go-spew and pkg/errors .
The first gives us nice pretty printing of deep structures. Essentially it takes an object and creates a nice string representation of it (very useful for debugging purposes).
The second allows us to stack errors and add context to them.
Having both spew and pkg/errors combined allows us to get all the context we need in the case of a database connection error in our New() method — connection parameters and error generated by the native database/sql package.
We define Config as the struct to hold the parameters to the database connection. This allows us to make this package very generic and connect to any postgres instance we wish (disclaimer: i wrote this for interfacing with CockroachDB — which exposes a Postgres interface — and not Postgres solely).
New() becomes then the holder of our pool of connections provided by database/sql . That is, whenever we want to interface with our Postgres instance we use Roach.Db which is then responsible for managing the connections.
It's important to note that sql.Open won't create a connection at the moment you call it (nor validate the parameters you passed to it), but only when you actually need it. As we're setting up the parameters we force a connection with db.Ping() .
Having no errors, we got our database set up for whatever we want 👌.
Now let's set up a sample table that uses Roach .
Creating a table, inserting and fetching rows
Bellow you can see how one can wrap the Roach struct implementing some methods that use the connection acquired by it to modify/insert content.
The code is highly commented so that you can grab each piece of it and use as you wish.
The code above tries to be very descriptive and really deal with some of the error handling that other posts miss.
Here are the three resources that I used to create this article / code:
- http://go-database-sql.org/ (you should really check this out)
- https://golang.org/pkg/database/sql good place to read the official
database/sqlnuances and eventually go through the code - https://github.com/lib/pq worth checking out if you wish to use the Postgres driver
- https://medium.com/@beld_pro/quick-tip-creating-a-postgresql-container-with-default-user-and-password-8bb2adb82342 Go through the process of setting a simple PostgreSQL Docker container
If you have any comments about this, please let me know! By the way, I'm super open so feel free to reach me at https://twitter.com/beld_pro .
✌️
