151–160 of 160 posts
Earlier quoted context omitted.
C has few enough restrictions though that you can for example make a struct and then make an array of that struct. In Go this is like rocket science.
Which one are you struggling with? https://play.golang.org/p/P8L0lSMhNgF https://play.golang.org/p/E8rM7JdrfkD
I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code: - Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen). - Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm) - Write Go structs, generate SQL schema from the structs, and use a custom query DSL (prote…
> - Write custom query language (YAML or other), generate SQL schema, queries, and Go query interface (xo). I've also tried to unify this approach with gRPC/Protobuf messages and CRUD operations: https://github.com/sashabaranov/pike/
Earlier quoted context omitted.
Yep, close to 100% of my data manipulation is done in pl/pgsql. It’s awesome. At least 50% fewer LOC, and 10-100x faster than the equivalent code written in Java or Go, due to all the round trips.
Oh yeah, I completely forgot to mention that. The performance gains are immense when you use Pl/pgSQL to eliminate round-trips to the database. That's easily one of the most important reasons to use Pl/pgSQL. The vast majority of data-heavy web apps today must have the database running on the same server or within the same datacenter -- they can't tolerate any kind of latency between the application and the database…
With plpgsql you define your schema once, in SQL alone; you don't write a million duplicate "entity objects" in your language of choice, there is no friction or "impedance mismatch", no need to catch network errors for each DB call -- you just write SQL and return values like any other Go function. Because my functions are generally self-contained, I rarely even need to bother with transaction management, which eliminates even more round trips.
It's true that I needed to write some supporting code to manage schema upgrades (one day I hope to open source it), and I'm really intrigued to see if I can use sqlc to create Go stubs for my PG functions. But my SQL code sits next to my Go code in my IDE, it's syntax and correctness checked by the GoLand IDE, and life with an SQL database is super enjoyable!
I'm looking forward to integrating plpgsql_check into my build chain.
I've used https://github.com/xo/xo , extended it with some custom functions for templating, extended the templates themselves, and can now generate CRUD for anything in the database, functions for common select queries based on the indices that exist in the database, field filtering and scanning, updates for subsets of fields including some atomic operations, etc. The sky is the limit honestly. It has allowed me to s…
I agree whole-heartedly that writing SQL feels right. Broadly speaking, you can take the following approaches to mapping database queries to Go code: - Write SQL queries, parse the SQL, generate Go from the queries (sqlc, pggen). - Write SQL schema files, parse the SQL schema, generate active records based on the tables (gorm) - Write Go structs, generate SQL schema from the structs, and use a custom query DSL (prote…
Earlier quoted context omitted.
I did this on a recent project, and it worked really well. I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. Which meant managing versions was easy (coupled with migration .sql files). I also got to find out if any of my SQL was broken right up front, and testing the SQL was simple - call the function and check the return. I also defined…
Could you please give 2 specific examples on how this would work? Are the functions only for UPDATE/INSERT, or also for reading data? I'm using views to simplify queries, but still via ORM.
Functions are for both reading and modifying data, because the actual SQL for the query might be complex (and therefore better managed as a function than as a string in Go code).
Example: User CRUD
create view vw_user as select id, name, email from users;
create function fn_get_user(p_user_id uuid) returns vw_user as $$ select * from vw_users where id = p_user_id $$;
create function fn_change_user_name(p_user_id uuid, p_name text) returns vw_user as $$ update users set name = p_name where id = p_user_id; select * from vw_users where id = p_user_id; $$
create function fn_create_user(p_name text, p_email text) returns vw_user as $$ insert into users (id, name, email) values (gen_random_uuid(), p_name, p_email); select * from vw_users where id = p_user_id; $$
Each function maps 1:1 to a Go function that calls it, though it's also possible/easy to have more complex Go functions that call more than one db function. Or indeed, meta-functions that call other functions before returning a value to the Go code.
The problem with ORMS is always that eventually the mapping between struct and database breaks, and you end up having to do some funky stuff to make it work (and from then on in it gets increasingly complex and difficult). The structures in the database are optimised for storing the data. The structures in the Go code need to be optimised for the business processes (and the structures in the UI are optimised for that, so they will be different again). An ORM ignores all of this and assumes that everything maps 1:1. Maintaining those relationships manually (rather than in an ORM) does involve some boilerplate, but it allows you to keep the structures separate.
For example: User UI data:
create view vw_ui_user as select users.id as user_id, users.name, users.email, sessions.id as session_id, max(sessions.when_created) as last_logged_in from users left join sessions on users.id = sessions.user_id group by 1,2,3,4;
create function get_ui_user(p_email text) returns json as $$ select to_json(select * from vw_ui_user where email = p_email);$$
caveat I didn't bother checking this for syntax or typos. I have probably made several errors in both.
Earlier quoted context omitted.
Could you please give 2 specific examples on how this would work? Are the functions only for UPDATE/INSERT, or also for reading data? I'm using views to simplify queries, but still via ORM.
Sure, why not. Functions are for both reading and modifying data, because the actual SQL for the query might be complex (and therefore better managed as a function than as a string in Go code). Example: User CRUD create view vw_user as select id, name, email from users; create function fn_get_user(p_user_id uuid) returns vw_user as $$ select * from vw_users where id = p_user_id $$; create function fn_change_user_name…
Earlier quoted context omitted.
Thanks a lot for this great project. I looked in the issues for Sqlite support and saw the merge of PR to "Add three new experimental engines, including SQLite" [0] and there's major architecture changes involved. That merge was 1.5 yr ago though, and I am curious what the plans are to take that further. [0] https://github.com/kyleconroy/sqlc/pull/331
I haven't written up a public roadmap yet as I'm still focused on improving the MySQL and PostgreSQL support. While there is technically a SQLite parser in the main tree, it's substantially lower quality than the others. This is due to the fact that it's generated using Bison and not used by any else in production. SQLite uses a custom parser generator called lemon[0] to parse SQL queries. Sadly that parser is deeply…
Earlier quoted context omitted.
Two problems 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’ 2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax…
> 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’ Writing UDFs and using Pl/pgSQL has no impact on how you do versioning. At my company we follow standard Gitflow and use golang-migrate for schema migrations (or Phinx for our PHP code bases). If you're…
Is this the same as concatenating strings or is there some special PL/pgSQL support for this?