giacomocavalieri.me
🐿️ Type safe SQL in Gleam
1import argv
2import envoy
3import filepath
4import gleam/bool
5import gleam/erlang/atom
6import gleam/int
7import gleam/io
8import gleam/list
9import gleam/regexp
10import gleam/string
11import shellout
12import simplifile
13import temporary
14
15/// An integration test for a specific postgres type.
16///
17type TestCase {
18 /// This tests squirrel on a fixed set of parametrised queries doing
19 /// insertions and selects. To do so it creates some Gleam code that uses
20 /// Gleam values as parameters to the query.
21 ///
22 TestType(postgres_type: String, values: List(TestValue))
23
24 /// This tests squirrel running a fixed query with no parameters. And it
25 /// checks the value you get back from the query to match the given
26 /// `expected_value`.
27 ///
28 TestQuery(query: String)
29}
30
31/// How a tested value looks in gleam code when it is fed into a query or
32/// returned as a result.
33///
34type TestValue {
35 TestInputOutput(
36 /// How the value is supposed to look like when passed in as a query
37 /// argument to a squirrel generated query:
38 ///
39 /// ```gleam
40 /// let assert Ok(_) =
41 /// sql.insert(db, <input>)
42 /// // ^^^^^^^ This will be replaced with the input string
43 /// ```
44 ///
45 input: String,
46 /// How the value is supposed to look like when returned as an output
47 /// from a squirrel generated query:
48 ///
49 /// ```gleam
50 /// let assert Ok(pog.Returned(_, [result])) sql.select_one(db)
51 /// let assert <output> = result.col
52 /// // ^^^^^^^^ This will be replaced with the output string
53 /// ```
54 ///
55 output: String,
56 )
57 TestValue(value: String)
58}
59
60const integration_tests = [
61 // Booleans
62 TestType("bool", [TestValue("True"), TestValue("False")]),
63 // Text data
64 TestType("name", [TestValue("\"hello\"")]),
65 TestType("text", [TestValue("\"hello\"")]),
66 TestType("char(1)", [TestValue("\"j\"")]),
67 TestType("bpchar", [TestValue("\"j\"")]),
68 TestType("varchar(3)", [TestValue("\"jak\"")]),
69 TestType("citext", [TestValue("\"Jak\"")]),
70 // Integers
71 TestType("int2", [TestValue("1")]),
72 TestType("int4", [TestValue("1")]),
73 TestType("int8", [TestValue("1")]),
74 // Floats
75 TestType("float4", [TestValue("1.0")]),
76 TestType("float8", [TestValue("1.0")]),
77 TestType("numeric", [TestValue("1.0")]),
78 TestQuery("select 1::numeric"),
79 // Uuid
80 TestType("uuid", [TestValue("uuid.v7()")]),
81 // Bytea
82 TestType("bytea", [TestValue("<<1, 2, 3>>")]),
83 // Date
84 TestType("date", [TestValue("calendar.Date(1998, calendar.October, 11)")]),
85 // TimeOfDay
86 TestType("time", [TestValue("calendar.TimeOfDay(1, 11, 10, 0)")]),
87 // Timestamp
88 TestType("timestamp", [TestValue("timestamp.from_unix_seconds(1000)")]),
89 // Array
90 TestType("int[]", [TestValue("[1, 2, 3]")]),
91 TestType("squirrel_colour[]", [TestValue("[sql.Red, sql.Grey]")]),
92 // Custom enums
93 TestType(
94 "squirrel_colour",
95 [TestValue("sql.LightBrown"), TestValue("sql.Red"), TestValue("sql.Grey")],
96 ),
97 // Json
98 TestType(
99 "json",
100 [
101 TestInputOutput(
102 input: "json.object([#(\"a\", json.int(1))])",
103 output: "\"{\\\"a\\\":1}\"",
104 ),
105 ],
106 ),
107 TestType(
108 "jsonb",
109 [
110 TestInputOutput(
111 input: "json.object([#(\"a\", json.int(1))])",
112 output: "\"{\\\"a\\\": 1}\"",
113 ),
114 ],
115 ),
116]
117
118const project_toml = "name = \"integration_test_project\"
119version = \"1.0.0\"
120
121[dependencies]
122squirrel = { path = \"../..\" }
123"
124
125fn with_timeout(seconds seconds: Int, run fun: fn() -> a) {
126 #(atom.create("timeout"), seconds, [fun])
127}
128
129pub fn integration_test_() {
130 // This test takes a while since it has to compile an entire gleam project.
131 // So we have to increase the timeout
132 use <- with_timeout(seconds: 60)
133
134 // This test takes some time se we skip it unless one passes the "integration"
135 // flag from command line.
136 use <- bool.guard(when: argv.load().arguments != ["integration"], return: Nil)
137
138 let assert Ok(result) = run_integration_tests(integration_tests)
139 case result {
140 Ok(_) -> Nil
141 Error(#(_status_code, message)) -> {
142 io.println(message)
143 panic as "integration test failed"
144 }
145 }
146
147 Nil
148}
149
150fn run_integration_tests(
151 values: List(TestCase),
152) -> Result(Result(String, #(Int, String)), simplifile.FileError) {
153 let integration_test_project = filepath.join(".", "integration_test_project")
154 let _ = simplifile.create_directory(integration_test_project)
155 use dir <- temporary.create(
156 temporary.directory() |> temporary.in_directory(integration_test_project),
157 )
158
159 scaffold_gleam_project(dir)
160 let code = {
161 use code, test_case <- list.fold(values, "")
162 let assertions = setup_and_generate_code(for: test_case, in: dir)
163 code <> "\n\n" <> assertions
164 }
165 write_main(code, to: dir)
166 test_project(dir)
167}
168
169fn setup_and_generate_code(for test_case: TestCase, in dir: String) -> String {
170 case test_case {
171 TestQuery(query:) -> create_query_files_and_assertions_for_query(query, dir)
172 TestType(postgres_type:, values:) -> {
173 let table_name = safe_name(postgres_type) <> "_table"
174 create_database_table(table_name, postgres_type)
175 create_query_files_and_assertions(postgres_type, table_name, values, dir)
176 }
177 }
178}
179
180fn create_database_table(table_name: String, postgres_type: String) -> Nil {
181 let drop = "drop table if exists " <> table_name
182 let create = "create table if not exists " <> table_name <> "(
183 id bigserial primary key,
184 col " <> postgres_type <> " not null
185 )"
186
187 let assert Ok(database_url) = envoy.get("DATABASE_URL")
188 let assert Ok(_) =
189 shellout.command(
190 run: "psql",
191 with: [database_url, "-c" <> drop],
192 in: ".",
193 opt: [],
194 )
195
196 let assert Ok(_) =
197 shellout.command(
198 run: "psql",
199 with: [database_url, "-c" <> create],
200 in: ".",
201 opt: [],
202 )
203
204 Nil
205}
206
207/// Creates the most basic structure of a gleam project needed to run the
208/// integration tests inside `dir`.
209///
210fn scaffold_gleam_project(dir: String) -> Nil {
211 // We set up the canonical squirrel project structure.
212 //
213 let src_dir = filepath.join(dir, "src")
214 let sql_dir = filepath.join(src_dir, "sql")
215 let assert Ok(_) = simplifile.create_directory(src_dir)
216 let assert Ok(_) = simplifile.create_directory(sql_dir)
217
218 // Write the `gleam.toml` needed to run the project.
219 //
220 let assert Ok(_) =
221 simplifile.write(project_toml, to: filepath.join(dir, "gleam.toml"))
222
223 Nil
224}
225
226/// Following squirrel's project structure, this writes a file for each query to
227/// be tested on the given table and returns the Gleam code that runs the
228/// assertions to check everything works fine.
229///
230fn create_query_files_and_assertions(
231 postgres_type: String,
232 table_name: String,
233 values: List(TestValue),
234 dir: String,
235) -> String {
236 let src_dir = filepath.join(dir, "src")
237 let sql_dir = filepath.join(src_dir, "sql")
238
239 // Then we create all the queries to be fed into squirrel.
240 //
241 let insert = table_name <> "_insert"
242 let assert Ok(_) =
243 "insert into <table>(col) values ($1)"
244 |> string.replace(each: "<table>", with: table_name)
245 |> simplifile.write(to: filepath.join(sql_dir, insert <> ".sql"))
246
247 let select_all = table_name <> "_select_all"
248 let assert Ok(_) =
249 "select col from <table>"
250 |> string.replace(each: "<table>", with: table_name)
251 |> simplifile.write(to: filepath.join(sql_dir, select_all <> ".sql"))
252
253 let delete = table_name <> "_delete_rows"
254 let assert Ok(_) =
255 "delete from <table>"
256 |> string.replace(each: "<table>", with: table_name)
257 |> simplifile.write(to: filepath.join(sql_dir, delete <> ".sql"))
258
259 let assertions = {
260 use value <- list.map(values)
261 let heading = case value {
262 TestInputOutput(input:, output:) -> "
263 let input = " <> input <> "
264 let expected_output = " <> output <> "\n"
265 TestValue(value:) -> "
266 let input = " <> value <> "
267 let expected_output = input"
268 }
269
270 "
271<heading>
272let assert Ok(pog.Returned(1, [])) = sql.<insert>(db, input)
273let assert Ok(pog.Returned(1, [res])) = sql.<select_all>(db)
274case expected_output == res.col {
275 True -> Nil
276 False -> {
277 io.println(string.inspect(res.col))
278 panic as \" test for <postgres_type> type failed\"
279 }
280}
281let assert Ok(pog.Returned(1, [])) = sql.<delete>(db)
282"
283 |> string.replace(each: "<postgres_type>", with: postgres_type)
284 |> string.replace(each: "<heading>", with: heading)
285 |> string.replace(each: "<insert>", with: insert)
286 |> string.replace(each: "<select_all>", with: select_all)
287 |> string.replace(each: "<delete>", with: delete)
288 }
289
290 string.join(assertions, with: "\n")
291}
292
293fn create_query_files_and_assertions_for_query(
294 query: String,
295 dir: String,
296) -> String {
297 let src_dir = filepath.join(dir, "src")
298 let sql_dir = filepath.join(src_dir, "sql")
299 let query_name = "query_" <> int.to_string(unique_integer())
300 let assert Ok(_) =
301 simplifile.write(query, to: filepath.join(sql_dir, query_name <> ".sql"))
302
303 "let assert Ok(_) = sql.<query>(db)"
304 |> string.replace(each: "<query>", with: query_name)
305}
306
307@external(erlang, "squirrel_ffi", "unique")
308fn unique_integer() -> Int
309
310/// Writes the entry point of the Gleam project that will connect to the
311/// database and run all the assertions testing the generated squirrel code.
312///
313fn write_main(assertions: String, to dir: String) -> Nil {
314 let main = "
315import gleam/erlang/process
316import gleam/io
317import gleam/json
318import gleam/string
319import gleam/time/calendar
320import gleam/time/timestamp
321import pog
322import sql
323import youid/uuid
324
325pub fn main() {
326 let name = process.new_name(\"test\")
327 let config =
328 pog.Config(
329 ..pog.default_config(name),
330 port: 5432,
331 user: \"squirrel_test\",
332 host: \"localhost\",
333 database: \"squirrel_test\",
334 )
335 let assert Ok(actor) = pog.start(config)
336 let db = actor.data
337
338" <> assertions <> "
339}"
340
341 let src_dir = filepath.join(dir, "src")
342 let assert Ok(_) =
343 simplifile.write(main, to: filepath.join(src_dir, "main.gleam"))
344
345 Nil
346}
347
348/// First runs the squirrel command to generate code and then the main module of
349/// the project to run all the generated assertions.
350///
351fn test_project(dir: String) -> Result(String, #(Int, String)) {
352 let assert Ok(_) =
353 shellout.command(
354 run: "gleam",
355 with: ["run", "-msquirrel"],
356 in: dir,
357 opt: [],
358 )
359
360 shellout.command(run: "gleam", with: ["run", "-mmain"], in: dir, opt: [])
361}
362
363fn safe_name(string: String) -> String {
364 let assert Ok(parens) = regexp.from_string("[()]")
365 let assert Ok(array) = regexp.from_string("\\[\\]")
366
367 string
368 |> regexp.replace(each: parens, with: "_")
369 |> regexp.replace(each: array, with: "array")
370}