1 files changed, 89 insertions, 0 deletions
diff --git a/tests/test_database.py b/tests/test_database.py
new file mode 100644
index 0000000..e33fdae
--- /dev/null
+++ b/tests/test_database.py
@@ -0,0 +1,89 @@
+import sqlite3
+
+import pytest
+
+from database.database import Database
+
+
+def test_migrate_creates_schema(tmp_path):
+    database = Database(tmp_path / "test.db")
+
+    database.migrate()
+
+    with database.connect() as connection:
+        tables = {
+            row[0]
+            for row in connection.execute(
+                """
+                SELECT name
+                FROM sqlite_master
+                WHERE type = 'table'
+                """
+            )
+        }
+
+        assert "schema_migrations" in tables
+        assert "memories" in tables
+
+
+def test_migrate_records_version(tmp_path):
+    database = Database(tmp_path / "test.db")
+
+    database.migrate()
+
+    with database.connect() as connection:
+        rows = connection.execute("SELECT version FROM schema_migrations").fetchall()
+
+    assert rows == [(1,)]
+
+
+def test_migrate_is_idempotent(tmp_path):
+    database = Database(tmp_path / "test.db")
+
+    database.migrate()
+    database.migrate()
+
+    with database.connect() as connection:
+        rows = connection.execute("SELECT version FROM schema_migrations").fetchall()
+
+    assert rows == [(1,)]
+
+
+def test_failed_migration_rolls_back(tmp_path):
+    database = Database(tmp_path / "test.db")
+
+    migration = database.migrations_dir / "002_failing_migration.sql"
+    migration.write_text(
+        """
+        THIS IS INVALID SQL;
+        """,
+        encoding="utf-8",
+    )
+    try:
+        with pytest.raises(sqlite3.OperationalError):
+            database.migrate()
+
+        with database.connect() as connection:
+            migrations = connection.execute(
+                "SELECT version FROM schema_migrations"
+            ).fetchall()
+
+        assert migrations == [(1,)]
+    finally:
+        migration.unlink()
+
+
+def test_duplicate_migration_versions_are_rejected(tmp_path):
+    database = Database(tmp_path / "test.db")
+
+    duplicate = database.migrations_dir / "001_duplicate.sql"
+    duplicate.write_text(
+        "CREATE TABLE duplicate_table (id INTEGER PRIMARY KEY);",
+        encoding="utf-8",
+    )
+
+    try:
+        with pytest.raises(ValueError, match="Duplicate migration version"):
+            database.migrate()
+    finally:
+        duplicate.unlink()