Building a PostgreSQL Library in D

I was always curious about new programming languages and its frameworks. All of these time my experience and curiosity spread over only in front-end development ( I’ve done some backend though ?). I challenged myself to expand my skills and I found D programming language. D is in simple words is the advanced version of C and CPP.

What is D? the website say “**D is a general-purpose programming language with static typing, systems-level access, and C-like syntax. With the D Programming Language, write fast, read fast, and run fast.”

I have used PostgreSQL as my database for my works, and that’s why I chose it for this library as well. PostgreSQL is one of the major open source SQL database system that companies using now and its features are expanding even more.

Why Build a Custom ORM Library?

When messing with D language I couldn’t find one package that satisfies my, either the packages are stopped maintenance or can be used as for direct query. From a JavaScript background I used Sequalize ORM. That light me an idea, How about similar one in D.

So, I did some research and I found Postgres provides library for C. Then I thought, how about use the C bindings in D and use it for developing the ORM. I found the source code from https://github.com/adamdruppe/arsd/blob/master/postgres.d for binding the C library to D.

Getting Started

Requirements:

• PostgreSQL must be installed in your system. ( I developed for PostgreSQL 16)
• IDE (Zed/ VSCode/Vim)
• DMD - d language compiler

To create a new project, use the following command in your terminal:

1. Open your terminal or command prompt.
2. Navigate to the directory where you want to create your project.
3. Run the command:

dub init <project_name>
</project_name>

This command will create a new project directory with the specified name and set up the basic structure for a D project.

1. You'll be prompted to enter the following information:
   • Format - .sdl or .json ( I selected json )
   • Description of the project (optional)
   • Author name
   • License (e.g., MIT, BSD, etc.)
   • Copyright string
   • Add dependency (optional)
2. After providing the information, dub will create a new directory with your project name and generate the following files:
   • dub.json: Configuration file for your project
   • source/app.d: Main source file
   • .gitignore: Git ignore file
3. Navigate into your new project directory: cd
4. You can now start developing your D project!

With these steps completed, you'll have a basic D project structure set up and ready for development.

In Windows the below section needs to be added on the dub.json.

dub init <project_name>
</project_name>

or

The way I did is copied all the necessary DLL files to lib ( manually created) folder and then added the below code:

"libs": [ "pq" ],
    "lflags-windows-x86_64": [ "-LIBPATH:C:/Program Files/PostgreSQL/16/lib/" ],
    "copyFiles-windows-x86_64": [
        "C:/Program Files/PostgreSQL/16/lib/libpq.dll",
        "C:/Program Files/PostgreSQL/16/bin/libintl-9.dll",
        "C:/Program Files/PostgreSQL/16/bin/libssl-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libcrypto-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libwinpthread-1.dll",
        "C:/Program Files/PostgreSQL/16/bin/libiconv-2.dll"
    ],

In Linux or macOS, you need to ensure that the PostgreSQL development libraries are installed and properly linked. You can typically do this by installing the appropriate packages through your system's package manager. For example, on Ubuntu or Debian-based systems, you might use:

"copyFiles-windows": [
        "libs/*.dll"
    ],
    "lflags-windows": [
        "/LIBPATH:$PACKAGE_DIR/libs"
    ],
    "libs": [
        "pq"
    ]

Once you have the necessary libraries installed and properly linked, you can proceed with setting up your D project to work with PostgreSQL.

Implementing C bindings:

Here is the C bindings for D.

sudo apt-get install libpq-dev

Now we can easily use these functions in D.
This is the code for some basic exception handling:

module postgres.implementation.implementationc;

extern (C)
{
    struct PGconn
    {
    }

    struct PGresult
    {
    }

    void PQfinish(PGconn*);

    PGconn* PQconnectdb(const char*);

    int PQstatus(PGconn*); // FIXME check return value

    const(char*) PQerrorMessage(PGconn*);

    char* PQresultVerboseErrorMessage(const PGresult* res,
        PGVerbosity verbosity,
        PGContextVisibility show_context);
    PGresult* PQexec(PGconn*, const char*);
    void PQclear(PGresult*);

    PGresult* PQprepare(PGconn*, const char* stmtName, const char* query,
        ulong nParams, const void* paramTypes);

    PGresult* PQexecPrepared(PGconn*, const char* stmtName,
        int nParams, const char** paramValues,
        const int* paramLengths, const int* paramFormats, int resultFormat);

    int PQresultStatus(PGresult*); // FIXME check return value

    int PQnfields(PGresult*); // number of fields in a result
    const(char*) PQfname(PGresult*, int); // name of field

    int PQntuples(PGresult*); // number of rows in result
    const(char*) PQgetvalue(PGresult*, int row, int column);

    size_t PQescapeString(char* to, const char* from, size_t length);

    enum int CONNECTION_OK = 0;
    enum int PGRES_COMMAND_OK = 1;
    enum int PGRES_TUPLES_OK = 2;
    enum int PGRES_FATAL_ERROR = 7;
    enum PGContextVisibility
    {
        PQSHOW_CONTEXT_NEVER,
        PQSHOW_CONTEXT_ERRORS,
        PQSHOW_CONTEXT_ALWAYS
    }

    enum PGVerbosity
    {
        PQERRORS_TERSE,
        PQERRORS_DEFAULT,
        PQERRORS_VERBOSE,
        PQERRORS_SQLSTATE
    }

    int PQgetlength(const PGresult* res,
        int row_number,
        int column_number);
    int PQgetisnull(const PGresult* res,
        int row_number,
        int column_number);

    int PQfformat(const PGresult* res, int column_number);

    alias Oid = int;
    enum BYTEAOID = 17;
    Oid PQftype(const PGresult* res, int column_number);

    char* PQescapeByteaConn(PGconn* conn,
        const ubyte* from,
        size_t from_length,
        size_t* to_length);
    char* PQunescapeBytea(const char* from, size_t* to_length);
    void PQfreemem(void* ptr);

    char* PQcmdTuples(PGresult* res);

}

• PGSqlException: A custom exception class that inherits from the standard D Exception class. It's designed to handle PostgreSQL-specific errors.
• Fields:
  • code: Stores the error code
  • sqlState: Stores the SQL state
  • message: Stores the error message
• Constructor: Takes a PGconn* (PostgreSQL connection) and an optional PGresult* (result of a query). PQresultVerboseErrorMessage and PQerrorMessage to extract detailed error information.
• DuplicateKeyException: A simple exception class for handling duplicate key errors. It only takes a message parameter and passes it to the base Exception class.

I will be adding more exceptions and other situations as I work on this project

Now create create a implementation/core/core.d file for writing the connection code.

module postgres.implementation.exception;

public:
import std.conv;

private import postgres.implementation.implementationc;

class PGSqlException : Exception
{
    string code;
    string sqlState;
    string message;
    this(PGconn* conn, PGresult* res = null)
    {
        if (res != null)
        {
            char* c = PQresultVerboseErrorMessage(res, PGVerbosity.PQERRORS_VERBOSE, PGContextVisibility
                    .PQSHOW_CONTEXT_ALWAYS);
            char* s = PQresultVerboseErrorMessage(res, PGVerbosity.PQERRORS_SQLSTATE, PGContextVisibility
                    .PQSHOW_CONTEXT_ALWAYS);
           string ss = to!string(c);
           import std.string:split;
           this.code = to!string(ss.split(':')[1]);

            this.sqlState = to!string(s);
        }
        const char* m = PQerrorMessage(conn);

        this.message = to!string(m);
        super(this.message);
    }
}

class DuplicateKeyException : Exception
{
    this(string message)
    {
        super(message);
    }
}

Key points of the above code:

• Postgres class: Represents a PostgreSQL database connection.
  • Manages connection creation, querying, and prepared statement execution.
  • Uses the C bindings defined earlier to interact with the PostgreSQL library.
• QueryResult class: Encapsulates the result of a database query.
  • Stores query results in a structured format.
  • Handles different data types and formats returned by PostgreSQL.
• Error handling: Implements custom exception handling for PostgreSQL errors.
• Connection management: Includes automatic reconnection attempts on connection loss.
• Prepared statements: Supports execution of prepared SQL statements with parameter binding.
• Memory management: Properly frees resources using destructors (~this()).
• UTF-8 support: Sets the connection encoding to UTF-8 by default.

This implementation provides a high-level interface for D applications to interact with PostgreSQL databases, abstracting away many of the low-level details of the C API.

You might have an IDE warning/error now that “connection module not found”

Let’s create connection module:

Create _internal/connection.d file add this code:

dub init <project_name>
</project_name>

Add constants and other options for SQL:

_internal/consts.d

"libs": [ "pq" ],
    "lflags-windows-x86_64": [ "-LIBPATH:C:/Program Files/PostgreSQL/16/lib/" ],
    "copyFiles-windows-x86_64": [
        "C:/Program Files/PostgreSQL/16/lib/libpq.dll",
        "C:/Program Files/PostgreSQL/16/bin/libintl-9.dll",
        "C:/Program Files/PostgreSQL/16/bin/libssl-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libcrypto-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libwinpthread-1.dll",
        "C:/Program Files/PostgreSQL/16/bin/libiconv-2.dll"
    ],

Creating Model Template

D supports template metaprogramming, a feature that allows you to write highly generic code. This means that D has templates similar to those in C but more powerful and flexible.
The ABC’s of Templates in D | The D Blog

Key features of D's templates:

1. Compile-time type checking: Templates are checked at compile time, ensuring type safety.
2. Code generation: You can use templates to generate specialized code for different types or values.
3. Variadic templates: D supports templates that can take an arbitrary number of arguments, including types and values.
4. Static ifs and mixins: These allow you to generate and manipulate code during compilation based on conditions or even inject string-based code (with mixin).

Now lets create a template class.

model.d

Now use the code from https://github.com/rodevasia/sequelized/blob/main/source/postgres/model.d paste to your file

Let's examine the code from the provided GitHub link:

"copyFiles-windows": [
        "libs/*.dll"
    ],
    "lflags-windows": [
        "/LIBPATH:$PACKAGE_DIR/libs"
    ],
    "libs": [
        "pq"
    ]

This code defines a template class Model in D. Here's a breakdown of its key components:

1. Module declaration: The code is part of the postgres.model module.
2. Imports: Various standard D libraries and custom modules are imported for use in the class.
3. Template class: The Model class is defined as a template with type parameter T. This allows the class to work with different types.
4. Class methods: The class includes several methods for database operations such as save(), update(), delete(), and find().
5. Compile-time reflection: The code uses D's compile-time features to inspect the fields of the type T and generate appropriate SQL queries.
6. SQL query generation: Methods like getInsertQuery() and getUpdateQuery() dynamically create SQL queries based on the structure of type T.
7. Database interaction: The class uses a Connection object to interact with a PostgreSQL database.

We written all the code for working. Let’s make this a library. add this on your dub.json

dub init <project_name>
</project_name>

Using the Library:

Let create a new project:

"libs": [ "pq" ],
    "lflags-windows-x86_64": [ "-LIBPATH:C:/Program Files/PostgreSQL/16/lib/" ],
    "copyFiles-windows-x86_64": [
        "C:/Program Files/PostgreSQL/16/lib/libpq.dll",
        "C:/Program Files/PostgreSQL/16/bin/libintl-9.dll",
        "C:/Program Files/PostgreSQL/16/bin/libssl-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libcrypto-3-x64.dll",
        "C:/Program Files/PostgreSQL/16/bin/libwinpthread-1.dll",
        "C:/Program Files/PostgreSQL/16/bin/libiconv-2.dll"
    ],

add the the library as dependency in dub.json

"copyFiles-windows": [
        "libs/*.dll"
    ],
    "lflags-windows": [
        "/LIBPATH:$PACKAGE_DIR/libs"
    ],
    "libs": [
        "pq"
    ]

app.d

sudo apt-get install libpq-dev

Let's break down the code and explain its main components:

Imports

The code imports necessary modules from the standard library and the Sequalized library:

• std.stdio: For basic input/output operations
• postgres._internal.connection: Handles database connection details
• postgres.implementation.core: Core functionality for PostgreSQL operations
• postgres.model: Provides the Model mixin for defining database models
• postgres._internal.consts: Contains constant values used in the library

Main Function

The main function demonstrates how to use the Sequalized library:

• It creates a DatabaseConnectionOption object with connection details
• Initializes a Postgres object with these options
• Creates an instance of the Example class
• Calls sync() to create the corresponding table in the database
• Sets a value for the textField and inserts a record into the database

Example Class

This class defines a model for the database table:

• It uses the Model mixin to inherit ORM functionality
• Defines two fields: id and textField
• Uses attributes like @Type, @PmKey, and @unique to specify field properties

I haven't included full process, and that is for you to find out :)

If you'd love to contribute to my project here is the link for the repo:
https://github.com/rodevasia/sequelized

The above is the detailed content of Building a PostgreSQL Library in D. For more information, please follow other related articles on the PHP Chinese website!