Click this link sponsr.is/bootdev_dreamsofcode and use my code DREAMSOFCODE to get 25% off your first payment for boot.dev. That’s 25% off your first month or your first year, depending on the subscription you choose.
We decided to use sqlc a couple months ago and it is the best tool we have found, but I have two gripes with it: 1. You cannot easily create a drop in replacement interface for two different engines. We wanted to use PG in production and SQlite for testing. Even though the generated types etc are entirely identical they are, at the end of the day, different types. So what we did was write a hacky python script that copies all the parameters and models into a common package and updates the references in the subpackages. It works, but a native way for this would be nice. 2. It bleeds sql types. Every nullable column turns into a sql.NullXXX field instead of, say, a *string. If I wrote the repository myself, I would convert that to normal types before passing it along, but I have not found a way to do that with sqlc so far.
Damn this is perfect! I've heard of sqlc, but didn't understand it. I've also been writing all my own code for this, and its been making me feel super sluggish. Thank you for showing me this!
Amazing :) We were just talking with a colleague, how we hate codegen, but this tool actually looks nice!
Місяць тому
Again, thank you for your work! I've tried to convince some people on my team to use sqlc, they were not fully convinced despite my fantastic persuasion techniques. Guess I'll just share this video so they can finally understand what I extremely articulately tried to say/show at the time xD
Take a look into go-jet/jet. It is really fantastic, true type safe way to write sql. The thing that author shows in this video is just as retarded as any other. Jet is the future.
The BTW part really got me 💀Very relevant video as well, I'm using something called SQLBoiler right now which is one of the better ORMs I've used but I'm still feeling the limitations on a day to day basis especially any time joins are needed so I either end up writing a raw query anyway or querying each table one by one and joining manually, at which point why even use an ORM, or a relational database for that matter... luckily I'm at the point where I can still make a big change like this so I'm fairly sure I'll be converting to sqlc. Edit: and I did it already in a couple hours, it went pretty smooth actually (not surprising for a small application like mine) and I'm finding this really easy to use and elegant so far
Great video. I didn't know about sqlc, but I am definitely going to try it now. Thanks a lot for such great quality content. And yes, I would love to have that video about your backend stack :) Thanks once again!
Exactly, i'd like to see how you can have one func that supports conditional filtering e.g. find all players based on their class, or have particular item, or have gold above or below certain value. And full text search with ordering based on query relevance e.g. in postgres column 'value'
If you spend 5 years having to almost exclusively use 3rd party APIs (poorly documented) instead of traditional DBs, any opportunity to use SQL directly is a dream.
Funny how sqlc requires a whole other set of abstractions like sqlc.arg(parameter), making it arguably just as complicated and proprietary as other ORMs
Tbh I don‘t get your point here. An ORM is a runtime dependency, a lib you have to keep track of and hope that someone continues to maintain it. Sqlc is just a code generator to simplify your workflow. If no one continues to maintain it or you want to get rid of it you just stop using it and fallback to write it yourself (starting point of the vid). Just because you use ChatGPT and copy generated code into your app doesn‘t mean your app depends on ChatGPT or features AI…
@@OverG88 go-jet is a fantastic tool, the killer feature. I wonder why it is still not the most popular and eventually the standard way to work with SQL.
As a developer that constantly have to stop my self from writing raw sql queries, this look amazing. We have a cross language platfrom Python and Typescript maaaaaybe I could sneak it in make our code base a little dry'er.
The only gripe I have with it is that it creates a non-standard SQL. The sqlc.embed() for example makes the SQL query not actually SQL, which was the whole point of using sqlc and not an ORM.
Those queries templates not literal queries. I know most libraries for SQLs provide replacing $1 or :amount with provided parameters but it this any diffrent ? But you are right is should be orthogonal and specfics about arguments shoud be done like comment $1/*amount::bigint*/.
It's valid SQL syntax which is important to note. Just as valid as using different SQL flavor depending on which SQL engine you use. This engine just happens to go through sqlc before being passed onto your database directly. Consider the SQL being executed within a runtime that has a preprocessor that simply replaces those calls during execution. This is why they are called macros, because they are exactly that.
Bro it's a macro, it produces the same SQL you would've written without it. That's the whole point. And you aren't even forced to use it, nobody's stopping you from raw dogging the conflicting column names and writing boilerplate. It's just QoL. The main purpose of SQLc is to strike a balance between DX and control, and it does hit the spot quite well. You also aren't forced to opt in fully, you can use it for part of your queries, it doesn't affect the schema (unlike ORMs), you can still use regular SQL or other tools with it.
I went with querydsl, which is a similarly-minded api. Way lighter and clearer code than jpa/hibernate, whilst also having access to the full sql spec and db-specific dialect operations (try writing a dynamic join or an INSERT SELECT ... query with jpa ... lmao)
The company I work for tried sqlc, they contributed to the project both with code and funding. Its missing a lot of features. For example, dynamic quueries. Its been an open issue on the issue tracker for *years* now. We moved on to xo.
Consider using bingo to declaratively manage the version of SQL binary you're using at a project level. This is very nice to make the build time environment reproducible for anyone cloning the repair and also to manage the version of the tools in git.
I'm definitely won over by the prospect of less boilerplate, but my first thought was "how does it deal with transient db failures?" After looking through the GitHub issues, unfortunately the answer seems to be be "not very well." There's no explicit retry mechanism, and there's currently no means of adding custom middleware through which to implement common retry logic. So AFAICT implementing retries means wrapping calls, which replaces one kind of boilerplate with another.
ORM libraries have several advantages over sqlc. It is easier to use a different target (in-memory) database to speed up integration tests. They include several levels of caching to avoid running the same queries at high rates when data is not changing so often. They manage relationships quite well, especially when caching is enabled: data is loaded only when needed. They facilitate the integration of data encryption and decryption with a column based approach. They may include a validation step which prevent storing incoherent data like an off limit value. They can manage the update and validation of database schema, which is useful for A/B deployments where two releases of the same application can coexist for some time before discarding one of them.
@@kianyanglee4618 Hibernate is the de-fscto standard and includes all features, Eclipselink is a strong contender too if your main use is to provide REST of Web services to access a database. ORM is considered overkill for mobile applications.
There are also some inconvenience with this tool, it's impossible to make some filtering requests. For example if you have a search route in your API and you have to insert some WHERE statements in the SQL request. Like, select rows with "active = true" when user set that checkbox, and other filters. But yeah, in general this tool is very cool, and I've used it in the production, but had to switch to plain SQL or something like squirrel to be able to build more flexible requests
@@AK-vx4dyI think he means that unless the checkbox is set, you are ok with both active being true and active being false, otherwise you only want to get the active entires
First, you know the filters that may or may not be applied, so build the query with those in mind up front. Don't concatenate query strings to form a query dynamically, instead encode the filters into the query itself: For example `SELECT * FROM table WHERE (@name IS NULL OR name LIKE @name) AND (@age IS NULL OR age = @age) AND (@active IS NULL OR active = @active) ...` This gives you the freedom to choose how filters are applied in compound. The benefit of this is that the execution plan can be cached and re-used no matter what inputs are, so there's practically zero overhead unlike stitching random queries together which may be different each time
@dealloc you beat me to it - this is correct, and the other style of code that tacks on extra sql is harder to maintain. An alternative to doing it this way would be to make multiple queries with different where clauses and choosing which one conditionally in the code
@@orterves If you need to be so elastic (user provided filtering), indeed good(!) orm can be a better choice. But.. I'm not sure but maybe is possible to add sql.embbed(whole_where_section) and bulit this part in code ?
There's a Rust library called sqlx, which uses the language's macro system to verify SQL commands against your local database at compile-time, and it generates a compiler error if it fails.
just a heads up here, sqlc is real nice and its future looks promising but still has issues with more complex queries and things like dynamic filters make sure you check the kinds of queries you have are possible in sqlc before committing to it
Looks nice, it can save some time. But I personally don't want to use it. 1st reason - I came in Go for simple setup, but now I forced to dive into code generation logic of 3-rd party tool and use yaml config, sql macroses ant etc. I would rather ask chatgpt/claude generate repository methods and provide it Go structs and SQL schemas I made, and keep it as non-generated code. 2nd reason - tests, I usually split storage layer into multiple EntityRepository parts and test them separately against DB in docker container. It's easier to modify code manually than try to find exact config combination needed for code gen tool. 3rd reason - Go structs not always repeat table columns. These "models" generated by sqlc is not models at all, it's more like helper mapping structs for DB driver. Models, if we use this term in paradigm of clean code, should be free of `db`, `json` tags and don''t have sql field types because this is abstraction leak. If we use pgx, we may have case when you need pgxtype fields for proper mapping, but it's not often. In many cases you can reuse your app level models with small addition of private (storage level) structs for mapping to specific type. Sqlc rely on generated models, not on models defined on app level by developer.
Go is and always was very heavily leaning into code generation. So it's not really out of its idiomatic approach. I'd prefer to configure it in Go rather than a YAML file, but that's all.
Note: ossp-uuid should NOT be used for secure uuidv4 (the implementation details do not guarantee security). Instead gen_random_uuid() (from pgcrypto) should be used. HOWEVER, there are performance issues with all uuidv4 varations, and uuidv7 should be preferred (typically generated by the application on insert, but also available as a postgres function if default values are needed).
I'm sold on sqlc, but I don't think it's a 1:1 replacement for a repository/storage/db/whatever-layer. If you have pure domain models, you'd want to map to those inside of the db-layer before returning. The same goes if you have many-to-many relationships. It's simply not feasible to do all of that with just sqlc. The same goes for if you have dynamic queries. It's not really supported by sqlc in a good way and you will probably have to use something like strings.Builder allow for such queries,. You would want this code to live next to your other db-code. This approach also avoids macros (mostly) and keeps your queries very simple and straightforward.
or just use a strongly typed language. With an IDE with good suggestions and github copilot its so fast to spend time where it matters and leave boilerplate and setup to copilot. With Go this works perfectly. And with good design, sql queries wont be that complex either.
I really loved this tutorial. I saw sqlc tutorial before but was scared to use and I didnt get much out of it. but with this I really find it easy and have already started working on a personal project with this. Please also make a full backend app tutorial would love to learn more about golang thanks again
Im using sqlx and go migrator embedded into my app rn and I'm thinking about moving to sqlc + atlas. But there is one important feature missing to me: how can I migrate default data to my backend? E.g. I have a permissions table and I have certain base permissions. How can I migrate them? Should I use two different migration tools 🤔
I’ve never understood this false dichotomy of ORM vs Raw SQL. I’ve used a number of database abstraction layers that are not ORMs but don’t force you to write SQL either. They lean toward relational semantics making the code more like “writing SQL in Python” than “writing Python and pretending that the database is a big hash map” (mapping objects to relations).
@@NostraDavid2 yes the “low level” stuff in SQLAlchemy is totally inline with what I mean. I’m more of a PyDAL guy myself but when/where I can’t use it I’ve used SQLAlchemy and refuse to do the ORMy type things that others use like a crutch for not understanding relational semantics
That is very cool, but I think it's kinda mistake that they decided to stick with .sql extension. this is registered extension for DBMS-es and pure sql syntax. with all this new sugar from sqlc - it will make those query files unrunnable: you cannot easily debug them in the CLI or DB UI client, you need to traverse the file to understand how this works, etc.. other than that - pretty interesting approach. But do they have a LSP implementation?
Hey, great video! However I seem to be facing an issue while overriding types of UUID columns that are a foreign key reference to another table. The primary key field is `uuid.UUID`, but the foreign key field is still `pgtype.UUID`. Any idea where I might be missing something?
Btw I understand that I don't really need foreign keys if everything is a raw query and I can write joins myself, but I'm working with an existing schema here
If you jump on my discord and drop me a message I can take a look! Will need to see what your schema and query look like otherwise. I've managed to get a FK reference to work fine with my join table before.
People still write single table queries in 2024. Meanwhile Oracle Siebel CRM does joins and nested queries since 99 declarative and with relatively high performance
Repository pattern does not help you with safety but with decoupling code. "Under" your "repository pattern" still needs to be some SQL so if you feed some unchecked strings or SQL engine will not handle this, you are screwed.
Pani Szumlewicz w momencie dyskusji o ebookach i książkach ujawniła, że w sumie to jest solidnie poza tematem i włączyło jej sie coś na zasadzie pięćdziesięcioletniej polonistki, która się odkleiła i będzie w kółko mówić o tym co przeczytała i jakie książki są godne... Dalej dygresja o filozofii, z całym szacunkiem niczego nie udowadnia, poza tym, że wywołania pisane przez Panią wyprodukowała nie taki obrazek jaki oba sobie wyobrażała... 😅 Pani po prostu zupełnie nie rozumie tych konceptów co w sumie jest smutne w przypadku kogos kto zajmuję miejsce ekspera w takiej debacie
Off all the languages and framework you learn, SQL would be the easiest by far. Simple and straight forward syntax. Very explicit as well. Why do people keep on "reinventing" this? Stop overcomplicating SQL it is not hard.
SQLc sounds great, but it's missing a lot of things. I'm afraid they don't see enough of the picture to empower users to do a clean separation of concerns.
Click this link sponsr.is/bootdev_dreamsofcode and use my code DREAMSOFCODE to get 25% off your first payment for boot.dev. That’s 25% off your first month or your first year, depending on the subscription you choose.
I NEVER use protection.
please wait a little bit longer for us to read code instead switching to your face xD
We decided to use sqlc a couple months ago and it is the best tool we have found, but I have two gripes with it:
1. You cannot easily create a drop in replacement interface for two different engines. We wanted to use PG in production and SQlite for testing. Even though the generated types etc are entirely identical they are, at the end of the day, different types. So what we did was write a hacky python script that copies all the parameters and models into a common package and updates the references in the subpackages. It works, but a native way for this would be nice.
2. It bleeds sql types. Every nullable column turns into a sql.NullXXX field instead of, say, a *string. If I wrote the repository myself, I would convert that to normal types before passing it along, but I have not found a way to do that with sqlc so far.
You can add a `emit_pointers_for_null_types:true` property to solve your second issue, not sure for your first one though,
Why not use pglite? Or postgres via testcontainers?
Bro, To quote from The Twelve-factor App, the development and production environments should match as much as possible.
I have been using sqlc for two years now. It is definitely one of the best tools for interacting with a database.
was not friendly with arguments inside case statements so I switched to go jet, but yes one of the best in the biz as they say
the ORM/SQL guy's laptop of choice is inverted for engagement baiting.
i wanted to say same thing
No just admit you’re an SQL normie. Embrace the label 🐑.
😂😂😂
missed the banana cursor
Very interested in seeing your go backend development stack
Damn this is perfect! I've heard of sqlc, but didn't understand it.
I've also been writing all my own code for this, and its been making me feel super sluggish. Thank you for showing me this!
this looks extremely interesting, thanks for showing this
I am on vacation and wanted to look into sqlc for a project when I am back, so this is perfect timing
sqlc is just a must use tool to use with databases
Amazing :) We were just talking with a colleague, how we hate codegen, but this tool actually looks nice!
Again, thank you for your work! I've tried to convince some people on my team to use sqlc, they were not fully convinced despite my fantastic persuasion techniques. Guess I'll just share this video so they can finally understand what I extremely articulately tried to say/show at the time xD
Take a look into go-jet/jet. It is really fantastic, true type safe way to write sql. The thing that author shows in this video is just as retarded as any other. Jet is the future.
You convinced me to try sqlc. Thanks for showing us this!!
The BTW part really got me 💀Very relevant video as well, I'm using something called SQLBoiler right now which is one of the better ORMs I've used but I'm still feeling the limitations on a day to day basis especially any time joins are needed so I either end up writing a raw query anyway or querying each table one by one and joining manually, at which point why even use an ORM, or a relational database for that matter... luckily I'm at the point where I can still make a big change like this so I'm fairly sure I'll be converting to sqlc.
Edit: and I did it already in a couple hours, it went pretty smooth actually (not surprising for a small application like mine) and I'm finding this really easy to use and elegant so far
Great video. I didn't know about sqlc, but I am definitely going to try it now. Thanks a lot for such great quality content. And yes, I would love to have that video about your backend stack :)
Thanks once again!
The only thing missing is a better way to handle queries with dynamic parameters.
to-many joins?
Exactly, i'd like to see how you can have one func that supports conditional filtering e.g. find all players based on their class, or have particular item, or have gold above or below certain value.
And full text search with ordering based on query relevance e.g. in postgres column 'value'
Yep this hurts the most for endpoints that need to apply lots of optional filters
@@TheNamakool you can try case when statements with associated booleans to check if the filters are provided since zero values might be a problem
you can mock it with weird sql case.. conditions etc.
Also I love sqlc. It was the first option I chose when switching to Go. Great choice I see
If you spend 5 years having to almost exclusively use 3rd party APIs (poorly documented) instead of traditional DBs, any opportunity to use SQL directly is a dream.
perfect timing video lol I was looking into sqlc last night. wasn't sure if I wanted to just go with the standard go database/sql package or use sqlc
Funny how sqlc requires a whole other set of abstractions like sqlc.arg(parameter), making it arguably just as complicated and proprietary as other ORMs
@@bryanleebmy That’s why I use Jet.
@@OverG88 what’s that?
@@rodrigomaximilianobellusci8860 It’s a type safe SQL library for Go.
Tbh I don‘t get your point here. An ORM is a runtime dependency, a lib you have to keep track of and hope that someone continues to maintain it. Sqlc is just a code generator to simplify your workflow. If no one continues to maintain it or you want to get rid of it you just stop using it and fallback to write it yourself (starting point of the vid). Just because you use ChatGPT and copy generated code into your app doesn‘t mean your app depends on ChatGPT or features AI…
@@OverG88 go-jet is a fantastic tool, the killer feature. I wonder why it is still not the most popular and eventually the standard way to work with SQL.
As a developer that constantly have to stop my self from writing raw sql queries, this look amazing. We have a cross language platfrom Python and Typescript maaaaaybe I could sneak it in make our code base a little dry'er.
wth i was just at trader joes and they do NOT sell SQL spice. Absolutely livid
I want to see a video about your complete go stack!
Oh shit, hopefully a Gleam plugin gets made. Maybe I'll give it a shot.
Would love to see you talking about the migrations.
this is what the squirrel library for Gleam does which is pretty cool, except that one only supports postgres for now
The only gripe I have with it is that it creates a non-standard SQL. The sqlc.embed() for example makes the SQL query not actually SQL, which was the whole point of using sqlc and not an ORM.
Those queries templates not literal queries. I know most libraries for SQLs provide replacing $1 or :amount with provided parameters but it this any diffrent ?
But you are right is should be orthogonal and specfics about arguments shoud be done like comment $1/*amount::bigint*/.
It's valid SQL syntax which is important to note. Just as valid as using different SQL flavor depending on which SQL engine you use. This engine just happens to go through sqlc before being passed onto your database directly.
Consider the SQL being executed within a runtime that has a preprocessor that simply replaces those calls during execution. This is why they are called macros, because they are exactly that.
Yeah, kind of defeats its purpose.
Bro it's a macro, it produces the same SQL you would've written without it. That's the whole point. And you aren't even forced to use it, nobody's stopping you from raw dogging the conflicting column names and writing boilerplate. It's just QoL.
The main purpose of SQLc is to strike a balance between DX and control, and it does hit the spot quite well. You also aren't forced to opt in fully, you can use it for part of your queries, it doesn't affect the schema (unlike ORMs), you can still use regular SQL or other tools with it.
@@theseangle What so you mean by "produces"? If I can't take the string literally and execute it directly, it's not valid SQL.
I would love to see more about it.
7:30 *Woosh. Woosh. Woosh. Woosh*
For real; quite distracting. Otherwise great content, too.
i learned a lot and i'm going to try sqlc now, thanks! squeal ftw
In Java Spring Boot there is a solution for db first called JOOQ, it's impressive for any spring boot development
I went with querydsl, which is a similarly-minded api. Way lighter and clearer code than jpa/hibernate, whilst also having access to the full sql spec and db-specific dialect operations (try writing a dynamic join or an INSERT SELECT ... query with jpa ... lmao)
0:03 framework systems for the win yayy!!
The company I work for tried sqlc, they contributed to the project both with code and funding. Its missing a lot of features. For example, dynamic quueries. Its been an open issue on the issue tracker for *years* now. We moved on to xo.
I second this
Without dynamic queries it was of no use... at least for my projects: p Nice video btw.
Very useful video!
I am not sure for go but for Java developers, Jdbi brings this thing with more nicer abstraction as well
oh good catch, I didn't know about JDBI, thanks for sharing
That looks cool :) I'll try it tonight before I Hibernate :)
Consider using bingo to declaratively manage the version of SQL binary you're using at a project level. This is very nice to make the build time environment reproducible for anyone cloning the repair and also to manage the version of the tools in git.
love your sharing!
Yes, please show us your full stack
I'm definitely won over by the prospect of less boilerplate, but my first thought was "how does it deal with transient db failures?" After looking through the GitHub issues, unfortunately the answer seems to be be "not very well." There's no explicit retry mechanism, and there's currently no means of adding custom middleware through which to implement common retry logic. So AFAICT implementing retries means wrapping calls, which replaces one kind of boilerplate with another.
Very well done video... Thanks.
Thank you... for clearly explaining this helpful tool and for the moments that made me chuckle 🤭
ORM libraries have several advantages over sqlc.
It is easier to use a different target (in-memory) database to speed up integration tests.
They include several levels of caching to avoid running the same queries at high rates when data is not changing so often.
They manage relationships quite well, especially when caching is enabled: data is loaded only when needed.
They facilitate the integration of data encryption and decryption with a column based approach.
They may include a validation step which prevent storing incoherent data like an off limit value.
They can manage the update and validation of database schema, which is useful for A/B deployments where two releases of the same application can coexist for some time before discarding one of them.
Any ORM you would recommend?
@@kianyanglee4618 Hibernate is the de-fscto standard and includes all features, Eclipselink is a strong contender too if your main use is to provide REST of Web services to access a database.
ORM is considered overkill for mobile applications.
We have the same cursor! All hail the banana cursor 🙇
There are also some inconvenience with this tool, it's impossible to make some filtering requests. For example if you have a search route in your API and you have to insert some WHERE statements in the SQL request. Like, select rows with "active = true" when user set that checkbox, and other filters. But yeah, in general this tool is very cool, and I've used it in the production, but had to switch to plain SQL or something like squirrel to be able to build more flexible requests
Are you sure ? You can add WHERE in query with parameted $1 like "WHERE active=$1"
@@AK-vx4dyI think he means that unless the checkbox is set, you are ok with both active being true and active being false, otherwise you only want to get the active entires
First, you know the filters that may or may not be applied, so build the query with those in mind up front. Don't concatenate query strings to form a query dynamically, instead encode the filters into the query itself:
For example `SELECT * FROM table WHERE
(@name IS NULL OR name LIKE @name) AND
(@age IS NULL OR age = @age) AND
(@active IS NULL OR active = @active) ...`
This gives you the freedom to choose how filters are applied in compound.
The benefit of this is that the execution plan can be cached and re-used no matter what inputs are, so there's practically zero overhead unlike stitching random queries together which may be different each time
@dealloc you beat me to it - this is correct, and the other style of code that tacks on extra sql is harder to maintain.
An alternative to doing it this way would be to make multiple queries with different where clauses and choosing which one conditionally in the code
@@orterves If you need to be so elastic (user provided filtering), indeed good(!) orm can be a better choice.
But.. I'm not sure but maybe is possible to add sql.embbed(whole_where_section) and bulit this part in code ?
Did I see Zen Browser? LET'S GO!
nice video, thanks! subscribed
There's a Rust library called sqlx, which uses the language's macro system to verify SQL commands against your local database at compile-time, and it generates a compiler error if it fails.
@@michawhite7613 yeah the original library is sqlx from golang
I have a video on sqlx! I'm a big fan of it.
Thank you so much
skeptical! not sure if it makes things easier to debug and to maintain
I really would like to see your tech stack and the reason why you are using certain tools instead of other tools, for example migrate and goose
just a heads up here, sqlc is real nice and its future looks promising but still has issues with more complex queries and things like dynamic filters
make sure you check the kinds of queries you have are possible in sqlc before committing to it
Could we get a look at that go stack?
Bananas near anything related to code/coding always remind me of Joy of Code
Looks nice, it can save some time. But I personally don't want to use it. 1st reason - I came in Go for simple setup, but now I forced to dive into code generation logic of 3-rd party tool and use yaml config, sql macroses ant etc. I would rather ask chatgpt/claude generate repository methods and provide it Go structs and SQL schemas I made, and keep it as non-generated code. 2nd reason - tests, I usually split storage layer into multiple EntityRepository parts and test them separately against DB in docker container. It's easier to modify code manually than try to find exact config combination needed for code gen tool.
3rd reason - Go structs not always repeat table columns. These "models" generated by sqlc is not models at all, it's more like helper mapping structs for DB driver. Models, if we use this term in paradigm of clean code, should be free of `db`, `json` tags and don''t have sql field types because this is abstraction leak. If we use pgx, we may have case when you need pgxtype fields for proper mapping, but it's not often. In many cases you can reuse your app level models with small addition of private (storage level) structs for mapping to specific type. Sqlc rely on generated models, not on models defined on app level by developer.
Go is and always was very heavily leaning into code generation. So it's not really out of its idiomatic approach. I'd prefer to configure it in Go rather than a YAML file, but that's all.
select * is generally bad practice but in this case when total control of database is asssumed, i can grant absolution ;)
Dreams of code video ❤
Finnally someone mentioned sqlc
the banana cursor
Coming soon!
Note: ossp-uuid should NOT be used for secure uuidv4 (the implementation details do not guarantee security). Instead gen_random_uuid() (from pgcrypto) should be used. HOWEVER, there are performance issues with all uuidv4 varations, and uuidv7 should be preferred (typically generated by the application on insert, but also available as a postgres function if default values are needed).
100% sold
But for me the main advantage of writing your own SQL code is being able to use your preferred database specifics , like postgres jsonc etc
we know whats really in that SQL-spice tin 👌 just a pinch will do in a clinch
we would love to know your GO full-stack.. please make a video on it
great vid. SQLc was the first time I learned about SQL code-gen stuffs
p.s. does anyone know what is the color theme he's using?
Every orm allows for raw sql queries which are mostly protected by default.
ok you win You got me curious. What's your go dev stack?
It would be interesting to see how well this plays with instrumentation (opentelemetry). Can sqlc generate instrumented boilerplate?
I'm sold on sqlc, but I don't think it's a 1:1 replacement for a repository/storage/db/whatever-layer. If you have pure domain models, you'd want to map to those inside of the db-layer before returning. The same goes if you have many-to-many relationships. It's simply not feasible to do all of that with just sqlc. The same goes for if you have dynamic queries. It's not really supported by sqlc in a good way and you will probably have to use something like strings.Builder allow for such queries,. You would want this code to live next to your other db-code. This approach also avoids macros (mostly) and keeps your queries very simple and straightforward.
or just use a strongly typed language. With an IDE with good suggestions and github copilot its so fast to spend time where it matters and leave boilerplate and setup to copilot. With Go this works perfectly. And with good design, sql queries wont be that complex either.
Problem achieved! you've replaced boilerplate with even more boilerplate in two different uncommon languages!
"Strong raction" epic ;)
oh i didn't know about this override thing. Gotta check if this works with mysql too :)
Thanks
I really loved this tutorial. I saw sqlc tutorial before but was scared to use and I didnt get much out of it. but with this I really find it easy and have already started working on a personal project with this.
Please also make a full backend app tutorial would love to learn more about golang
thanks again
Thank you so much for the support! I'm glad you found value in the video!!
I absolutely will make a full backend app tutorial. 😁
I wrote code in Javascript. I usually use Kysely. It's query builder, I don't know if it's considered ORM or not...
not an ORM.. :)
Im using sqlx and go migrator embedded into my app rn and I'm thinking about moving to sqlc + atlas. But there is one important feature missing to me: how can I migrate default data to my backend? E.g. I have a permissions table and I have certain base permissions. How can I migrate them? Should I use two different migration tools 🤔
You can add default data into your migrations if you are guaranteed to need it!
I’ve never understood this false dichotomy of ORM vs Raw SQL. I’ve used a number of database abstraction layers that are not ORMs but don’t force you to write SQL either. They lean toward relational semantics making the code more like “writing SQL in Python” than “writing Python and pretending that the database is a big hash map” (mapping objects to relations).
Shout out to SQLAlchemy, which can do this! It's pretty nice!
@@NostraDavid2 yes the “low level” stuff in SQLAlchemy is totally inline with what I mean. I’m more of a PyDAL guy myself but when/where I can’t use it I’ve used SQLAlchemy and refuse to do the ORMy type things that others use like a crutch for not understanding relational semantics
that's the query builder pattern and it's what I also like best
How is this better than Spring JPA Repository named queries?
Nice idea, but this doesn't support any kind of dynamic order by clause, only static queries
Gave this a try just for funsies - overrides don't seem to actually work lol
Great Tools but one question is it Golang exclusive tool ?
No, that's also Python, Kotlin, TypeScript, Ruby, C# and F#
That is very cool, but I think it's kinda mistake that they decided to stick with .sql extension. this is registered extension for DBMS-es and pure sql syntax. with all this new sugar from sqlc - it will make those query files unrunnable: you cannot easily debug them in the CLI or DB UI client, you need to traverse the file to understand how this works, etc..
other than that - pretty interesting approach.
But do they have a LSP implementation?
Hey, great video! However I seem to be facing an issue while overriding types of UUID columns that are a foreign key reference to another table. The primary key field is `uuid.UUID`, but the foreign key field is still `pgtype.UUID`. Any idea where I might be missing something?
Btw I understand that I don't really need foreign keys if everything is a raw query and I can write joins myself, but I'm working with an existing schema here
If you jump on my discord and drop me a message I can take a look! Will need to see what your schema and query look like otherwise. I've managed to get a FK reference to work fine with my join table before.
Sqlx + bun is enough in 99% usecases and it’s way more simplistic
If you don't mind the feedback, could you reconsider the "whoosh" sound effect? I personally find it quite distracting.
Thanks for the feedback! I'll pass this on to my editor
after seeing the docs, i’d rather create my own
but again, this is my case not urs
waiting for your github repo link :)
Hope sqlc will also gen rust some day.
If you want a Rust-compatible tool, check out PRQL :)
People still write single table queries in 2024. Meanwhile Oracle Siebel CRM does joins and nested queries since 99 declarative and with relatively high performance
>no c++, elixir and forth support
What are your thoughts about SQLx and Turso/libSqlite if they come to the ecuation?
Eh feels like yet another technology to add to the overflowing tech stack that continues to overcomplicate Cicero
Repository pattern does not help you with safety but with decoupling code. "Under" your "repository pattern" still needs to be some SQL so if you feed some unchecked strings or SQL engine will not handle this, you are screwed.
Pani Szumlewicz w momencie dyskusji o ebookach i książkach ujawniła, że w sumie to jest solidnie poza tematem i włączyło jej sie coś na zasadzie pięćdziesięcioletniej polonistki, która się odkleiła i będzie w kółko mówić o tym co przeczytała i jakie książki są godne...
Dalej dygresja o filozofii, z całym szacunkiem niczego nie udowadnia, poza tym, że wywołania pisane przez Panią wyprodukowała nie taki obrazek jaki oba sobie wyobrażała... 😅
Pani po prostu zupełnie nie rozumie tych konceptów co w sumie jest smutne w przypadku kogos kto zajmuję miejsce ekspera w takiej debacie
Those Swoosh sounds when doing an animation are way to bassy and loud for my liking
Thanks for the feedback!
What about flyway and liquibase?
Hi, what is the theme you are using?
Quick question what font and color scheme are you using ?
Off all the languages and framework you learn, SQL would be the easiest by far. Simple and straight forward syntax. Very explicit as well. Why do people keep on "reinventing" this? Stop overcomplicating SQL it is not hard.
how do u handle validation if its setup this way?
i don't wanna rewrite the structs just to add the bindings
I usually handle validation above this layer, typically during the http handler.
SQLc sounds great, but it's missing a lot of things. I'm afraid they don't see enough of the picture to empower users to do a clean separation of concerns.