Jason Shin
30 Jul 2023
•
3 min read
This story is about sqlx-ts. It is a CLI tool that provides
In short, it enables you to never ship broken and unoptimized queries to production. Here is an example of sqlx-ts running against PostgreSQL (but it works with MySQL as well)
in the video, sqlx-ts performed
As a result, you can never deploy SQL queries that are broken or unoptimized. Optimizing query is easier with raw SQLs as you can directly run SQL analyzer instead of grabbing a generated SQLs from ORMs.
sqlx-ts can work with complex queries as well such as joins
For a full guide, check the official documentation
First, install sqlx-ts
npm install sqlx-ts
# or if yarn
yarn add sqlx-ts
installing sqlx-ts NPM module also installs Rust binary of sqlx-ts.
Check the full getting started guide
Check --help
for all supported CLI arguments
npx sqlx-ts --help
You need to have a database with schema running locally (for example)
$ npx sqlx-ts <path to project> --db-host=127.0.0.1 --db-port=4321 --db-type=postgres --db-pass=postgres --db-user=postgres --db-name=sqlx-ts
Above command will search the target directory and validate all detected SQLs in-code against the target database's information_schema.
That's it! All your queries are valid if sqlx-ts did not throw an error.
Next, we can try generating TypeScript types against the detected SQLs
// src/app/index.ts
(async () => {
const someQuery = await client.query(sql`
SELECT items.id
FROM items
`)
for (const row of someQuery.rows) {
const { tableId, points } = row
console.log(tableId, points)
}
})();
Run npx sqlx-ts <path to project> --db-host=127.0.0.1 --db-port=4321 --db-type=postgres --db-pass=postgres --db-user=postgres --db-name=sqlx-ts -g
*note that I added -g
in the end.
The command generated
// src/app/index.queries.ts
export type SomeQueryParams = [];
export interface ISomeQueryResult {
food_type: string;
id: number;
points: number;
table_id: number;
time_takes_to_cook: number;
};
export interface ISomeQueryQuery {
params: SomeQueryParams;
result: ISomeQueryResult;
};
(If you don't like the generated type's formatting, you can simply post-process them using prettier)
Now you can use the generated types against the database queries in-code
// src/app/index.ts
import { ISomeQueryResult } from './index.queries'
(async () => {
const someQuery = await client.query<ISomeQueryResult>(sql`
SELECT items.id
FROM items
`)
for (const row of someQuery.rows) {
const { tableId, points } = row
console.log(tableId, points)
}
})();
As a result, any changes in the schema would break the query during build-time.
You can also use annotations to
You can imagine 95% of SQL features are supported for both SQL check and type-generation features.
The project is largely inspired by a library in Rust sqlx. I'm always interested in how to keep code safe especially during compile time. Rust and its ecosystem always have something to learn and port over into TypeScript world for greater good.
Thank you and please report any issues on Github
Jason Shin
Node, Python, Rust, Machine Learning, Functional Programming, React, Vue, Kubernetes and Scala.
See other articles by Jason
Ground Floor, Verse Building, 18 Brunswick Place, London, N1 6DZ
108 E 16th Street, New York, NY 10003
Join over 111,000 others and get access to exclusive content, job opportunities and more!