sqlx VS go-sqlite3

> The sane middle ground is libraries that give you nicer ergonomics around SQL without hiding it (like Golangs sqlx https://github.com/jmoiron/sqlx). Engineers should be writing SQL, period.

The blog suggests that an ORM for OLAP would do exactly that

sqlx builds on Go’s database/sql and adds useful features like named queries, struct mapping, and scanning slices.

SQLx isn’t a migration tool per se, but it’s a powerful library for working with SQL in Go. You can build a custom migration system using SQLx to execute migration scripts, giving you ultimate flexibility. This approach is best for teams who want full control over their migration logic.

4. Infrastructure Layer — internal/infra/ This layer implements database logic using go-jet and sqlx. It satisfies the interfaces defined in the domain layer.

### When to Use Viper Use Viper for **applications with complex configurations**, like microservices or CLI tools. For simple scripts, environment variables alone might suffice. ## Comparing the Libraries Here’s a quick table to help you decide which library fits your needs. | Library | Use Case | Key Feature | Performance | Learning Curve | |---------|----------|-------------|-------------|----------------| | Gin | Web APIs | Routing | High | Low | | Testify | Testing | Assertions | N/A | Low | | Zap | Logging | Structured | High | Medium | | GORM | Databases| ORM | Medium | Medium | | Viper | Config | Flexibility | N/A | Low | ## Tips for Getting Started - **Start Small**: Try one library at a time in a side project. - **Read Docs**: Each library has excellent documentation (linked above). - **Join Communities**: Check GitHub issues or Go forums for tips. - **Experiment**: Use the example code as a base and tweak it. ## What’s Next? These libraries are just the tip of the iceberg. Once you’re comfortable, explore others like [Cobra](https://github.com/spf13/cobra) for CLI tools or [sqlx](https://github.com/jmoiron/sqlx) for lightweight database access. The Go ecosystem is vast, and combining these tools can help you build robust, scalable applications. Pick one library, try the example, and see how it fits your workflow. Happy coding!

Go offers quick database interactions that result in fast performance. It provides both native database drivers and sqlx for simplified querying. As you have seen in this article, you have used native database driver, SQL just by importing a package straight from GitHub. Similarly, you can use sqlx to have reduced boilerplate and more built-in features like struct mapping.

sqlx for the database driver.

We've always used https://github.com/jmoiron/sqlx which is just the standard package + mapping to/from structs.

You can use this https://github.com/jmoiron/sqlx

SQLite encryption at-rest is a hot requested feature of both the “default” CGo driver [1] and the transpiled alternative driver [2]. So, this is a feature I wanted to bring to my own Wasm based Go driver/bindings [3].

Open-source SQLite encryption extensions have had a troubled last few years. For whatever reason, in 2020 the (undocumented) feature that made it easy to offer page-level encryption was removed [4]. Some solutions are stuck with SQLite 3.31.1, but Ulrich Telle stepped up with a VFS approach [5].

Still, their solution seemed harder than something I'd want to maintain, as it requires understanding the structure of what's being written to disk at the VFS layer. So, I looked at full disk encryption for something with less of an impedance mismatch.

Specifically, I'm using the Adiantum tweakable and length-preserving encryption (with 4K blocks, matching the default SQLite page size), and encrypting whole files (rather than page content).

I'm not a cryptographer, so I'd really appreciate some roasting before release.

There is nothing very Go specific about this (apart from the implementation) so if there are no obvious flaws, it may make sense to port it to C/Rust/etc and make it a loadable extension.

[1] https://github.com/mattn/go-sqlite3/pull/1109

for what it's worth, the two pool approach is suggested here by a collaborator to github.com/mattn/go-sqlite3: https://github.com/mattn/go-sqlite3/issues/1179#issuecomment...

SQLite is great. I've also recently settled on it as a key-value store, after considering a few purpose-built key-value solutions. Turns out that it's really easy to make SQLite work as a key-value store, but very difficult to make key-value stores relational.

Just be careful with `:memory:` databases. From the mattn/go-sqlite3 FAQ[1]:

> Each connection to ":memory:" opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?cache=shared" (or "file:foobar?mode=memory&cache=shared"). Every connection to this string will point to the same in-memory database.

I noticed strange behaviors with just `:memory:` where tables would just disappear at random, and this workaround helped. Make sure to use a unique filename as the `file:` value, especially if using this in tests.

[1]: https://github.com/mattn/go-sqlite3#faq

github.com/mattn/go-sqlite3

For building my web server, I chose to use the Gin framework as the foundation of my app. It was incredibly easy to understand and work with, and I was pleasantly surprised by how seamlessly it integrated with writing unit tests for the server. To handle the database, I leveraged the power of go-sqlite and migrate for efficient SQL queries and migrations. These libraries proved to be both powerful and user-friendly, making the development process a breeze.

https://github.com/mattn/go-sqlite3/blob/master/_example/sim...

SQLite does have an optional "user authentication" extension, though I've not personally tried it out:

https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth....

The widely used Go SQLite library by mattn says it supports it, if that's useful:

https://github.com/mattn/go-sqlite3#user-authentication

I have an OSS project, sq which is a data-wrangling swiss-army knife for structured data. Think of it as jq for databases. It supports Postgres, SQLServer, MySQL and - relevantly - SQLite. It embeds SQLite via CGo and the mattn/go-sqlite3 driver.