원시 SQL을 안전하게 작성하기 위한 라이브러리

PropelAuth에서 제가 맡은 업무 중 하나는 다양한 언어/프레임워크로 예제 앱/가이드를 작성하는 것입니다. 그것은 정말로 내 일에서 가장 재미있는 부분 중 하나입니다. 저는 새로운 것과 오래된 것 등 다양한 스택을 가지고 놀면서 고객을 지원하는 최선의 방법을 찾아냅니다.

그래서 처음부터 많은 프로젝트를 만들게 됐어요. 새 프로젝트를 시작할 때마다 몇 가지 중요한 선택을 해야 하는데, 제가 가장 많은 시간을 투자하는 결정은 다음과 같습니다.

어떤 DB 라이브러리를 사용해야 하나요?

제가 선호하는 라이브러리는 내가 작성하는 코드와 SQL 쿼리 자체 사이에 최소한의 추상화 계층을 갖고 있는 라이브러리입니다.

그 이유 중 하나는 실용성입니다. 언어를 자주 바꾸다보니 특정 ORM에 능숙해질 시간이 많지 않습니다. 또한 과거에는 데이터 과학 구성 요소가 많은 직업을 가졌기 때문에 SQL이 매우 편안합니다.

하지만 저는 "마술"을 싫어하는 경향이 있는 개발자이기도 합니다. 그래서 생성된 SQL이 어떻게 보일지 쉽게 알 수 없는 라이브러리나 시간을 다 소모한다고 느끼는 라이브러리는 피합니다. "X에서 조인하는 방법" 다음에 "두 가지 조건으로 X에서 조인하는 방법"을 검색해 보세요.

이 게시물에서는 제가 자주 사용하는 몇 가지 라이브러리와 제가 직접 사용해 보고 싶은 라이브러리를 강조하고 싶었습니다. 이 라이브러리는 모두 제가 작성한 코드와 SQL 사이의 차이를 최소화하려고 노력합니다. 처형되었습니다.

개인적으로 가장 좋아하는 것은 SQLx

제가 가장 좋아하는 Rust 크레이트 중 하나는 SQLx입니다.

자신의 말:

SQLx는 컴파일 시간 확인 쿼리를 지원합니다. 그러나 쿼리 작성을 위해 Rust API 또는 DSL(도메인별 언어)을 제공함으로써 이를 수행하지는 않습니다. 대신 일반 SQL을 입력으로 사용하고 해당 내용이 데이터베이스에 유효한지 확인하는 매크로를 제공합니다. 이것이 작동하는 방식은 SQLx가 컴파일 타임에 개발 DB에 연결하여 데이터베이스 자체가 SQL 쿼리를 확인(및 일부 정보 반환)하도록 하는 것입니다.

다르게 말하면 SQLx에서는 다음과 같은 쿼리를 작성해 보겠습니다.

let row = sqlx::query!(r#"
    SELECT enail
    FROM user
    WHERE user_id = ?
"#, user_id)
  .fetch_one(&pool)
  .await?;

표준처럼 보이지만 코드를 컴파일하면 다음과 같은 오류가 발생합니다.

error returned from database: column "enail" of relation "user" does not exist

이와 동일한 컴파일 타임 검사는 복잡한 쿼리에도 적용됩니다.

SELECT job_id, job_data 
FROM job_queue
WHERE job_status = 'Queued' AND run_at >= NOW()
ORDER BY run_at ASC
FOR UPDATE SKIP LOCKE -- oops
LIMIT 1

error returned from database: syntax error at or near "LOCKE"

쿼리는 데이터베이스에 대해 확인되므로 설치된 모든 확장 프로그램에서도 작동합니다.

이게 왜 멋있나요?

이것의 놀라운 점은 문자 그대로 SQL을 작성하고 있다는 것입니다. 그러나 원시 SQL을 작성할 수도 있는 postgres와 같은 크레이트와 달리 SQLx는 어리석은 실수를 방지합니다.

이것은 적은 비용으로 발생합니다. 이제 데이터베이스에 대한 컴파일 타임 종속성이 있지만 SQLx는 "오프라인 모드"를 통해 이 문제를 해결합니다. DB를 사용할 수 있으면 검증된 모든 쿼리가 포함된 파일을 생성할 수 있으며, 그러면 빌드에서 SQLx가 데이터베이스 대신 이 파일을 확인합니다.

내가 작성한 코드와 실행된 SQL 간의 차이를 최소화하려는 노력에서 SQLx를 사용하면 차이가 없으며 이를 얻기 위해 안전을 희생할 필요가 없었습니다.

PgTyped를 사용하여 SQL용 TS 인터페이스 생성

JavaScript/TypeScript 생태계에서 흔히 볼 수 있듯이 여기에는 다양한 옵션이 있습니다.

데이터베이스에서 TS 유형을 생성한 다음 쿼리 빌더와 원시 SQL을 작성하는 방법을 모두 제공하는 Kysely와 같은 옵션이 있습니다. 쿼리 빌더인 Drizzle이 있지만 명시된 목표는 작성하는 TS 코드와 생성된 SQL 간의 델타를 줄이는 것입니다. 아직 시도해 볼 기회가 없었던 SQLx 포트도 있습니다.

하지만 여기서 내가 찾고 있는 것과 가장 잘 일치하는 라이브러리는 PgTyped입니다. PgTyped를 사용하면 다음과 같이 별도의 파일에 쿼리를 정의할 수 있습니다.

/* @name FindEmailById */
SELECT email FROM user WHERE user_id = :userId;

그런 다음 스키마에 따라 적절한 유형의 함수를 생성하는 npx pgtyped -c config.json 명령을 실행합니다.

export interface IFindEmailByIdParams {
    userId?: string | null;
}

export interface IFindEmailByIdResult {
    email: string
}export const findEmailById = new PreparedQuery< // ...

해당 함수를 호출하여 DB에서 결과를 가져올 수 있습니다. 중요한 것은 쿼리가 잘못된 경우(존재하지 않는 열을 참조하는 경우) 다음과 같은 오류가 발생한다는 것입니다.

Error in query. Details: {
  errorCode: 'errorMissingColumn',
  hint: 'Perhaps you meant to reference the column "user.email".',
  message: 'column "enail" does not exist',
  position: '7'
}

이는 원시 SQL을 안전하게 작성할 수 있을 뿐만 아니라 애플리케이션 코드가 호출(또는 테스트에서 모의)할 수 있는 멋진 TS 추상화를 얻음을 의미합니다.

PgTyped의 가장 큰 단점은 Github 문제입니다. 유형의 null 허용 여부가 존중되지 않습니다. 이는 필수 필드에 대해 합리적으로 null을 전달할 수 있다는 의미이므로 상당히 실망스러울 수 있습니다. 또 다른 단점은 Postgres에만 해당된다는 것입니다. 이에 대해서는 나중에 "이식성" 섹션에서 자세히 설명합니다.

Prisma recently released TypedSQL — a “a new way to write raw SQL queries in a type-safe way.” They mention that part of the inspiration was both SQLx and PgTyped, so I am excited to try it out!

Something for the Python world: PugSQL

A library I enjoy when I switch to Python is PugSQL (Python). Similar to PgTyped, you create separate SQL files for your queries like this:

-- :name find_email :one
select email from users where user_id = :user_id

which will create a function that you can call:

email = queries.find_email(user_id=42)

The downside (relative to the previous libraries) is these queries aren’t automatically checked for issues. That being said, some tests can surface most (all?) the issues with the query itself — you just need to write them.

If you are feeling fancy, it’s possible to add your own automation which will check the queries. There are ways to verify a query against a DB without running the query — it’s just some additional work. Each query being in its own file makes it a bit easier to automate since you don’t need to go parse out the queries in the first place.

Mark up your interfaces with JDBI

Whenever I talk about how much I liked Dropwizard, I usually get met with blank stares. It’s a bit of a deeper cut in the Java world relative to Spring (either that or normal people don’t discuss Dropwizard at parties).

One of the reasons I liked Dropwizard so much was just because it came with JDBI. That library allowed you to annotate the functions on an interface with SQL queries and it would generate the implementation for you.

public interface UserDAO {
  @SqlQuery("select email from user where user_id = :user_id")
  String fetchEmail(@Bind("user_id") String userId);
}

final UserDAO userDao = database.onDemand(UserDAO.class);

Again though, this would require additional testing to find issues in the queries.

I should also mention that Spring Data JPA does also have the same concept with it’s @Query annotation. It’s been a very long time, but back when I was comparing JDBI and Spring Data JPA - I always felt like Spring was trying to get me to use it’s more magical “function name to sql query” methods. Upon re-reading the docs recently though, I was wrong, and it does mention that you can fallback to @Query pretty frequently.

Other considerations

“Use it sparingly”

If you followed some of the links in this post, you’ll find that some of these libraries don’t advocate for this approach as the primary way to query the database.

TypedSQL describes it as an escape hatch for when querying via their ORM isn’t sufficient. Same for Spring Data JPA which describes it as “fine for a small number of queries”.

This isn’t an unfounded claim — if you go down the path of writing raw SQL for every query, it can be pretty verbose. There are absolutely times where I am making a simple, boring table that’s basically just a key-value store, and the exercise in writing INSERT INTO boring_table VALUES (...) and SELECT * FROM boring_table WHERE ... etc is just a typing exercise.

A library that provides the best of both worlds seems great! The devil is really in the details, as it depends on what you consider to be complex enough to warrant writing raw SQL and how frequently those queries come up.

Portability

One issue with the raw SQL approach is it’s less portable. If you are using an ORM, that ORM often will be compatible with more than just the database you are currently working with.

This can mean small things like running sqlite locally and a different DB in production — or big things like making it easier to migrate your database to something else.

Again, your mileage may vary here — it’s really dependent on how much you care about this.

Use a query builder instead

Going back to the java ecosystem, a popular library is jOOQ. With jOOQ, you aren’t writing raw SQL, but it’s very close:

To me, this is great! My stated goal was just keeping the delta between my code and the generated SQL as little as possible, so query builders like jOOQ or Drizzle do a good job of keeping that delta small.

Not all query builders are made equal here, as I tend to dislike ones like Knex which have a larger delta.

Summary

• Raw SQL libraries like SQLx, PgTyped, and JDBI allow writing SQL directly while providing safety and type checking.

• These libraries aim to minimize the gap between code and executed SQL, with some offering benefits like compile-time checking and generated type interfaces.

• 대안으로는 SQL을 직접 작성하는 jOOQ 및 Drizzle과 같은 쿼리 빌더가 있지만 그 차이는 여전히 작습니다.

• DB 라이브러리를 선택할 때 고려해야 할 사항에는 이식성, 자세한 내용, 복잡한 쿼리와 간단한 CRUD 작업의 필요성 등이 있습니다.

위 내용은 원시 SQL을 안전하게 작성하기 위한 라이브러리의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!