How to Use the World’s Most Used SQL Database in C (SQLite Quickstart)

SQLite is the world’s most used relational database for one simple reason: it’s easy to integrate it into your app. No database connections or user accounts to set up. Just compile and link a single source file into your program, and you’re ready to go.

Yes, an entire SQL database system in a single source file. Okay, the raw source code is spread over multiple files. However, when you download the release version everything is merged into one file and two headers. This makes it ridiculously easy to use.

Simpply copy sqlite3.c and the headers to a new project. Next, write a simple 3 line CMake build script to compile and link main.c and sqlite.c. Then write a handful of lines of C code:

#include <stdio.h>
#include <stdlib.h>

#include "sqlite3.h"

int main(int argc, const char **argv) {
    // Initialization
    sqlite3 *db;
    int retCode = sqlite3_open("mydatabase.db", &db);
    if (retCode) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return EXIT_FAILURE;
    } else {
        printf("Opened database successfully\n");
    }

    //Cleanup
    printf("Closing the database\n");
    sqlite3_close(db);
    db = NULL;

    return EXIT_SUCCESS;
}

This code will create mydatabase.db when run for the first time. But, it’ll be empty. So lets add some code to create a table. I’m going to create a users table; the kind you’d have if you’re building a multi-user app via this code:

/** Creates the database table(s) if needed.
 * 
 * @return bool true if successful, and false otherwise
 */
bool initDB(sqlite3 *db) {
    if(!db) {
        fprintf(stderr, "NULL pointer passed to %s\n", __func__);
        return false;
    }
    char *sql = "CREATE TABLE IF NOT EXISTS users("
                "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                "username TEXT UNIQUE NOT NULL,"
                "password_hash TEXT,"
                "first_name TEXT,"
                "family_name TEXT);";
    char *errMsg = NULL;

    int retCode = sqlite3_exec(db, sql, NULL, NULL, &errMsg);

    if (retCode == SQLITE_OK) {
        printf("Database initialized\n");
        return true;
    } else {
        fprintf(stderr, "SQL error when creating table: %s\n", errMsg);
        sqlite3_free(errMsg);
        return false;
    }
}

Now if we open the database file you’ll see that it contains the users table that we designed. There are no users yet, but the database structure is there (see below).

By the way, I’m viewing the database via the SQLite Viewer extension for VS Code. It’s very useful if you’re working on database code.

Next video we’ll add some users to our database. See you in the next video.

Download the Source Code

The source code (and much more) is available in the Kea Campus, to Creator & higher tier members.