git.iko.soy/rowtype-yoga/purescript-yoga-postgres
on add-postgres-koans help sign in
rowtype-yoga/purescript-yoga-postgres/test/Main.purs
457 lines 16 kB history raw
1module Test.Postgres.Main where
3import Prelude
5import Data.Either (Either(..))
6import Data.Maybe (Maybe(..))
7import Data.Time.Duration (Milliseconds(..))
8import Effect (Effect)
9import Effect.Aff (Aff, bracket, launchAff_, throwError, try)
10import Effect.Class (liftEffect)
11import Effect.Console (log)
12import Effect.Exception (error)
13import Test.Spec (Spec, around, describe, it)
14import Test.Spec.Assertions (shouldEqual, shouldSatisfy)
15import Test.Spec.Reporter.Console (consoleReporter)
16import Test.Spec.Runner (runSpec)
17import Yoga.Test.Docker as Docker
18import Test.Postgres.Schema as Schema
19import Yoga.Postgres as PG
20import Yoga.Postgres.TypedQuery as TypedQuery
21import Yoga.SQL.PostgresTypes as TypedSQL
23-- Test configuration
24testHost :: PG.PostgresHost
25testHost = PG.PostgresHost "localhost"
27testPort :: PG.PostgresPort
28testPort = PG.PostgresPort 5433 -- Test port from docker-compose.test.yml
30testDatabase :: PG.PostgresDatabase
31testDatabase = PG.PostgresDatabase "test_playground"
33testUsername :: PG.PostgresUsername
34testUsername = PG.PostgresUsername "postgres"
36testPassword :: PG.PostgresPassword
37testPassword = PG.PostgresPassword "postgres"
39-- Helper to create and manage Postgres connection
40withPostgres :: (PG.Connection -> Aff Unit) -> Aff Unit
41withPostgres test = do
42 conn <- liftEffect $ PG.postgres
43 { host: testHost
44 , port: testPort
45 , database: testDatabase
46 , username: testUsername
47 , password: testPassword
48 }
49 -- Clean up test tables before each test
50 _ <- try $ PG.executeSimple (PG.SQL "DROP TABLE IF EXISTS test_users CASCADE") conn
51 _ <- try $ PG.executeSimple (PG.SQL "DROP TABLE IF EXISTS test_posts CASCADE") conn
52 test conn
53 _ <- PG.end conn
54 pure unit
56-- Helper to check if value is Left (for use with shouldSatisfy)
57isLeft :: forall a b. Either a b -> Boolean
58isLeft (Left _) = true
59isLeft _ = false
61-- Helper to set up test table
62setupTestTable :: PG.Connection -> Aff Unit
63setupTestTable conn = do
64 _ <- PG.executeSimple
65 ( PG.SQL
66 """
67 CREATE TABLE test_users (
68 id SERIAL PRIMARY KEY,
69 name TEXT NOT NULL,
70 email TEXT,
71 age INTEGER,
72 active BOOLEAN DEFAULT true,
73 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
74 )
75 """
76 )
77 conn
78 pure unit
80-- Helper to set up table with constraints
81setupTableWithConstraints :: PG.Connection -> Aff Unit
82setupTableWithConstraints conn = do
83 _ <- PG.executeSimple
84 ( PG.SQL
85 """
86 CREATE TABLE test_users (
87 id SERIAL PRIMARY KEY,
88 email TEXT UNIQUE NOT NULL,
89 name TEXT NOT NULL
90 )
91 """
92 )
93 conn
94 pure unit
96spec :: Spec Unit
97spec = do
98 Schema.spec
100 describe "Yoga.Postgres Integration Tests" do
102 -- Connection Management Tests
103 around withPostgres do
104 describe "Connection Management" do
105 it "connects to Postgres successfully" \conn -> do
106 healthy <- PG.ping conn
107 healthy `shouldEqual` true
109 it "retrieves connection options" \conn -> do
110 opts <- liftEffect $ PG.options conn
111 opts.host `shouldSatisfy` (_ /= "")
112 opts.port `shouldEqual` 5433
113 opts.database `shouldEqual` "test_playground"
115 describe "Connection Errors" do
116 it "handles connection failures" do
117 result <- try do
118 conn <- liftEffect $ PG.postgres
119 { host: PG.PostgresHost "invalid-host-that-does-not-exist"
120 , port: PG.PostgresPort 9999
121 , database: testDatabase
122 , username: testUsername
123 , password: testPassword
124 }
125 PG.ping conn
126 case result of
127 Left _ -> pure unit
128 Right false -> pure unit
129 Right true -> shouldEqual "unhealthy" "healthy"
131 -- Basic Query Tests
132 around withPostgres do
133 describe "Basic Queries" do
134 it "creates and drops tables" \conn -> do
135 _ <- PG.executeSimple (PG.SQL "DROP TABLE IF EXISTS test_temp") conn
136 count <- PG.executeSimple (PG.SQL "CREATE TABLE test_temp (id SERIAL PRIMARY KEY, name TEXT)") conn
137 count `shouldEqual` 0
138 _ <- PG.executeSimple (PG.SQL "DROP TABLE test_temp") conn
139 pure unit
141 it "inserts and queries data" \conn -> do
142 setupTestTable conn
144 count <- PG.execute (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)")
145 [ PG.toPGValue "Alice", PG.toPGValue "alice@example.com" ]
146 conn
147 count `shouldEqual` 1
149 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
150 result.count `shouldEqual` 1
152 it "queries with WHERE clause" \conn -> do
153 setupTestTable conn
155 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email, age) VALUES ($1, $2, $3)")
156 [ PG.toPGValue "Bob", PG.toPGValue "bob@example.com", PG.toPGValue 30 ]
157 conn
159 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email, age) VALUES ($1, $2, $3)")
160 [ PG.toPGValue "Charlie", PG.toPGValue "charlie@example.com", PG.toPGValue 25 ]
161 conn
163 result <- PG.query (PG.SQL "SELECT * FROM test_users WHERE age > $1")
164 [ PG.toPGValue 26 ]
165 conn
166 result.count `shouldEqual` 1
168 it "updates data" \conn -> do
169 setupTestTable conn
171 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)")
172 [ PG.toPGValue "Diana", PG.toPGValue "diana@example.com" ]
173 conn
175 updateCount <- PG.execute (PG.SQL "UPDATE test_users SET email = $1 WHERE name = $2")
176 [ PG.toPGValue "diana.new@example.com", PG.toPGValue "Diana" ]
177 conn
178 updateCount `shouldEqual` 1
180 it "deletes data" \conn -> do
181 setupTestTable conn
183 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)")
184 [ PG.toPGValue "Eve", PG.toPGValue "eve@example.com" ]
185 conn
187 deleteCount <- PG.execute (PG.SQL "DELETE FROM test_users WHERE name = $1")
188 [ PG.toPGValue "Eve" ]
189 conn
190 deleteCount `shouldEqual` 1
192 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
193 result.count `shouldEqual` 0
195 -- Parameterized Query Tests
196 around withPostgres do
197 describe "Parameterized Queries" do
198 it "prevents SQL injection" \conn -> do
199 setupTestTable conn
201 let maliciousInput = "'; DROP TABLE test_users; --"
202 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
203 [ PG.toPGValue maliciousInput ]
204 conn
206 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
207 result.count `shouldEqual` 1
209 it "handles multiple parameters" \conn -> do
210 setupTestTable conn
212 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, age, email) VALUES ($1, $2, $3)")
213 [ PG.toPGValue "Frank", PG.toPGValue 35, PG.toPGValue "frank@example.com" ]
214 conn
216 result <- PG.query (PG.SQL "SELECT * FROM test_users WHERE age > $1")
217 [ PG.toPGValue 30 ]
218 conn
219 result.count `shouldEqual` 1
221 it "handles array parameters" \conn -> do
222 setupTestTable conn
224 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)")
225 [ PG.toPGValue "Grace", PG.toPGValue "grace@example.com" ]
226 conn
228 result <- PG.query (PG.SQL "SELECT * FROM test_users WHERE name = ANY($1)")
229 [ PG.toPGValue [ "Grace", "Unknown" ] ]
230 conn
231 result.count `shouldEqual` 1
233 -- Transaction Tests
234 around withPostgres do
235 describe "Transactions" do
236 it "commits transactions successfully" \conn -> do
237 setupTestTable conn
239 _ <- PG.transaction
240 ( \txn -> do
241 _ <- PG.txExecute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
242 [ PG.toPGValue "Henry" ]
243 txn
244 _ <- PG.txExecute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
245 [ PG.toPGValue "Iris" ]
246 txn
247 pure unit
248 )
249 conn
251 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
252 result.count `shouldEqual` 2
254 it "rolls back on errors" \conn -> do
255 setupTestTable conn
257 result <- try $ PG.transaction
258 ( \txn -> do
259 _ <- PG.txExecute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
260 [ PG.toPGValue "Jack" ]
261 txn
262 throwError (error "Intentional error")
263 )
264 conn
266 result `shouldSatisfy` isLeft
268 count <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
269 count.count `shouldEqual` 0
271 it "queries within transaction" \conn -> do
272 setupTestTable conn
274 _ <- PG.transaction
275 ( \txn -> do
276 _ <- PG.txExecute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
277 [ PG.toPGValue "Leo" ]
278 txn
280 result <- PG.txQuerySimple (PG.SQL "SELECT * FROM test_users") txn
281 liftEffect $ log $ "Rows in transaction: " <> show result.count
282 pure unit
283 )
284 conn
286 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
287 result.count `shouldEqual` 1
289 -- Data Type Tests
290 around withPostgres do
291 describe "Data Types" do
292 it "handles NULL values" \conn -> do
293 setupTestTable conn
295 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)")
296 [ PG.toPGValue "Mia", PG.toPGValue "" ]
297 conn
299 result <- PG.query (PG.SQL "SELECT * FROM test_users WHERE email IS NULL OR email = ''")
300 []
301 conn
302 result.count `shouldSatisfy` (_ >= 0)
304 it "handles various data types" \conn -> do
305 setupTestTable conn
307 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, age, active) VALUES ($1, $2, $3)")
308 [ PG.toPGValue "Nina", PG.toPGValue 28, PG.toPGValue true ]
309 conn
311 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
312 result.count `shouldEqual` 1
314 it "handles boolean values" \conn -> do
315 setupTestTable conn
317 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name, active) VALUES ($1, $2)")
318 [ PG.toPGValue "Oscar", PG.toPGValue false ]
319 conn
321 result <- PG.query (PG.SQL "SELECT * FROM test_users WHERE active = $1")
322 [ PG.toPGValue false ]
323 conn
324 result.count `shouldEqual` 1
326 -- Error Handling Tests
327 around withPostgres do
328 describe "Error Handling" do
329 it "handles syntax errors" \conn -> do
330 result <- try $ PG.executeSimple (PG.SQL "INVALID SQL SYNTAX HERE") conn
331 result `shouldSatisfy` isLeft
333 it "handles constraint violations" \conn -> do
334 setupTableWithConstraints conn
336 _ <- PG.execute (PG.SQL "INSERT INTO test_users (email, name) VALUES ($1, $2)")
337 [ PG.toPGValue "unique@example.com", PG.toPGValue "User1" ]
338 conn
340 result <- try $ PG.execute (PG.SQL "INSERT INTO test_users (email, name) VALUES ($1, $2)")
341 [ PG.toPGValue "unique@example.com", PG.toPGValue "User2" ]
342 conn
344 result `shouldSatisfy` isLeft
346 it "handles missing table errors" \conn -> do
347 result <- try $ PG.querySimple (PG.SQL "SELECT * FROM non_existent_table") conn
348 case result of
349 Left _ -> pure unit -- Expected error
350 Right _ -> throwError (error "Expected error for missing table")
352 it "handles missing column errors" \conn -> do
353 setupTestTable conn
355 result <- try $ PG.querySimple (PG.SQL "SELECT non_existent_column FROM test_users") conn
356 case result of
357 Left _ -> pure unit -- Expected error
358 Right _ -> throwError (error "Expected error for missing column")
360 -- Query Helper Tests
361 around withPostgres do
362 describe "Query Helpers" do
363 it "queryOne returns Maybe for single result" \conn -> do
364 setupTestTable conn
366 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
367 [ PG.toPGValue "Paul" ]
368 conn
370 result <- PG.queryOne (PG.SQL "SELECT * FROM test_users WHERE name = $1")
371 [ PG.toPGValue "Paul" ]
372 conn
373 case result of
374 Just _ -> pure unit
375 Nothing -> throwError (error "Expected to find user Paul")
377 it "queryOne returns Nothing when no results" \conn -> do
378 setupTestTable conn
380 result <- PG.queryOne (PG.SQL "SELECT * FROM test_users WHERE name = $1")
381 [ PG.toPGValue "NonExistent" ]
382 conn
383 case result of
384 Nothing -> pure unit
385 Just _ -> throwError (error "Expected Nothing for non-existent user")
387 it "queryOneSimple works without parameters" \conn -> do
388 setupTestTable conn
390 _ <- PG.execute (PG.SQL "INSERT INTO test_users (name) VALUES ($1)")
391 [ PG.toPGValue "Quinn" ]
392 conn
394 result <- PG.queryOneSimple (PG.SQL "SELECT * FROM test_users LIMIT 1") conn
395 case result of
396 Just _ -> pure unit
397 Nothing -> throwError (error "Expected to find at least one user")
399 it "executeSqlOne rejects multiple rows" \conn -> do
400 setupTestTable conn
401 _ <- PG.executeSimple
402 (PG.SQL "INSERT INTO test_users (name) VALUES ('Alice'), ('Bob')")
403 conn
404 result <- TypedQuery.executeSqlOne @() @{ id :: Int }
405 (TypedSQL.sql (TypedSQL.nonArg "SELECT id FROM test_users ORDER BY id"))
406 {}
407 conn
408 result `shouldSatisfy` isLeft
410 -- Prepared Statement Tests
411 around withPostgres do
412 describe "Prepared Statements" do
413 it "prepares and executes statements" \conn -> do
414 setupTestTable conn
416 let stmtName = PG.StatementName "insert_user"
417 _ <- PG.prepare stmtName (PG.SQL "INSERT INTO test_users (name, email) VALUES ($1, $2)") conn
419 result <- PG.executePrepared stmtName
420 [ PG.toPGValue "Rachel", PG.toPGValue "rachel@example.com" ]
421 conn
422 result.count `shouldEqual` 1
424 _ <- PG.deallocate stmtName conn
425 pure unit
427 it "reuses prepared statements" \conn -> do
428 setupTestTable conn
430 let stmtName = PG.StatementName "insert_user_batch"
431 _ <- PG.prepare stmtName (PG.SQL "INSERT INTO test_users (name) VALUES ($1)") conn
433 _ <- PG.executePrepared stmtName [ PG.toPGValue "Sam" ] conn
434 _ <- PG.executePrepared stmtName [ PG.toPGValue "Tina" ] conn
435 _ <- PG.executePrepared stmtName [ PG.toPGValue "Uma" ] conn
437 result <- PG.querySimple (PG.SQL "SELECT * FROM test_users") conn
438 result.count `shouldEqual` 3
440 _ <- PG.deallocate stmtName conn
441 pure unit
443main :: Effect Unit
444main = launchAff_ do
445 liftEffect $ log "\nStarting Postgres Integration Tests (with Docker)\n"
447 bracket
448 ( do
449 liftEffect $ log "Starting Postgres and waiting for it to be ready..."
450 Docker.startService (Docker.ComposeFile "docker-compose.test.yml") (Docker.Timeout (Milliseconds 30000.0))
451 liftEffect $ log "Postgres is ready!\n"
452 )
453 ( \_ -> do
454 Docker.stopService (Docker.ComposeFile "docker-compose.test.yml")
455 liftEffect $ log "Cleanup complete\n"
456 )
457 (\_ -> runSpec [ consoleReporter ] spec)