4 ms·

I disagree with the author on few points. SQL is not terrible when you get used to it. They conflate database functions with triggers. It's very easy to restr

by xupybd 4y ago

I disagree with the author on few points. SQL is not terrible when you get used to it. They conflate database functions with triggers. It's very easy to restrict your database code to pure functions. It is possible to keep database functions in git. It requires some discipline but it's not hard. Migration tools like Flyway help keep track of db function changes. At small single server scale db functions can be much faster for fetching data. Tools such as Postgrest prove that you can do much of your crud work in DB functions.

idoubtit 4y ago
> It is possible to keep database functions in git. It requires some discipline but it's not hard. My experience differs. How do you check that the stored procedures in the DB are exactly as defined in Git? I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. With a compiled executable, consistency is guaranteed. With interpreted code (Python, PHP), consistency check is done with a command like `git status`. But there is no standard way to compare the state of store procedures in the DB with their expected code in versioned files.
- xupybd 4y ago
  The solution is that nothing gets to production unless it gets migrated in via the CI pipeline. Admittedly it's still a problem during development. But a tool like Flyway versions your DB. You know if you are out of date as the migration table is behind. It's not as good as compiled code but it works when you need it.
- yobbo 4y ago
  > How do you check that the stored procedures in the DB are exactly as defined in Git? How do you know that a binary is produced by some code in git? Because you built it. Same principle applies. But also, the db can list its code.
- Smeevy 4y ago
  > I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. If you have everything under version control you can just reapply that version's stored procedures off the database scripts. If someone or something is not handling failures in deployment scripts (not just database migrations) then you get that sort of problem.
- cafard 4y ago
  I have done this with both T-SQL and PL/SQL. The stored code is all available in system views and easily extracted with a script. If you set up the script to run automatically at a given time, you know that the version in git is at most n hours out of date. If you leave the control up to the scheduled job, then your commit messages may not be what you want. But there's nothing keeping you from running your extraction script off schedule and then writing a good commit message.
- gifflar 4y ago
  We store our database functions in SQL scripts that are automatically executed with every Flyway migration, so-called callback scripts. In the callback script every function is declared to be created or recreated. As Flyway is executed with each application start, the database functions are recreated with every application start, even if they didn’t change. This approach has its pros and cons but it ensures that database functions always match the version of the application code. Because the callback script of a database function is stored in a file that is edited when needed, the Git history works the same as for application code.
kumarvvr 4y ago
I agree with all your points, except for SQL. Getting lost in SQL is easy, due to its syntax and lack of standards. Compare that to any of the mainstream programming languages, the difference in cognitive load to process is stark.
- xupybd 4y ago
  It might be Stockholm syndrome but I find SQL to be one of the best DSLs I know. It's only good at querying relational data but it's good at that. I might have spent too much time in SQL but it one of my favorites.
  - kumarvvr 4y ago
    I always live in the fear of accidentally destroying data or tables with SQL. Not a rational fear, just an irrational one. A primitive fear of the unknown. But, to be fair, I have had to work with some of the worst SQL constructs and stored procedures. shudder
- whynotmaybe 4y ago
  I want to thank you for saying you don't like SQL. Now in your future, please tell it to everyone involved where you work so that you won't work on it and they'll find someone else to do the SQL. I've worked at place where people hated SQL and where hiding it. Instead of crafting nice and performing SQL, they wrote "select * from table" and then did foreach loops in the code to filter the results they wanted.
addicted 4y ago
Isn’t the author talking about procedural SQL? Regular SQL is just fine. Great even. But procedural SQL truly sucks and is highly inconsistent between DBs.

by xupybd 4y ago

idoubtit 4y ago

> It is possible to keep database functions in git. It requires some discipline but it's not hard. My experience differs. How do you check that the stored procedures in the DB are exactly as defined in Git? I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. With a compiled executable, consistency is guaranteed. With interpreted code (Python, PHP), consistency check is done with a command like `git status`. But there is no standard way to compare the state of store procedures in the DB with their expected code in versioned files.

xupybd 4y ago
The solution is that nothing gets to production unless it gets migrated in via the CI pipeline. Admittedly it's still a problem during development. But a tool like Flyway versions your DB. You know if you are out of date as the migration table is behind. It's not as good as compiled code but it works when you need it.
yobbo 4y ago
> How do you check that the stored procedures in the DB are exactly as defined in Git? How do you know that a binary is produced by some code in git? Because you built it. Same principle applies. But also, the db can list its code.
Smeevy 4y ago
> I remember spending several hours on a bug that was caused by an altered stored procedure. One SQL function was not as it should have been. I don't know if someone had patched it live, or if there had been an unnoticed failure in a DB migration script. If you have everything under version control you can just reapply that version's stored procedures off the database scripts. If someone or something is not handling failures in deployment scripts (not just database migrations) then you get that sort of problem.
cafard 4y ago
I have done this with both T-SQL and PL/SQL. The stored code is all available in system views and easily extracted with a script. If you set up the script to run automatically at a given time, you know that the version in git is at most n hours out of date. If you leave the control up to the scheduled job, then your commit messages may not be what you want. But there's nothing keeping you from running your extraction script off schedule and then writing a good commit message.
gifflar 4y ago
We store our database functions in SQL scripts that are automatically executed with every Flyway migration, so-called callback scripts. In the callback script every function is declared to be created or recreated. As Flyway is executed with each application start, the database functions are recreated with every application start, even if they didn’t change. This approach has its pros and cons but it ensures that database functions always match the version of the application code. Because the callback script of a database function is stored in a file that is edited when needed, the Git history works the same as for application code.

kumarvvr 4y ago

I agree with all your points, except for SQL. Getting lost in SQL is easy, due to its syntax and lack of standards. Compare that to any of the mainstream programming languages, the difference in cognitive load to process is stark.

xupybd 4y ago
It might be Stockholm syndrome but I find SQL to be one of the best DSLs I know. It's only good at querying relational data but it's good at that. I might have spent too much time in SQL but it one of my favorites.
- kumarvvr 4y ago
  I always live in the fear of accidentally destroying data or tables with SQL. Not a rational fear, just an irrational one. A primitive fear of the unknown. But, to be fair, I have had to work with some of the worst SQL constructs and stored procedures. shudder
whynotmaybe 4y ago
I want to thank you for saying you don't like SQL. Now in your future, please tell it to everyone involved where you work so that you won't work on it and they'll find someone else to do the SQL. I've worked at place where people hated SQL and where hiding it. Instead of crafting nice and performing SQL, they wrote "select * from table" and then did foreach loops in the code to filter the results they wanted.

addicted 4y ago

Isn’t the author talking about procedural SQL? Regular SQL is just fine. Great even. But procedural SQL truly sucks and is highly inconsistent between DBs.