5 ms·

Getting very close in my large side projects for everything to just be Go + PostgreSQL. Go is the API, business logic, and very soon the entire web layer. This

by buro9 5y ago

Getting very close in my large side projects for everything to just be Go + PostgreSQL. Go is the API, business logic, and very soon the entire web layer. This is all on Linode, there are no containers I just put a binary on an LTS Ubuntu and then iptables to just those ports (with SSH configured in their Cloud Firewall to only be possible from my home IP). I have a long term itch that I want to scratch soon, whether it's possible to dispose of PostgreSQL and just store my data in an S3 compatible object store and yet still have a SQL interface to it. https://blevesearch.com/ https://blevesearch.com/ is on my list of things to look into for this. I'd already structured the PostgreSQL schema with the possibility of putting individual tables into an object store, but as my databases grow I'm keen to avoid becoming a DBA and I don't use most PostgreSQL features so if I have the chance to reduce my tech stack to just Go I may take it. What I've learned from having a mix of Python + Django + some NPM... bitrot and maintaining dependencies is a debt that you have to start paying way too soon. Originally only the API was in Go, but that is the one part that over years has required virtually no debt paydown due to the language, environment, dependencies. You can leave a Go application for 8 years and all you need do is compile it to the latest Go and everything works perfectly... I can't even run or upgrade Python apps from half that time ago without major work to bring it up to date. As the side projects proliferated or grew, it is less overall effort for me to rewrite in Go than maintain everything else.

justsomeuser 5y ago
Opinion: Don’t put your business data in s3 objects! It can work if all you will ever need is a key value store, but as soon as you need SQL-like queries (joins, filtering, transactions) you will be stuck reimplementing a sql engine in your app code. I recommend to try SQLite with some kind of replication (either Linode disk snapshots, Litestream etc). Having your state observable and local to your application instead of a slow http hop away makes development much easier.
- cweagans 5y ago
  +1 for SQLite. You might take a look at https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite if you really need replication. SQLite can go pretty far on its own though.
  - otoolep 5y ago
    rqlite author here, happy to answer questions about it. I should note that rqlite is a distributed database that uses SQLite as its database engine. This means in practice it does replicate a SQLite database to each node on the rqlite cluster -- but it's not a SQLite replication system per-se. https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md
- Hermitian909 5y ago
  > you will be stuck reimplementing a sql engine in your app code. Only if you're intent on keeping the s3-only architecture when it no longer makes sense, and the transition to SQL need need not be hard if you've been disciplined about your data structures from the get go. I've been part of a transition from s3 only to s3/sql when business requirements changed, it only took us a week to set up a db, set up a workflow manager to insert necessary data into the db when something uploaded to s3, and ran some batch jobs for data that already existed. Total data in s3 was ~1pb. IMO it's all about whether your problem space fits well with s3-only, and sometimes it does!
- Terretta 5y ago
  You might not have to make a SQL engine immediately. - S3 Select: https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectObjectContent.html https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectOb... - Athena: https://towardsdatascience.com/query-data-from-s3-files-using-aws-athena-686a5b28e943 https://towardsdatascience.com/query-data-from-s3-files-usin...
  - anothernewdude 5y ago
    Athena is a bad tool that if you have too much data or your queries take too long will just error out and give you empty results, and charge you for the privilege. It's cheaper and less pain to host your own Presto if you need it. How much is too much data? It depends. On things you won't be told. Varies day to day. Totally random.
- deleted 5y ago
  [deleted]
jjice 5y ago
How do you handle DB interactions in Go? I've been interested in how people choose between ORMs and hand written wrappers and how they structure them recently.
- buro9 5y ago
  Manual via https://pkg.go.dev/database/sql https://pkg.go.dev/database/sql with handwritten SQL in the majority of places... but with a wrapper to handle the more complex search scenarios. For example these from a multi-tenant SaaS forum platform (it's old, but it's also OSS so I can show you)... This helper to get connections: https://github.com/microcosm-cc/microcosm/blob/master/helpers/db.go https://github.com/microcosm-cc/microcosm/blob/master/helper... used like this for inserts: https://github.com/microcosm-cc/microcosm/blob/master/models/conversations.go#L191-L225 https://github.com/microcosm-cc/microcosm/blob/master/models... and this for reads https://github.com/microcosm-cc/microcosm/blob/master/models/conversations.go#L436-L487 https://github.com/microcosm-cc/microcosm/blob/master/models... . But searches... i.e. highly consistent SELECT queries with different WHERE statements (and potentially FROM statements), then in each project I tend to have an idea of a search struct ( https://github.com/microcosm-cc/microcosm/blob/master/models/search_query.go https://github.com/microcosm-cc/microcosm/blob/master/models... ) that will validate the inputs and represent the search query, and then something that will consume that and build the SQL for it ( https://github.com/microcosm-cc/microcosm/blob/master/models/search_metadata.go https://github.com/microcosm-cc/microcosm/blob/master/models... ). This isn't pretty... but it's easy for me to tune, debug, and keeps the rest of the code base very maintainable... all the complexity is here in the search. The vast majority of SQL is very very simple and needs no ORM, and the complexity is just in the search scenario where I want to be able to tune the performance more than an ORM would allow me to do so.
  - ggregoire 5y ago
    Is there a way to write and import the queries from a SQL file in Go? Like HugSQL (Clojure) or PugSQL (Python). To benefit from the SQL tooling (syntax highlighting, autocomplete and so on).

by buro9 5y ago

justsomeuser 5y ago

Opinion: Don’t put your business data in s3 objects! It can work if all you will ever need is a key value store, but as soon as you need SQL-like queries (joins, filtering, transactions) you will be stuck reimplementing a sql engine in your app code. I recommend to try SQLite with some kind of replication (either Linode disk snapshots, Litestream etc). Having your state observable and local to your application instead of a slow http hop away makes development much easier.

cweagans 5y ago
+1 for SQLite. You might take a look at https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite if you really need replication. SQLite can go pretty far on its own though.
- otoolep 5y ago
  rqlite author here, happy to answer questions about it. I should note that rqlite is a distributed database that uses SQLite as its database engine. This means in practice it does replicate a SQLite database to each node on the rqlite cluster -- but it's not a SQLite replication system per-se. https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md
Hermitian909 5y ago
> you will be stuck reimplementing a sql engine in your app code. Only if you're intent on keeping the s3-only architecture when it no longer makes sense, and the transition to SQL need need not be hard if you've been disciplined about your data structures from the get go. I've been part of a transition from s3 only to s3/sql when business requirements changed, it only took us a week to set up a db, set up a workflow manager to insert necessary data into the db when something uploaded to s3, and ran some batch jobs for data that already existed. Total data in s3 was ~1pb. IMO it's all about whether your problem space fits well with s3-only, and sometimes it does!
Terretta 5y ago
You might not have to make a SQL engine immediately. - S3 Select: https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectObjectContent.html https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectOb... - Athena: https://towardsdatascience.com/query-data-from-s3-files-using-aws-athena-686a5b28e943 https://towardsdatascience.com/query-data-from-s3-files-usin...
- anothernewdude 5y ago
  Athena is a bad tool that if you have too much data or your queries take too long will just error out and give you empty results, and charge you for the privilege. It's cheaper and less pain to host your own Presto if you need it. How much is too much data? It depends. On things you won't be told. Varies day to day. Totally random.
deleted 5y ago
[deleted]

jjice 5y ago

How do you handle DB interactions in Go? I've been interested in how people choose between ORMs and hand written wrappers and how they structure them recently.

buro9 5y ago
Manual via https://pkg.go.dev/database/sql https://pkg.go.dev/database/sql with handwritten SQL in the majority of places... but with a wrapper to handle the more complex search scenarios. For example these from a multi-tenant SaaS forum platform (it's old, but it's also OSS so I can show you)... This helper to get connections: https://github.com/microcosm-cc/microcosm/blob/master/helpers/db.go https://github.com/microcosm-cc/microcosm/blob/master/helper... used like this for inserts: https://github.com/microcosm-cc/microcosm/blob/master/models/conversations.go#L191-L225 https://github.com/microcosm-cc/microcosm/blob/master/models... and this for reads https://github.com/microcosm-cc/microcosm/blob/master/models/conversations.go#L436-L487 https://github.com/microcosm-cc/microcosm/blob/master/models... . But searches... i.e. highly consistent SELECT queries with different WHERE statements (and potentially FROM statements), then in each project I tend to have an idea of a search struct ( https://github.com/microcosm-cc/microcosm/blob/master/models/search_query.go https://github.com/microcosm-cc/microcosm/blob/master/models... ) that will validate the inputs and represent the search query, and then something that will consume that and build the SQL for it ( https://github.com/microcosm-cc/microcosm/blob/master/models/search_metadata.go https://github.com/microcosm-cc/microcosm/blob/master/models... ). This isn't pretty... but it's easy for me to tune, debug, and keeps the rest of the code base very maintainable... all the complexity is here in the search. The vast majority of SQL is very very simple and needs no ORM, and the complexity is just in the search scenario where I want to be able to tune the performance more than an ORM would allow me to do so.
- ggregoire 5y ago
  Is there a way to write and import the queries from a SQL file in Go? Like HugSQL (Clojure) or PugSQL (Python). To benefit from the SQL tooling (syntax highlighting, autocomplete and so on).