slonik VS ts-sql

And sometimes invest instead in learning a technology rather than hide it: for example slonik encourages you to write normal SQL queries by making SQL templating easier and safer. In turn, your IDE would be able to understand those queries and give you support based on the database schemas you actually have.

I'd push you to consider using postgres, slonik or similar for database queries. With these libraries, you just write SQL, but they perform input sanitization for you. So you can safely write:

You can already use postgres with Slonik.

https://github.com/gajus/slonik#user-content-slonik-how-are-...

It is not going to be the default because it is way slower.

https://github.com/gajus/slonik/actions/runs/6616647651

Test node_version:18 test_only:postgres-integration is taking 3 minutes.

Test node_version:18 test_only:pg-integration is taking 38 seconds.

For those uninitiated, Slonik is a battle-tested SQL query building and execution library for Node.js. Its primary goal is to allow you to write and compose SQL queries in a safe and convenient way. Now, let's see how it pairs with Express.js.

I am the maintainer of Slonik and I am trying to understand what portion of this sub-users are using Slonik vs other libraries, and if they are using anything else – what are their reasons for it.

It's nice that they implemented string templates essentially exactly the same way Javascript template literals and tag functions work. They even give an example of using it to create a prepared statement (e.g. DB."SELECT * FROM foo WHERE bar = \{inputParam}") which is exactly what many NodeJS libraries due, e.g. Slonik https://github.com/gajus/slonik, like sql`SELECT * FROM foo WHERE bar = ${inputParam}`;

I've found libraries like Zod useful when interacting with external data sources like a database. Slonik[1] uses Zod to define the types expected from a SQL query and then performs runtime validation on the data to ensure that the query is yielding the expected type.

I don't think it's necessary to use Zod/runtime validation everywhere, but it's a nice tool to have on hand.

[1]https://github.com/gajus/slonik

Demonstrate how easily and accidentally one can make an SQL injection with these:

https://github.com/porsager/postgres

https://github.com/gajus/slonik

Have you tried Slonik (https://github.com/gajus/slonik)? It won't generate types from queries automatically, but it encourages writing SQL vs. a query builder and allows type annotations of queries with Zod. Query results are validated at runtime to ensure the queries are typed correctly.

There is an implementation of SQL that operates on a table shaped type, entirely at type level. For your amusement: https://github.com/codemix/ts-sql

There are a bunch of more practical takes that codegen types from your database and generate types for your queries, eg: https://github.com/adelsz/pgtyped

To me the second approach seems much more pragmatic because you don’t need to run a SQL parser in a fairly potato interpreter on every build

> If I would level criticisms at dialyzer it would be its sometimes difficult to read warnings, it’s speed (despite being multithreaded) and the race conditions in the VS Code plugin (which is looking for extra maintainers – if I had time I would help).

One of the advantages of TypeScript is that VSCode is written in TypeScript, and both VSCode and TypeScript are developed by the same company, so there's a really nice synergy there. I imagine Kotlin users feel the same way using Jetbrains products, and Swift users feel the same way about XCode.

Dialyzer looks interesting, but I can't imagine giving up on the expressiveness of TypeScript. Some of the things you can do with generics, mapped types, intersection types, template literal types, conditional types, and utility types are almost mind boggling. It's difficult to reap all of the benefits of static analysis without some of these advanced type operators. The type manipulation section of the TS manual is really underrated.

Someone for example wrote an SQL parser in TypeScript that requires no runtime code [1]. It can infer the types of an SQL query's result based on an SQL string without any runtime code execution. There was a similar project where someone built a JSON parser entirely using the type system [2]. There's also an ongoing discussion on Github about the the fact that TypeScript's type system appears to be a Turing-complete language with some other cool examples [3]. My point is that the type system is incredibly expressive. You rarely run into an idiom that can't be typed effectively.

[1] https://github.com/codemix/ts-sql

[2] https://twitter.com/buildsghost/status/1301976526603206657

[3] https://github.com/microsoft/TypeScript/issues/14833

If anyone saw this meme and thought, "damn parsing a type from a SQL query, that looks useful" (as I did), the source appears to be from here.

I mean, why stop there? https://github.com/codemix/ts-sql

Which allows for things like this type that implements a simplified SQL query parser checked against a provided 'database' object:

https://github.com/codemix/ts-sql

This project was my go-to "nifty but pointless" example for TS string literal types before this article :)

author of ts-sql[0] here, this looks great (and a way more practical approach!)

[0] https://github.com/codemix/ts-sql