Ruby Dive in the C: Make a Ruby-Pg lite extension
Tags: ruby, postgres, database
Published: 2023-11-06 06:14:14

In this article, I wanted to do two things. First, understand how the ruby-pg gem works and then how to create a C extension.

We will walk through both in this article.

## Create the C extension

There is several blog post on how to create a C extension, for example, https://www.rubyguides.com/2018/03/write-ruby-c-extension/

But Basically, you have to create an `extconf.rb` file like this : 

```ruby
require 'mkmf'

create_header
create_makefile 'pgext'
```

Then run it with ruby : `ruby extconf.rb`.

This will create a makefile, and then you can create a C file called exactly pgext.c because the Makefile has been created for it.

Do not forget to make your libpq library available for your libraries for example in your Makefile. In your `CPPFLAGS` add `-I/opt/homebrew/opt/libpq/include` and in your `dldflags` add `-L/opt/homebrew/opt/libpq/lib`.

## LibPQ

LibPQ is the C library that abstracts everything and enables it to connect to a Postgres database. There is a lot of function and data structure in this lib. 

But we will focus on the main one to be able to connect and execute a query. And eventually, print the result of our query. 

## Connect to Postgres

First, we must create a data structure that will encapsulate our C connection for the Ruby World.

For that, we need to wrap the PGConn data structure of LibPQ and then create a pg_connection_type that will hold this PGConn and make the connection accessible in the Ruby World.

```C
typedef struct {
  PGconn *pgconn;
} t_pg_connection;

static const rb_data_type_t pg_connection_type = {
	.wrap_struct_name ="MyPG::Conn",
	.function = {
		.dmark = NULL,
		.dfree = RUBY_DEFAULT_FREE,
		.dsize = conn_size,
	},
	.data = NULL,
	.flags = RUBY_TYPED_FREE_IMMEDIATELY,
};
```

Now, we can define a `pg_conn` function to get the connection info as an argument. This function will return a VALUE pointer to `MyPG::Conn`. 

To get a VALUE pointer, we use the `TypedData_Make_Struct` function. This function will allocate the memory necessary to hold our connection and link the t_pg_connection to the pg_connection_type. Eventually, it will give you back the VALUE pointer to your data structure, which is accessible in the Ruby world.

After that, we only have to use the `PQconnectdb` function with the connection information, and our connection will be established.

```C
static VALUE
pg_conn(int argc, VALUE *argv, VALUE klass) {
  t_pg_connection *this;
  const char* conninfo = argv[0];
  VALUE self = TypedData_Make_Struct(klass, 
                                     t_pg_connection, 
                                     &pg_connection_type,
                                     this );
  this->pgconn = PQconnectdb(conninfo);
  return self;
}
```

That's cool, but having a connection without being able to execute queries is not very useful, right? 

## Executing Queries

As before, we must define our data structure and everything accessible in the Ruby World. We also need to declare a VALUE pointer, but that's a bit of a foreshadowing; let's discuss that later.

```C
VALUE my_rb_pg_result;

typedef struct { 
	PGresult *pgresult;
} t_pg_result;

static const rb_data_type_t pg_result_type = {
	.wrap_struct_name ="MyPG::Result",
	.function = {
		.dmark = NULL,
		.dfree = RUBY_DEFAULT_FREE,
		.dsize = res_size,
	},
	.data = NULL,
	.flags = RUBY_TYPED_FREE_IMMEDIATELY,
};
```

So before executing your query, you have to get the connection to the database you made earlier. Thanks to this function: `TypedData_Get_Struct`, the klass is still `MyPG::Conn`, so we can pass klass to the connection data.

Then, we have to allocate memory for the result of the query. We use the same function as before, but now, we can't use `klass` as the first argument since this is not the type of Ruby Object. 

We have to pass: `my_rb_pg_result`. Indeed, you need the type to match your class, and my_rb_pg_result will represent `MyPG::Result`. 

After that, execute the query upon the current connection. The query will be the first argument of the function that we define. We need to parse it as a string C string object before to use it.

Then you can execute your query with `PQexec` of LibPQ with the current connection and store it in the PGResult data structure which is encapsulate in our own C structure.

```C
static VALUE
pg_exec(int argc, VALUE *argv, VALUE klass)
{
  t_pg_connection *this;
  t_pg_result *result;

  TypedData_Get_Struct(klass, 
                       t_pg_connection, 
                       &pg_connection_type,
                       this);

  const char *query = StringValueCStr(argv[0]);

  VALUE rb_result = TypedData_Make_Struct(my_rb_pg_result, 
                                     t_pg_result,
                                     &pg_result_type,
                                     res);

  result->pgresult = PQexec(this->pgconn, query);

  return rb_result;

}
```

### Fun fact: reading the code of Ruby language, I think I have discovered why this is called klass and not klass and even in the Ruby language. The reason is that there is already a reserved keyword `class` in C; that's it.

## Printing value 

Once you have executed your query, you would like at least to see the result. 

To do that, you can get the value in the field of the result with PQgetValue, given the result of the query and the position in the result matrix.

```C
static void 
pg_printvalue(int argc, VALUE *argv, VALUE klass) {
  t_pg_result *res;
  TypedData_Get_Struct(klass, 
                       t_pg_result,
                       &pg_result_type, 
                       res);
  int i = argv[0];
  int j = argv[1];
  printf("%-15s", PQgetvalue(res->pgresult, i,j));
}
```

## Using the extension 

If you want to use your extension to do that, you must create an `Init_pgext` function, which will define every constants and make them available in the Ruby World.

```C
int Init_pgext() {
  VALUE c_myPG = rb_define_module("MyPG");
  VALUE my_rb_pg = rb_define_class_under(c_myPG, 
                                         "Conn", 
                                         rb_cObject);

  my_rb_pg_result = rb_define_class_under(c_myPG,
                                          "Result", 
                                          rb_cObject);

rb_define_singleton_method(my_rb_pg, "conn",pg_conn, -1);
rb_define_method(my_rb_pg, "exec",pg_exec, -1);
rb_define_method(my_rb_pg_result, "printvalue",pg_printvalue, -1);
}
```

Now you can compile your extension and eventually use it in your Ruby Code like the following: 

```ruby
require_relative './pgext'

connection = MyPG::Conn.conn("dbname = postgres")

query = <<-SQL
SELECT * FROM pg_catalog.pg_tables WHERE schemaname 
!= 'pg_catalog' AND schemaname != 'information_schema';
SQL

result = connection.exec(query)

res.printvalue(0,0) #-> table_name
```

## Conclusion

So in this article, we've managed to both: create a C extension and an essential way to connect and execute queries in Postgres. 

We have created both a C extension and connect, and execute arbitrary queries in our Postgres instance without any extra gem.