go-sqlite3 VS GORM

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.

IDE: use whatever make you productive. I personally use vscode. VCS: git, as golang communities use github heavily as base for many libraries. AFAIK Linter: use staticcheck for linting as it looks like mostly used linting tool in go, supported by many also. In Vscode it will be recommended once you install go plugin. Libraries/Framework: actually the standard libraries already included many things you need, decent enough for your day-to-day development cycles(e.g. `net/http`). But here are things for extra: - Struct fields validator: validator - Http server lib: chi router , httprouter , fasthttp (for non standard http implementations, but fast) - Web Framework: echo , gin , fiber , beego , etc - Http client lib: most already covered by stdlib(net/http), so you rarely need extra lib for this, but if you really need some are: resty - CLI: cobra - Config: godotenv , viper - DB Drivers: sqlx , postgre , sqlite , mysql - nosql: redis , mongodb , elasticsearch - ORM: gorm , entgo , sqlc(codegen) - JS Transpiler: gopherjs - GUI: fyne - grpc: grpc - logging: zerolog - test: testify , gomock , dockertest - and many others you can find here

GORM is a comprehensive ORM tool in Go, offering a code-first approach which allows defining database schemas using struct tags in Go. It's known for its developer-friendly nature, making it suitable for both beginners and experienced users. GORM supports a variety of SQL databases like MySQL, PostgreSQL, and SQLite. It's designed to be flexible, allowing developers to drop down to raw SQL when necessary. However, it's important to be cautious about its performance implications in large-scale applications.

Homepage: https://gorm.io/

Since most of these APIs will be simple CRUD (Create/Read/Update/Delete) endpoints, let's build this service using GORM, an ORM library that makes building CRUD endpoints really simple.

After successfully configuring the application, it's time to delve into integrating the data layer. For this purpose, I will utilize gorm, a powerful SQL ORM that facilitates rapid development of the data layer using model structs.

For basic INSERT ... VALUES ... or SELECT ... WHERE ... JOIN ..., use a library such as GORM. For INSERT .... SELECT ... statements where one combines two round trips (SELECT and INSERT) into one, ORMs have a hard time performing this query. Particularly when you start doing joins. Joins are the heart of Relational database theory (they denote relationships). So get to know what an INNER, LEFT, RIGHT and OUTER join is and why you would use them. Also learn INSERT ... SELECT ...

I have never hated gorm and it serves me well. However I tend to feed it raw SQL very often.

Array of values with embedded value all pointed to the last value, reflect code was broken: https://github.com/go-gorm/gorm/pull/5901 data corruption