I have been thinking about it for quite a while. I am currently using MS SQL Server (Express Edition) for everything. Recently, Microsoft broke SQL server connectivity (ODBC and ADODB alike) on the 1803 update, for 64-bit systems. Luckily, 32-bit systems can still connect so my deployed apps on 32-bit can still work. 64-bit is close on my roadmap however and I can no longer support my 32 bit apps with the current version of Xojo, since the gui runs on 64-bit systems…
That’s pretty the tipping point. I must move on to a database that will work on multiple platforms, from multiple platforms. Postgre is the prime candidate. I must bite the proverbial bullet.
I am looking for a management tool that will:
let me manage my instances and do the maintenance on tables, stored procedures (I will move most SQL server stored procedures to prepared statements, but still…) - I remember @Norman Palardy telling me not to use stored procedures to avoid being locked on one database. That advice starts sounding very good…
compare instances (DEV, QA, PRD for example) and synchronize objects (tables, stored procs, etc. also data)
What are your favorites? - I am already considering Navicat and the latest pgAdmin (does not do sync as far as I can tell)
I don’t have much experience with prepared statements, since I was (am) using exclusively ADODB with stored procedures on SQL Server. Any warnings, caveats, recommendations are welcome.
I use Valentina Studio. Although I pay for the Pro version, mostly to support @Ruslan Zasukhin 's work, the free version does almost everything. (The Pro version’s ability to apply a color to a database or individual tables is really useful, especially when you don’t want to accidentally make a change on your production database.)
BTW, despite Norman’s advice, we have loaded our database with functions. We access it with apps from two different environments and centralizing that code is more efficient than recreating it or attempting to call one environment from the other. Postgres’ PLPGSQL language takes some getting used to, but is convenient and flexible once you get the hang of it.
I’m using all of Navicat 11, PGAdmin4 and Valentina Studio for some reason. All of those are decent tools that should get you started. I like Navicat the least, it has some serious bugs when it comes to editing functions with loads of parameters. Maybe those are fixed in Navicat 12.
Wow! thank you all for such quick answers! I am going to add Valentina Studio to my list of worthwhile candidates.
Here begins the journey. I am setting up a Linux Mint 19 VM ( that should be supported until 2023). I am thinking of setting up PG 10. is this too bleeding edge?
As a admin GUI I can recommend EMS SQL Manager (Windows / WINE) or JetBrains DataGrip but you should get used to psql as well. Further list in the wiki here and here
For schema comparison / migration, have a look to Pyrseas or Postgres Compare (alpha, not tested yet)
And, please call it PostgreSQL or Postgres. There is no such thing as “Postgre”
I had nothing against SQL Server, other than the fact that it is very Windows-centric. MS’ blunder with the 1803 update is pushing me to make the move. SQL Server is powerful and easy to manage. I have a feeling that PostgreSQL is at least as powerful, but management seems a bit more difficult. It could just be that I haven’t learned it and it is just as easy. Time will tell.
Why no mention of MySQL and CubeSQL? I use both of these plus PostgreSQL. Horses for courses Now that SQLDatabaseMBS supports MSSQL, MySQL, PostgreSQL, CubeSQL, SQLite I write all my code to be so SQL generic that I can change the back end to use any database the client desires.
For large commercial databases it’s hard to beat PostgreSQL on price!
Yes that is correct.I should have added “conveniently”. I don’t like to remote debug. I am going to do it until the move to postgreSQL is completed. But this is definitely not my preferred way of doing things.
I don’t like the mySQL licence. It is disqualified at the start. Frankly, I acknowledge its popularity, but I don’t understand it.
CubeSQL: maybe but right or wrong, I don’t perceive it to be industrial grade. SQLite: I use it for single user stuff. It is not suitable for my intended use. There are also others: Firebird, Oracle, DB2 for example.
I decided upon PostgreSQL because the MSSQL databases that I want to convert are quite large and complex. I need a top-tier RDBMS for these two projects. Also, I decided on PostgreSQL because the price is right, regardless of the commercial status of the application. There are many tools for it. I was already familiar with some of the suggestions in this thread, and suggestions that I was not familiar with seem quite capable also. For smaller projects, I don’t rule out other choices.
One feature of PostgreSQL that I really love is the notifications. I use notifications paired with triggers extensively. This is a great way to keep the user interface data current.
I understand and use triggers here and know how the notifications work in principle. In what context do you use notifications paired with triggers ? Just asking out of curiosity.
how do you send messages between postgres clients ? with xojo it seems you can only send a string, I would like to send a string, and some parameters too. you can only listen to known messages, not a known message and a parameter.
how can you do that ? send a client that table xxx field yyy rowid zzz has been modified for example?
Check out Xojo/Example projects/Database/PostgreSQL/Listen and Notify . I am using this a lot as well, works like a charm. Wouldn’t want to build a multiplayer shooting game around this feature, but it is ideal for updating data on client apps once changes in the db occur. I’m using this even on the machine that initiated the change.
