3 files changed, 42 insertions, 0 deletions
diff --git a/database/001_games.sql b/database/001_games.sql
new file mode 100644
index 0000000..be45084
--- /dev/null
+++ b/database/001_games.sql
@@ -0,0 +1,12 @@
+CREATE TABLE games (
+	id INTEGER PRIMARY KEY,
+	player1 INTEGER NOT NULL,
+	player2 INTEGER NOT NULL,
+	data TEXT NOT NULL,
+	created_at DATE DEFAULT (datetime('now')),
+	last_move_at DATE DEFAULT NULL,
+	FOREIGN KEY (player1) REFERENCES users(id) ON DELETE CASCADE,
+	FOREIGN KEY (player2) REFERENCES users(id) ON DELETE CASCADE
+);
+CREATE INDEX idx_games_player1 ON games(player1);
+CREATE INDEX idx_games_player2 ON games(player2);
diff --git a/database/db.js b/database/db.js
index 259ab58..a360e9b 100644
--- a/database/db.js
+++ b/database/db.js
@@ -3,6 +3,7 @@ import Database from "better-sqlite3";
 
 const allMigrations = [
 	"database/000_init.sql",
+	"database/001_games.sql",
 ];
 
 const db = new Database("jdm.db");
diff --git a/database/games.js b/database/games.js
new file mode 100644
index 0000000..b378eb2
--- /dev/null
+++ b/database/games.js
@@ -0,0 +1,29 @@
+import db from "./db.js";
+
+const createGameStatement = db.prepare("INSERT INTO games (player1, player2, data) VALUES (?, ?, ?);");
+const getGameStatement = db.prepare("SELECT * from games where id = ?;");
+const listGamesStatement = db.prepare("SELECT * from games where player1 = ?1 OR player2 = ?1 ORDER BY last_move_at;");
+
+export function createGame(player1, player2, data) {
+	try {
+		return createGameStatement.run(player1, player2, data).lastInsertRowId;
+	} catch {
+		return null;
+	}
+}
+
+export function getGame(id) {
+	try {
+		return getGameStatement.get(id);
+	} catch {
+		return null;
+	}
+}
+
+export function listGames(userId) {
+	try {
+		return listGamesStatement.all({ 1: userId });
+	} catch {
+		return [];
+	}
+}