A practical introduction to embedded databases in C. Connect to SQLite3, create a table with name and date of birth fields, insert records, and query them back — all in clean ANSI C.
SQLite3 is a self-contained, serverless database engine. The entire database lives in a single file on disk. No separate server process is needed — your program is the database server.
YYYY-MM-DD text — SQLite has no native date type.
sqlite3_exec() — is the classic ANSI C approach. Each row returned by the query triggers one call to your callback function.
You need the SQLite3 header (sqlite3.h) and link library. Choose your platform:
macOS ships with SQLite3 built in — no extra install needed.
This is a minimal two-file project — a Makefile and a single source file:
After building and running, SQLite3 will create a third file automatically:
Click any file to expand and view its contents. Use the Copy button to copy a single file, or download the ZIP to get everything at once.
CC = gcc
CFLAGS = -Wall -Wextra -ansi -pedantic
LDFLAGS = -lsqlite3
BIN = sqlite_demo
.PHONY: all clean
all: $(BIN)
$(BIN): main.c
$(CC) $(CFLAGS) -o $@ main.c $(LDFLAGS)
@echo ""
@echo " Build complete: ./$(BIN)"
@echo ""
clean:
rm -f $(BIN) people.db
@echo " Cleaned."
/*
* main.c - SQLite3 database example in ANSI C
* Creates a table with name + date-of-birth fields,
* inserts sample rows, then queries and prints them.
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#define DB_FILE "people.db"
/* Callback invoked once per result row by sqlite3_exec().
* argc = number of columns
* argv = column values (as strings, or NULL)
* cols = column names
*/
static int print_row(void *unused, int argc, char **argv, char **cols)
{
int i;
(void)unused;
for (i = 0; i < argc; i++) {
printf(" %-6s : %s\n", cols[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
/* Thin wrapper around sqlite3_exec() that prints errors. */
static void run_sql(sqlite3 *db, const char *sql, const char *label,
int use_callback)
{
char *err = NULL;
sqlite3_callback cb = use_callback ? print_row : NULL;
int rc = sqlite3_exec(db, sql, cb, NULL, &err);
if (rc != SQLITE_OK) {
fprintf(stderr, "SQL error [%s]: %s\n", label, err);
sqlite3_free(err);
}
}
int main(void)
{
sqlite3 *db;
int rc;
/* 1. Open (or create) the database file on disk */
rc = sqlite3_open(DB_FILE, &db);
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
printf("Opened database: %s\n\n", DB_FILE);
/* 2. Create table — two fields: name TEXT and dob TEXT (YYYY-MM-DD) */
run_sql(db,
"CREATE TABLE IF NOT EXISTS people ("
" id INTEGER PRIMARY KEY AUTOINCREMENT,"
" name TEXT NOT NULL,"
" dob TEXT NOT NULL"
");",
"CREATE TABLE", 0);
/* 3. Insert sample records */
run_sql(db,
"INSERT INTO people (name, dob) VALUES"
" ('Alice Johnson', '1990-03-15'),"
" ('Bob Martinez', '1985-07-22'),"
" ('Carol White', '2001-11-08');",
"INSERT", 0);
/* 4. Query all records ordered by date of birth */
printf("Records in database (ordered by date of birth):\n");
printf("--------------------------------------------------\n");
run_sql(db,
"SELECT id, name, dob FROM people ORDER BY dob;",
"SELECT", 1);
/* 5. Show total row count */
printf("Total rows:\n");
run_sql(db,
"SELECT COUNT(*) AS total FROM people;",
"COUNT", 1);
/* 6. Close the connection — flushes all data to people.db */
sqlite3_close(db);
printf("Database closed.\n");
return 0;
}
From inside the sqlite_demo/ directory:
After the first run, people.db is created on disk. You can inspect it directly with the SQLite3 shell:
INSERT will add three more rows because CREATE TABLE IF NOT EXISTS skips recreation but the inserts still execute. Use make clean to delete the database and start fresh.
sqlite3_open(DB_FILE, &db) returns SQLITE_OK on success. If people.db does not exist, SQLite3 creates it automatically. The db variable is an opaque handle you pass to every subsequent call.
The people table has three columns:
SQLite3 has no dedicated DATE type. Storing dates as TEXT in YYYY-MM-DD format works naturally: alphabetical sort equals chronological sort, and SQLite3's built-in date functions all understand this format.
The second argument to sqlite3_exec() is a function pointer. SQLite3 calls it once for every row in the result set, passing the column values as an array of strings. Returning a non-zero value from the callback aborts the query early.
sqlite3_close(db) finalises all pending writes and releases the file lock. Always close the database before your program exits — skipping this can corrupt the database on some platforms.
For inserting user-supplied data, use sqlite3_prepare_v2() and sqlite3_bind_text() instead of building SQL strings. This prevents SQL injection and improves performance for repeated inserts.
SQLite3 ships with built-in date functions. To calculate a person's age from their dob field:
To modify an existing record use UPDATE: