The big bad boy servers like postgres have quite a set up cost…
ie. It will take you a fair amount of time and effort before you can even say ‘select “hello world”;’
in what context? $package_manager install, add a user/db, then open the command line client and start writing SQL? roughly the same for MariaDB and postgres
Modern package systems have made it hugely much easier than it was… but still, it’s a fair bit to read and understand (what’s a user, what’s a db, what’s a role, oh dear, the DB notion of a users and grants is utterly unrelated to the unix notion… but I need to have both) and then some stuff to do before you ready to go compared to sqlite.
If the aim is just learning sql, sqlite is just so easy.
Main counterpoint I can think of is the backup strategy of “copy the file” vs pg_dump
“copy the file” is not a secure backup strategy for sqlite ;) Regardless of the tech you choose you will have some homework to do: https://www.sqlite.org/backup.html
“Copy the file” will lose your most recent writes, but I thought it always yields a transactionally-consistent view of the database (it has to, as a power failure could stop writes at any time).
I take daily backups, and I don’t care if they are actually backing up data from 10 minutes earlier than the scheduled backup time.
“Copy the file” will lose your most recent writes, but I thought it always yields a transactionally-consistent view of the database (it has to, as a power failure could stop writes at any time).
You are mixing up two failure cases. The sqlite DB itself is transactionally-consistent and will recover on power loss during writes.
‘Dumb copy’ of a consistent sqlite file that fails on write during copy may end up being corrupt. You’re on the mercy of the filesystem to recover that and even if so the resulting file may not be usable by sqlite itself as there is no write ahead log for that copy operation the db could recover from.
The docs I linked above mention exactly that scenario:
if a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery.
Database professionals have lost their way hopelessly.
Their core ideas come from relational algebra…. and they have forgotten them or never quite understood them.
How so? What do you base this assertion on? I will agree that many people that work with databases lack basic relational theory knowledge and this is a problem. But what is specific about “Database Professionals”? How does it differ from [insert your programming language] professionals?
Once you have understood the basics of SQL and want to level up…..
Read “SQL and Relational Theory: How to Write Accurate SQL Code” by C.J. Date and take him deadly seriously.
This is certainly good advice. Note the word basics. If one doesn’t dig into relational theory fundamentals, then there is really the very basics of SQL that can be understood.
I should definitely mention sqllite. Even if, as some sibling comments mention, installing MySQL or Postgres is pretty easy with a package manager, new developers are far less likely to be stymied by bizarre admin or install errors with sqllite.
It’s funny to see a discussion on Stack Overflow bashing quality of another resource while it itself is known for highly misleading and often damaging answers.
Pot calling kettle of course, but w3schools is historically pretty rough. They are simply saying avoid using it as a citation because it is often incorrect. Now I hear they’ve tried to clean up their act and all that but there’s so many better resources. Even just getting a decent book on the subject would do you far better than trying to read w3schools.
The big bad boy servers like postgres have quite a set up cost…
ie. It will take you a fair amount of time and effort before you can even say ‘select “hello world”;’
Can I recommend sqlite…?
Incredibly small, simple and easy fast and rock solid.
It’s utterly ubiquitous (odds on it is in your browser AND your phone!) Not just one instance either.
The documentation is excellent https://sqlite.org/lang.html
And the gui tools are lovely. https://sqlitebrowser.org/dl/
Or you can an firefox addon that allows you to go poking about all the sqlite db’s in your browser (or elsewhere) http://add0n.com/sqlite-manager.html
Lastly I cannot help but have my favourite whinge.
Database professionals have lost their way hopelessly.
Their core ideas come from relational algebra…. and they have forgotten them or never quite understood them.
Once you have understood the basics of SQL and want to level up…..
Read “SQL and Relational Theory: How to Write Accurate SQL Code” by C.J. Date and take him deadly seriously.
Suddenly your SQL becomes much much smaller and more reliable.
Of course, I suggested you move away from Postgres unless you need it……
…but when you do need a serious SQL server, I strongly recommend it.
in what context? $package_manager install, add a user/db, then open the command line client and start writing SQL? roughly the same for MariaDB and postgres
Modern package systems have made it hugely much easier than it was… but still, it’s a fair bit to read and understand (what’s a user, what’s a db, what’s a role, oh dear, the DB notion of a users and grants is utterly unrelated to the unix notion… but I need to have both) and then some stuff to do before you ready to go compared to sqlite.
If the aim is just learning sql, sqlite is just so easy.
I thought that too. Main counterpoint I can think of is the backup strategy of “copy the file” vs pg_dump, but tbh even that isn’t a huge amount.
“copy the file” is not a secure backup strategy for sqlite ;) Regardless of the tech you choose you will have some homework to do: https://www.sqlite.org/backup.html
“Copy the file” will lose your most recent writes, but I thought it always yields a transactionally-consistent view of the database (it has to, as a power failure could stop writes at any time).
I take daily backups, and I don’t care if they are actually backing up data from 10 minutes earlier than the scheduled backup time.
You are mixing up two failure cases. The sqlite DB itself is transactionally-consistent and will recover on power loss during writes.
‘Dumb copy’ of a consistent sqlite file that fails on write during copy may end up being corrupt. You’re on the mercy of the filesystem to recover that and even if so the resulting file may not be usable by sqlite itself as there is no write ahead log for that copy operation the db could recover from.
The docs I linked above mention exactly that scenario:
I’d be astonished if a power failure mid-backup ever resulted in a working backup.
That said, it looks like
vacuum intois probably what I actually want.How so? What do you base this assertion on? I will agree that many people that work with databases lack basic relational theory knowledge and this is a problem. But what is specific about “Database Professionals”? How does it differ from [insert your programming language] professionals?
This is certainly good advice. Note the word basics. If one doesn’t dig into relational theory fundamentals, then there is really the very basics of SQL that can be understood.
It would also recommend the original paper by Codd, where it all started (so to speak) https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
Reliance on autoincrement keys is a clue. It immediately tells you they don’t actually know what their primary key is.
I should definitely mention sqllite. Even if, as some sibling comments mention, installing MySQL or Postgres is pretty easy with a package manager, new developers are far less likely to be stymied by bizarre admin or install errors with sqllite.
w3schools uuughhhhh
https://meta.stackoverflow.com/questions/280478/why-not-w3schools-com
I have reached a point with them that I literally filter them out in google searches.
It’s funny to see a discussion on Stack Overflow bashing quality of another resource while it itself is known for highly misleading and often damaging answers.
Pot calling kettle of course, but w3schools is historically pretty rough. They are simply saying avoid using it as a citation because it is often incorrect. Now I hear they’ve tried to clean up their act and all that but there’s so many better resources. Even just getting a decent book on the subject would do you far better than trying to read w3schools.