Which one to use postgres vs. sqlx

1

I came across two modules to query Postgres directly using sql: postgres vs. sqlx, I am not clear which one I should use. I found there are more sqlx examples online, also it has a connection pool.

1 Like
2

That depends on:

  1. Whether your code is sync or async. If it's sync, you'll want to use postgres (or diesel). If it's async, use sqlx
  2. If your code is sync, choose postgres if that's the only db you'll want to interact with, and diesel if you also want to interact with eg sqlite or mysql db's.

Also note that for both diesel and sqlx queries are checked at compile time, I'm not sure if this is the case for postgres, but I suspect it does no compile time query validation.

There's a bunch more factors to consider (which library introduces the least amount of overhead? Which one scales better in number of concurrent users? etc) but if the goal is just get started on something for a personal project, then the list at the top of this post is probably sufficient.

3 Likes
3

Note that for async you can also use tokio-postgres.

2 Likes
4

@jjpe, thank you for the information! when you say checked at compile-time, do you mean the compiler will resolve the variables and construct the queries?

in terms of pool management, seems postgres doesn't have anything built-in. diesel and sqlx have pool management supports.

5

@alice, noticed, thanks!

6

The sqlx library includes some code that it runs during compile time to verify that an SQL query is valid syntax. This is part of its macro.

Yes, for libraries that don't include pool management, you typically want bb8 for async code or r2d2 for non-async code.

2 Likes
7

To put the obvious disclaimer first: I'm one of diesels maintainers, so my views may be a bit biased.

As already mentioned: diesel and sqlx provide some sort of compile time checking for your queries. sqlx does this by connecting to the database and "outsourcing" the checking to the database, while diesel uses the type system to provide a dsl that makes it impossible to construct invalid queries. Both approaches have advantages and disadvantages, so let me list some:

  • sqlx
    • Advantages:
      • Provides relatively clean error messages
    • Disadvantages:
      • Requires a database server running at build time (there is an option to use a cache for this, but at some point you need a running database server)
      • Is only able to check "static" queries. So anything that depends on parameters only available at run time cannot be checked. This includes things like batch inserts, IN statements and so on
      • Connecting to a database to check each query at compile time may have a serve impact on compile times
  • diesel:
    • Advantages:
      • Dsl based approach allows also to check "dynamic" queries (so things like IN statements are also checked at compile time)
      • The checking is not only done for complete queries, but also for query parts. This allows you to reuse smaller parts in different queries
    • Disadvantages:
      • Error messages may be confusing at first, as they contain quite large types
      • Type checking can have an minor impact on compile times

The postgres and the tokio-postgres crate does not provide any sort of compile time checking. Another important point may be performance. postgres/tokio-postgres and diesel are quite similar here, while sqlx seems to be about 2 times slower according to our benchmarks.

6 Likes
8

I wasn't aware of this. And it's quite unfortunate because I was planning to use sqlx in an async project. But if perf is this far apart I'll have to use diesel anyway I guess.

9

At least in my experience an async database library is not that important, because your backend will likely be limited by the number of open database connection (== number of queries you can process in parallel) much earlier than by anything else. This means having an async connection pool that just waits till a connection is available + running the actual queries on the blocking part of the executor should do the trick for almost all use cases.
Deadpool provides support for diesel based connections for example.

1 Like
10

@alice , thank you!

11

@weiznich , thank you for the detailed answers. in my case, i only need to run a few simple queries, i am much concerned about performance (a query might return millions of records) and connection pool management (might run hundreds of threads). seems a direct sql approach is better than using an ORM like diesel.

12

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.