Using Databases with Concord

When developing a Discord bot, you often need to persist data across bot restarts. Databases provide a reliable way to store and retrieve information that won't be lost when your bot shuts down or crashes.

This guide covers two popular database options for Concord bots:

SQLite3: A lightweight, file-based database ideal for simple bots or development
PostgreSQL: A robust, client-server database system better for production applications

Choosing the Right Database

Feature	SQLite3	PostgreSQL
Setup Complexity	Low (single file)	Higher (server installation)
Scalability	Limited (suitable for small-to-medium bots)	High (can handle large applications)
Concurrency	Limited (single-writer, multiple-reader)	Excellent (true concurrent access)
Performance	Good for most small bots	Better for high-load applications
Administration	Minimal	Requires more maintenance
Use Case	Personal bots, development	Production bots, multi-server bots

PostgreSQL

PostgreSQL is a powerful, enterprise-grade database system with robust features for scaling, concurrency, and data integrity.

Installation

Before using PostgreSQL, you'll need to:

Install PostgreSQL on your server or use a hosted solution
Install the PostgreSQL client library:

# Debian/Ubuntu
sudo apt-get install libpq-dev

# Fedora/RHEL
sudo dnf install postgresql-devel

# macOS
brew install postgresql

When compiling your bot, link against libpq:

gcc bot.c -o bot -ldiscord -lcurl -lpq

Opening a Database Connection

Creating a connection to PostgreSQL requires providing authentication credentials:

#include <libpq-fe.h> // PostgreSQL library
#include <concord/discord.h>
#include <concord/logmod.h>

// Connect to the database
PGconn *conn = PQconnectdb("host=localhost port=5432 dbname=mybot user=postgres password=secretpassword");

// Check connection status
if (PQstatus(conn) != CONNECTION_OK) {
    logmod_log(FATAL, NULL, "Connection to database failed: %s", PQerrorMessage(conn));
    PQfinish(conn);
    return;
}

logmod_log(INFO, NULL, "Successfully connected to PostgreSQL database");

// Use the connection for database operations...

// Always close the connection when done
PQfinish(conn);

For better security, consider storing credentials in environment variables or a config file rather than hardcoding them.

Creating a Table

Tables define the structure of your data. Here's how to create one:

// Create a table for storing user data
PGresult *res = PQexec(conn, 
    "CREATE TABLE IF NOT EXISTS users("
    "user_id BIGINT PRIMARY KEY,"  // Discord IDs are 64-bit integers
    "guild_id BIGINT NOT NULL,"
    "points INTEGER DEFAULT 0,"
    "last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    ")");

if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    logmod_log(ERROR, NULL, "Failed to create table: %s", PQerrorMessage(conn));
    PQclear(res);
    // Continue execution - table creation might fail if table already exists
} else {
    logmod_log(INFO, NULL, "Table created or already exists");
}

// Always clean up the result object
PQclear(res);

Using Prepared Statements

Prepared statements are safer and more efficient than building SQL strings directly:

// Prepare a statement for inserting user data
res = PQprepare(conn, "insert_user",
    "INSERT INTO users(user_id, guild_id, points) VALUES($1, $2, $3) "
    "ON CONFLICT(user_id) DO UPDATE SET points = users.points + $3",
    3, NULL);

if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    logmod_log(ERROR, NULL, "Failed to prepare statement: %s", PQerrorMessage(conn));
    PQclear(res);
    return;
}
PQclear(res);

// Execute the prepared statement with parameters
const char *paramValues[3];
char user_id_str[32], guild_id_str[32], points_str[32];

snprintf(user_id_str, sizeof(user_id_str), "%"PRIu64, msg->author->id);
snprintf(guild_id_str, sizeof(guild_id_str), "%"PRIu64, msg->guild_id);
snprintf(points_str, sizeof(points_str), "%d", 10); // Add 10 points

paramValues[0] = user_id_str;
paramValues[1] = guild_id_str;
paramValues[2] = points_str;

res = PQexecPrepared(conn, "insert_user", 3, paramValues, NULL, NULL, 0);

if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    logmod_log(ERROR, NULL, "Failed to execute statement: %s", PQerrorMessage(conn));
} else {
    logmod_log(INFO, NULL, "Successfully updated user points");
}

PQclear(res);

Reading Data

To retrieve data from PostgreSQL:

// Query user points
char user_id_str[32];
snprintf(user_id_str, sizeof(user_id_str), "%"PRIu64, msg->author->id);

const char *paramValues[1] = {user_id_str};

res = PQexecParams(conn,
    "SELECT points, last_seen FROM users WHERE user_id = $1",
    1, NULL, paramValues, NULL, NULL, 0);

if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    logmod_log(ERROR, NULL, "Query failed: %s", PQerrorMessage(conn));
    PQclear(res);
    return;
}

// Check if we got any results
if (PQntuples(res) > 0) {
    int points = atoi(PQgetvalue(res, 0, 0));
    const char *last_seen = PQgetvalue(res, 0, 1);
    
    char response[256];
    snprintf(response, sizeof(response), 
             "You have %d points. Last seen: %s", points, last_seen);
             
    struct discord_create_message params = { .content = response };
    discord_create_message(client, msg->channel_id, &params, NULL);
} else {
    // No data found
    struct discord_create_message params = { .content = "No data found for your user." };
    discord_create_message(client, msg->channel_id, &params, NULL);
}

PQclear(res);

PostgreSQL Best Practices

Use connection pooling for busy bots
Use prepared statements to prevent SQL injection
Implement transactions for operations that modify multiple tables
Handle connection failures gracefully with reconnection logic
Include proper indexes on frequently queried columns

SQLite3

SQLite3 is a lightweight, file-based database that's perfect for simpler bots or development environments.

Installation

Install the SQLite3 development library:

# Debian/Ubuntu
sudo apt-get install libsqlite3-dev

# Fedora/RHEL
sudo dnf install sqlite-devel

# macOS
brew install sqlite3

When compiling, link against SQLite3:

gcc bot.c -o bot -ldiscord -lcurl -lsqlite3

Opening a Database Connection

SQLite3 stores your entire database in a single file:

#include <sqlite3.h>
#include <concord/discord.h>
#include <concord/logmod.h>

sqlite3 *db;
int rc = sqlite3_open("bot_data.db", &db);

if (rc != SQLITE_OK) {
    logmod_log(ERROR, NULL, "Failed to open database: %s", sqlite3_errmsg(db));
    sqlite3_close(db);  // Always close even on error
    return;
}

// Use the database...

// Close when done
sqlite3_close(db);

Creating a Table

Create tables to define your data structure:

char *err_msg = NULL;
const char *sql = 
    "CREATE TABLE IF NOT EXISTS users("
    "user_id INTEGER NOT NULL,"    // Discord user ID (64-bit)
    "guild_id INTEGER NOT NULL,"   // Discord guild ID (64-bit)
    "points INTEGER DEFAULT 0,"    // User points
    "last_seen INTEGER,"          // Timestamp
    "PRIMARY KEY(user_id, guild_id)"
    ")";

rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);

if (rc != SQLITE_OK) {
    logmod_log(ERROR, NULL, "SQL error: %s", err_msg);
    sqlite3_free(err_msg);
    return;
}

Using Prepared Statements

Prepared statements are safer and more efficient than direct SQL:

// Prepare statement for adding points
sqlite3_stmt *stmt = NULL;
rc = sqlite3_prepare_v2(db,
    "INSERT INTO users(user_id, guild_id, points, last_seen) "
    "VALUES(?, ?, ?, ?) "
    "ON CONFLICT(user_id, guild_id) DO "
    "UPDATE SET points = points + excluded.points, last_seen = excluded.last_seen",
    -1, &stmt, NULL);

if (rc != SQLITE_OK) {
    logmod_log(ERROR, NULL, "Failed to prepare statement: %s", sqlite3_errmsg(db));
    return;
}

// Bind parameters to the statement
sqlite3_bind_int64(stmt, 1, msg->author->id);     // user_id
sqlite3_bind_int64(stmt, 2, msg->guild_id);       // guild_id
sqlite3_bind_int(stmt, 3, 10);                    // points to add
sqlite3_bind_int64(stmt, 4, time(NULL));          // current timestamp

// Execute statement
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
    logmod_log(ERROR, NULL, "Failed to execute statement: %s", sqlite3_errmsg(db));
}

// Reset statement for reuse or finalize
sqlite3_finalize(stmt);

Reading Data

Query data from your database:

// Prepare a query
sqlite3_stmt *stmt = NULL;
rc = sqlite3_prepare_v2(db,
    "SELECT points, last_seen FROM users WHERE user_id = ? AND guild_id = ?",
    -1, &stmt, NULL);

if (rc != SQLITE_OK) {
    logmod_log(ERROR, NULL, "Failed to prepare query: %s", sqlite3_errmsg(db));
    return;
}

// Bind parameters
sqlite3_bind_int64(stmt, 1, msg->author->id);
sqlite3_bind_int64(stmt, 2, msg->guild_id);

// Execute and fetch results
if (sqlite3_step(stmt) == SQLITE_ROW) {
    // Data found - get values from columns
    int points = sqlite3_column_int(stmt, 0);
    int64_t last_seen = sqlite3_column_int64(stmt, 1);
    
    // Format a timestamp
    time_t timestamp = (time_t)last_seen;
    char time_str[64];
    strftime(time_str, sizeof(time_str), "%Y-%m-%d %H:%M:%S", localtime(&timestamp));
    
    char response[256];
    snprintf(response, sizeof(response), 
             "You have %d points. Last seen: %s", points, time_str);
             
    struct discord_create_message params = { .content = response };
    discord_create_message(client, msg->channel_id, &params, NULL);
} else {
    // No data found
    struct discord_create_message params = { .content = "No data found for your user." };
    discord_create_message(client, msg->channel_id, &params, NULL);
}

// Always finalize statements
sqlite3_finalize(stmt);

SQLite3 Best Practices

Use transactions for multiple operations:

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
// Multiple operations...
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

Avoid database locks by keeping transactions short
Use prepared statements to prevent SQL injection and improve performance
Check error codes from all SQLite3 functions
Always finalize statements and close the database when done

Consider WAL mode for better concurrency:

sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);

Debugging SQLite3 Issues

Use these techniques to troubleshoot database problems:

Enable foreign key constraints:

sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, NULL);

Check database integrity:

sqlite3_exec(db, "PRAGMA integrity_check", NULL, NULL, NULL);

Trace SQL statements for debugging:

sqlite3_trace(db, callback_function, user_data);

Example Bot Implementation

For a complete working example with SQLite3, see our SQLite3 with Concord guide which includes a full bot implementation with points tracking and leaderboards.

Navigation Menu

Uh oh!

Using Databases with Concord

Choosing the Right Database

PostgreSQL

Installation

Opening a Database Connection

Creating a Table

Using Prepared Statements

Reading Data

PostgreSQL Best Practices

SQLite3

Installation

Opening a Database Connection

Creating a Table

Using Prepared Statements

Reading Data

SQLite3 Best Practices

Debugging SQLite3 Issues

Example Bot Implementation

Further Reading

Uh oh!

FilesExpand file tree

databases_with_concord.md

Latest commit

History

databases_with_concord.md

File metadata and controls

Using Databases with Concord

Choosing the Right Database

PostgreSQL

Installation

Opening a Database Connection

Creating a Table

Using Prepared Statements

Reading Data

PostgreSQL Best Practices

SQLite3

Installation

Opening a Database Connection

Creating a Table

Using Prepared Statements

Reading Data

SQLite3 Best Practices

Debugging SQLite3 Issues

Example Bot Implementation

Further Reading