This article is the first in a series covering the use of the PostgreSQL RDBMS with C using the official libpq library. In this post I will introduce the library with a simple program which creates a database and then adds a few tables and views. In future articles I’ll cover DML or CRUD: inserting, querying, updating, deleting data, and also reading database schemas.
Target Audience
Relational database management systems and SQL are large and complex areas so I will not attempt to give an introduction to them here - this article will assume that you understand at least the basics of tables, column types, primary keys and foreign keys, as well as creating them using SQL. The code for this post assumes you have PostgreSQL installed, and ideally pgAdmin as well.
The Database Schema
For this project I will be creating a very simple database with the two tables shown in this diagram, complete with their primary key and foreign key constraints.
I will also create another table which has date and Boolean columns so that in a later post I can demonstrate inserting these data types. Finally I'll create a view which joins the two tables in the diagram to show data from each.
Generating the CREATE Statements
The chances are your development database evolved over a period of weeks or months, with lots of fiddling about using a GUI and no definitive set of SQL scripts to recreate it. Fortunately it is easy to generate scripts to create a production database using pgAdmin.
To generate a CREATE script for an object such as a table, view or the database itself, right-click the object in the tree view, mouseover Scripts and click "CREATE Script". This will show the SQL in a new window for you to copy/paste or save to a file.
You can also generate a script for all the database objects (but not the database itself) by right-clicking the database name and selecting Backup. Enter a filename and in the Format dropdown select Plain. Now click the Dump Options #1 tab and select the "Only schema" checkbox. Then click the Backup button followed by the Done button. This may take a few seconds if your database schema is large.
Installing libpq
If you use a Debian-based Linux distro you can install libpq with this command:
Installing libpq
sudo apt-get install libpq-dev
libpq Functions
The libpq library is comprehensively documented on the PostgreSQL site https://www.postgresql.org/docs/9.5/libpq.html and provides a large number of functions, constants and structs. They start with PQ or PG and as they are scattered round the code I have shown them in yellow just to make them easier to spot. My own functions start with CDPG.
The Code
The code for this project consists of the following files:
- cdpgconnection.c
- cdpgconnection.h
- cdpgddl.h
- cdpgddl.c
- ddldemo.c
You can download the source code as a zip or clone/download from Github if you prefer.
Source Code Links
Let's look at cdpgconnection.h and cdpgconnection.c first.
cdpgconnection.h
#include<libpq-fe.h> PGconn* CDPGget_connection(char* connstring); void CDPGclose_connection(PGconn*);
cdpgconnection.c
#include<libpq-fe.h> void CDPGclose_connection(PGconn* conn) { PQfinish(conn); } PGconn* CDPGget_connection(char* connstring) { PGconn *conn = PQconnectdb(connstring); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "CONNECTION_BAD %s\n", PQerrorMessage(conn)); CDPGclose_connection(conn); return NULL; } else if (PQstatus(conn) == CONNECTION_OK) { puts("CONNECTION_OK"); return conn; } }
These two functions open and close a connection for us, and are really only standins for something rather more sophisticated which you are likely to use in a real-world situation. Specifically what I am thinking of is a connection pool management system which maintains a number of open connections through the lifetime of the process, issuing free ones and then returning them as requested by the application. The reason for this is that opening a connection is resource-heavy and the connection pool pattern is therefore more efficient and widely used. For this demo though I'll stick with this simple solution.
As you can see CDPGget_connection accepts a connection string and attempts to open a PGconn using the PQconnectdb function. If the attempt is unsuccessful we output the error message retrieved using PQerrorMessage (PostgreSQL has adopted the highly innovative practice of issuing helpful and informative error messages) and return NULL. If the connection is opened successfully it is returned.
CDPGclose_connection closes the connection with PQfinish, although as I implied above it could be regarded as a placeholder for a function which returns an open connection to the pool.
Now let's move on to the heart of this project: the functions which create the database and its objects.
cdpgddl.h
#include<libpq-fe.h> void CDPGcreatedb(); void CDPGcreateDBobjects();
cdpgddl.c
#include<libpq-fe.h> #include"cdpgconnection.h" #include"cdpgddl.h" void CDPGcreatedb() { char* sql = "CREATE DATABASE codedrome"; PGconn* connpg = CDPGget_connection("user=xxx password=xxx dbname=postgres"); PGresult* res; if(connpg != NULL) { res = PQexec(connpg, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(connpg)); } else { CDPGcreateDBobjects(); } PQclear(res); CDPGclose_connection(connpg); } } void CDPGcreateDBobjects() { const char* sql[5]; sql[0] = "CREATE TABLE galleries(galleryid serial PRIMARY KEY, name varchar(64) NOT NULL, description varchar(256))"; sql[1] = "CREATE TABLE photos(photoid serial PRIMARY KEY, galleryid smallint REFERENCES galleries(galleryid) NOT NULL, title varchar(64) NOT NULL, description varchar(256) NOT NULL, photographer varchar(64) NOT NULL, datetaken date)"; sql[2] = "CREATE TABLE typesdemo(serialid serial PRIMARY KEY, intcolumn integer, realcolumn real, varcharcolumn varchar(64), datecolumn date, booleancolumn boolean)"; sql[3] = "CREATE VIEW galleriesphotos AS SELECT galleries.name AS galleryname, galleries.description AS gallerydescription, photos.title AS phototitle, photos.description AS photodescription FROM galleries LEFT JOIN photos ON photos.galleryid = galleries.galleryid"; PGconn* connpg = CDPGget_connection("user=xxx password=xxx dbname=codedrome"); PGresult* res; if(connpg != NULL) { for(int i = 0; i <= 3; i++) { res = PQexec(connpg, sql[i]); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(connpg)); break; } PQclear(res); } CDPGclose_connection(connpg); } }
The CDPGcreatedb will attempt to create an empty database and then, if successful, will call a separate function to create the tables and other objects.
Firstly we create a string containing the SQL to create a database, and then call CDPGget_connection with. There are a few of points to note about the connection string:
- You will need to edit user and password, obviously to a login with the necessary permissions.
- The connection string shown here has no host of hostaddr and will therefore try to use localhost/127.0.0.1. If you are using a database on a remote server you will need to set one of these properties. When deciding which to use you need to ask yourself "Is my sysadmin more likely to change the IP address and forget to tell me, or to forget to change the DNS settings?"
- You might also need to include port if it is not the PostgreSQL standard 5432.
- There are many more optional parameters which are listed in the documentation linked above - I have just used the minimum required to get up and running.
If we don't get a NULL connection back we can attempt to run the SQL with the connection using PQexec. This returns a pointer to a PGresult which we can check with PQresultStatus. If something went wrong we output the error message: note this "belongs" to the connection not the result. If all went well we call CDPGcreateDBobjects.
Finally we need to tidy up by calling PQclear on the result and CDPGclose_connection on the connection.
CDPGcreateDBobjects works along the same lines but as we have more than one bit of SQL to run these are added to an array which is iterated in a for loop. In a real-world situation you are unlikely to hard code SQL; you would probably read it from a file.
This time if a PQexec fails we break out of the loop. In this situation there really isn't any point pressing on trying to create subsequent objects.
That's the code finished so let's try it out.
ddldemo.c
#include<stdio.h> #include<stdlib.h> #include<stdbool.h> #include"cdpgconnection.h" #include"cdpgddl.h" int main(int argc, char* argv[]) { puts("---------------------------"); puts("| codedrome.com |"); puts("| PostgreSQL with libpq |"); puts("| DDL: Creating Databases |"); puts("---------------------------\n"); CDPGcreatedb(); return EXIT_SUCCESS; }
This is very straightforward, after the #includes and heading text we just call CDPGcreatedb.
Now we can build and run the program with this command:
Building and Running the Program
gcc ddldemo.c cdpgconnection.c cdpgddl.c -std=c11 -I/usr/include/postgresql -lpq -o ddldemo ./ddldemo
I won't bother showing the boring console output but if you open pgAdmin and expand the tree on the left you'll see the newly created database, complete with these tables, columns and constraints. In the screenshot one of the table nodes is expanded to show the 6 columns and 2 constraints, ie. the primary key and foreign key. If you scroll down a bit you will also see the view.
What's Next?
Having created the database, a few tables and a view we can then go on to insert, update and delete data, and also query the tables and view. This will be the topic of the next post.