4 ms·

Mike, given all your experience, do you have a preferred relational db? I'm assuming the answer is "it depends" but I feel like, say, MySQL and Postgres are fai

by aidos 9y ago

Mike, given all your experience, do you have a preferred relational db? I'm assuming the answer is "it depends" but I feel like, say, MySQL and Postgres are fairly indentical in terms of usecase. I'm assuming you know pretty much every dark corner of the dbs you have engines for in sqla. It must be a little hard forever supporting strange breakages like your example above when it's a db you don't care for as much.

zzzeek 9y ago
If I was doing brand new development somewhere I'm sure I'd use Postgresql, since from a developer point of view it's the most consistent and flexible. While for the last few years I've worked way more with MySQL / MariaDB and at the moment the MySQL side of things is a bit more familiar to me, I still appreciate PG's vastly superior query planner and index features. Certainly in the SQLAlchemy world when i want to see how a database does something "correctly", Postgresql is where you go to see the "correct" way to do something (such as, the correct answer for "SELECT NULL IN (SELECT 1 WHERE 1=0)" - the answer is false, not NULL, or when I had to convince MariaDB developers, who just added real CHECK constraints in 10.2, that I should be able to drop a column that has a single-column CHECK constraint on it without first dropping the constraint, PG serves as the canonical example for "yes this is supposed to work"). I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-2-intro-to.html http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model... for examples), but for selfish reasons; features like these are meant as replacements for what you get from a tool like SQLAlchemy Core and the unusual syntaxes they have are also really hard to implement yet I continually get asked to implement more of them. These aren't reasons I wouldn't use Postgresql it's just this little cloud of "ugh" that hangs over me when I really have to dig into PG :). It also makes me cringe a bit the way Postgresql spawns a child process on the server for every database connection still. MySQL has very cheap connections and that's still a pretty compelling reason to keep it in mind for some kinds of applications (applications called Openstack which eat up thousands of connections...). In the closed source world, MS SQL Server has really been getting a lot easier to use and I'm very impressed at their linux version, I have it running in docker and it is 100% exactly the same as the windows version as far as SQL / client interaction. None of this "MySQL default casing conventions are OS-dependent" stuff (see https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sens... , and prepare to be amazed what hacky cruft still lives on within the MySQL ecosystem).
- porker 9y ago
  > I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model.. http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model.... for examples) I read that link and didn't get it - has anyone a fuller explanation or other links to recommend?
  - aidos 9y ago
    I guess this is one of the more interesting examples to look at: select * from inventory_item i where (i.cogs_account).control_code = '5500'; You're only referencing 1 table in the query (inventory_item), but then i.cogs_account runs a query on a related table. It doesn't seem like a great idea to me. You can't really tell what's happening and there's a huge performance impact in doing it. The query planner is going to be a bit limited in what it can do. The example following where you have a save procedure against a column is a bit baffling. SELECT (i.save).* FROM (SELECT (row(null, 3, 1, 2, 'TEST124', 'Inventory testing item 2', 1, 2, true)::inventory_item).save) i;
- aidos 9y ago
  Wow, I'd never seen that style of defining the relationships as computed columns inside a table before — it's somewhat....terrifying. Think I'll stick to SQLA on that front thanks!
nafey 9y ago
Forgive me for being blunt but I am sorry, can you please tell me who zzzeek is? Why does everyone know him but first name?
- magpi3 9y ago
  Click on his username to read his profile which includes a link to his website http://techspot.zzzeek.org http://techspot.zzzeek.org
- deleted 9y ago
  [deleted]

by aidos 9y ago

zzzeek 9y ago

If I was doing brand new development somewhere I'm sure I'd use Postgresql, since from a developer point of view it's the most consistent and flexible. While for the last few years I've worked way more with MySQL / MariaDB and at the moment the MySQL side of things is a bit more familiar to me, I still appreciate PG's vastly superior query planner and index features. Certainly in the SQLAlchemy world when i want to see how a database does something "correctly", Postgresql is where you go to see the "correct" way to do something (such as, the correct answer for "SELECT NULL IN (SELECT 1 WHERE 1=0)" - the answer is false, not NULL, or when I had to convince MariaDB developers, who just added real CHECK constraints in 10.2, that I should be able to drop a column that has a single-column CHECK constraint on it without first dropping the constraint, PG serves as the canonical example for "yes this is supposed to work"). I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-2-intro-to.html http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model... for examples), but for selfish reasons; features like these are meant as replacements for what you get from a tool like SQLAlchemy Core and the unusual syntaxes they have are also really hard to implement yet I continually get asked to implement more of them. These aren't reasons I wouldn't use Postgresql it's just this little cloud of "ugh" that hangs over me when I really have to dig into PG :). It also makes me cringe a bit the way Postgresql spawns a child process on the server for every database connection still. MySQL has very cheap connections and that's still a pretty compelling reason to keep it in mind for some kinds of applications (applications called Openstack which eat up thousands of connections...). In the closed source world, MS SQL Server has really been getting a lot easier to use and I'm very impressed at their linux version, I have it running in docker and it is 100% exactly the same as the windows version as far as SQL / client interaction. None of this "MySQL default casing conventions are OS-dependent" stuff (see https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sens... , and prepare to be amazed what hacky cruft still lives on within the MySQL ecosystem).

porker 9y ago
> I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model.. http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model.... for examples) I read that link and didn't get it - has anyone a fuller explanation or other links to recommend?
- aidos 9y ago
  I guess this is one of the more interesting examples to look at: select * from inventory_item i where (i.cogs_account).control_code = '5500'; You're only referencing 1 table in the query (inventory_item), but then i.cogs_account runs a query on a related table. It doesn't seem like a great idea to me. You can't really tell what's happening and there's a huge performance impact in doing it. The query planner is going to be a bit limited in what it can do. The example following where you have a save procedure against a column is a bit baffling. SELECT (i.save).* FROM (SELECT (row(null, 3, 1, 2, 'TEST124', 'Inventory testing item 2', 1, 2, true)::inventory_item).save) i;
aidos 9y ago
Wow, I'd never seen that style of defining the relationships as computed columns inside a table before — it's somewhat....terrifying. Think I'll stick to SQLA on that front thanks!

nafey 9y ago

Forgive me for being blunt but I am sorry, can you please tell me who zzzeek is? Why does everyone know him but first name?

magpi3 9y ago
Click on his username to read his profile which includes a link to his website http://techspot.zzzeek.org http://techspot.zzzeek.org
deleted 9y ago
[deleted]