Which database should I use?

As can PostgreSQL - one of the reasons I would recommend it over Maria or mySQL which are NOT free for commercial use
Literally the license includes ( from here PostgreSQL: License)

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

And should you ever need it the EnterpriseDB folks have great paid support for postgresql

Another option is Firebird (use via the ODBC plugin or MBS SQL plugin ), it’s also free for any use:

Source

I have a significant project that’s backed by a Firebird database (over 100 tables many containing hundreds of thousands of records, thousands of transactions daily from dozens of concurrent users), it’s been live on an Ubuntu server for over 6.5 years and has buzzed along without a hiccup.

Firebird, while a capable DB, is just not as widely used nor as widely supported as PostgreSQL

Hi Kayla,

Welcome :slight_smile:

Though I am sure you are eager to get started with building an application that talks to a database, if this is your first time working with databases, I would recommend reading and watching a number of tutorials on them as well as beginning to work with them. Once you start to grasp the structure and concept of a database, you can begin thinking about the database design as it relates to your application. Keep in mind that a bad database design will create nothing but heartburn in the long run.

As far as which database to begin learning on, I would recommend sqlite or postresql as good choices.

agree… but there is no caveat as far as you do not ship your software bundled with any 3rd party DBMS like MySQL. I let my customers decide. If they choose Oracle MySQL, then licensing is not my concern anymore.

Hi Guys - I am still quite new with this version of Xojo, but am finding it works very well with databases. :wink:

Couple points to ponder - Oracle and MS SQL server are both free for development and Small Deployments.

They are several steps beyond MySQL or SQLite in many ways, easy to manage and develop in, and the cost is right. If you find the need to grow, they can grow to just about unlimited size and performance as well. Even deploy the databases securely in the cloud with AWS and/or Azure. Choose Oracle if you intend to deploy your app on MacOS and Linux, because Xojo does not support MSSqlServer on those platforms, so far as I can tell.

Second, if you choose Oracle, be aware XoJo works right now only with 32bit versions of the Oracle client libraries, at least on Windows 8.1, 10, and OSX Yosemite. (Runs fine on 64bit operating systems, just use Oracle InstantClient 32 bit clients.)

This is not an ornerous issue as it won’t make any difference unless you are doing a lot of very large transactions - and a customer facing app to store purchase transactions will probably never face that problem. I understand Xojo is working on and very close to releasing a 64bit version of Xojo as well, which will make this consideration just go away.

Last point, neither Oracle or SQL server run on MacOS. Oracle runs great on Windows or Linux, and SQL Server runs only under Windows. That tends to mean you need to run the database server on a virtual machine dedicated to the task, or on a spare machine if you happen to have one. VMs are nice, as running a Linux or Windows VM also allows you to easily test your application on those platforms, if you wish.

Yours,
Paul

p.s. - obviously, the above only makes sense if you are providing a centralized database. If each copy of the app has its own database running on the local machine, SQLlite, Valentina, or even flat files are all good choices to consider. :slight_smile:

Thanks for everyone’s input! :slight_smile: And nobody poo-pooed on at least my parade. It’s always good to be aware of these things, like commercially using a database product. I actually hadn’t thought of it before, so I’m happy I now know and that SQLite is okay to use for things like commercial use. I really enjoy reading the comments. There is nothing I’ve without learning something.

I just created my first database…well practice database. :wink: I used the example from the Xojo manual (Introduction to Programming with Xojo). The examples are very useful and I’m going to create a database using the theory from the example.

Kayla

Just wanted to tell you a little bit about our use of Sqlite. We had some requirements and it was the only one that could meet these considerations.

  1. Had to be embedded because our application allows the user to create an unlimited number of databases. We need easy and seamless access.

  2. Had to be extremely portable because our clients will sometimes take the database on their local computer to a client’s office and when they return to the office, move it back to a network drive.

  3. Cross platform. Windows and OS X.

  4. Lots of 3rd party support.

We were converting an application from Visual Foxpro to XOJO (Realbasic at the time) so we needed the same functionality and speed that VFP provided.

We converted our application a little over a year ago and we have 20,000+ users and thus far have had no significant issues. We know that some of our users will have multiple user access and even though Sqlite is a single user database, we have had no corruption issues or problems. We use CubeSQL in-house for our enduser database which is accessed by multiple users and it’s great.

The one thing that we invested a great deal of time in is writing an application to manage our database structures for all our applications. Kind of reminds me of the database manager in Filemaker but without the auto-enter and validation features.

It in turn creates a database that is distributed with our applications and each time our application starts it checks to see if there are file structure changes by comparing the selected database with the “template” one. We have a routine that handles the update process.

I think you’ll find Sqlite quite good as long as you don’t need the true multi-user features.

If you are still relatively new at using databases, I recommend reading a book first on using SQL.

As most people know here, I am a database vendor, so I have some self serving recommendations on database formats…but that said, do yourself a favor and learn some SQL. That’s almost 100% transferable knowledge to any database platform.

Is that a valid argument?
Firebird is an excellent rdbms, it is perfectly adequate for almost any use. It has the strengths of both postgresql and sqlite as it can function in both both server and embedded modes. It requires practically zero admin which is not true of postgresql.
It’s community is not as big as postgresql but it is still very widely used. It is not going away. It is for sure up to the job of an application developed in xojo.

[quote=191946:@Jack Kingston]Is that a valid argument?
Firebird is an excellent rdbms, it is perfectly adequate for almost any use. It has the strengths of both postgresql and sqlite as it can function in both both server and embedded modes. It requires practically zero admin which is not true of postgresql.
It’s community is not as big as postgresql but it is still very widely used. It is not going away. It is for sure up to the job of an application developed in xojo.[/quote]
It IS just my opinion - so I’m not sure how you assess “VALIDITY” ?
I’d use postgresql before firebird
But that’s IMHO

[quote=191947:@Norman Palardy]It IS just my opinion - so I’m not sure how you assess “VALIDITY” ?
I’d use postgresql before firebird
But that’s IMHO[/quote]

Firebird community is not as big as postgresql.
Is the firebird community too small to trust firebird?
That is what I mean about validity

I once looked at Firebird, and abandoned the option because the product was near dead. It would now seem to be resurrected. Development seems active, satisfaction appears to be quite high among users. Is adoption in the same league as for Postgre? Evidently not. Is it sufficient to warrant that my product will not use an orphaned DB solution? perhaps. My tolerance to risk, among other decision factors, will dictate whether I select Firebird or I discard it as a solution.

In the end, selecting a tool depends on many factors. Suitability to task is one. Level of support and market share may be an other. My reality and the next person’s are different. Our decisions are likely to be different as well.

What is important here is to provide the OP with as much insight as possible on various options. Saying that I would choose Postgre before Firebird for general purposes is a valid statement, in light of selection criteria that include market weight. An other valid statement is that perhaps I will select a more lightweight solution for other projects, possibly Firebird. Certainly, your post has made me look at Firebird again and realize that the the announcement of its death was premature.

+1 on @Lynn Fredricks suggestion to read a good book on SQL before diving into development. There are many to choose from.

When was that??? I have been following firebird since 2007 and have never had that feeling.

Hi Kayla,

Yes, @Lynn Fredricks and @Louis Desjardins are correct. If you are going to do any database work you must learn SQL. When you get stuck there are a LOT of very SQL-literate folks here who can help you.

This website is good for basic SQL: http://www.w3schools.com/sql/

Once you figure out which way is up you will want to begin exploring proper database design. There are some good books that will help you learn that as well.
A good book that hits the middle ground between practical and theoretical is: “Beginning Relational Data Modeling”
If you have a Safari subscription you should be able to look through it for free.
You can also check out this website. It has 1,300 sample designs that will at least point you in the right direction.
http://www.databaseanswers.org/data_models/

Good luck and don’t hesitate to ask for help.

Also I am not knocking Postgresql but Firebird is a great rdbms and I don’t like to see it being criticised unfairly.
In my view it is the perfect hybrid.
It can work in embedded mode just like Sqlite but with far superior support for official SQL standards, e.g right join.
It can function as a server database just like Postgresql.
So if you want to provide a solution that may start out as single user but later scale it is ideal.
The alternative is to use sqlite for embedded and later port to postgresql and there are always issues with this.
If you have a product that in normal use requires a shared database but for which you’ like to be able to provide
a single user trial version firebird is what you want. Users run the installer and it works.
In summary if you want to learn a single rdbms that can be used in any situation firebird is without an equal.
And of course, like postgresql, it is completley free and open source.

There is also a book available for programming SQLite with Xojo

SQLite with Xojo

There are also other books and articles at Great-White-Software.com that can help. :slight_smile:

[quote=191948:@Jack Kingston]Firebird community is not as big as postgresql.
Is the firebird community too small to trust firebird?
That is what I mean about validity[/quote]

Its just not as widely supported by cloud providers (who seem to favor mySQL despite the licensing mess mySQL & maria place users in)
I’ve used it but given a choice I’d pick PostgreSQL over Firebird
Now should some enterprising soul take Firebird and do what the EnterpriseDB folks have done with PostgreSQL that could certainly change minds

From my experience I think Firebird is as close to a zero-admin RDMS as it’s possible to get.

Yes, and you can also choose between a multi-threaded or multi-process server, the former being faster the latter being more robust. When Firebird 3 arrives the embedded server will be unified with the client library so I imagine going from single user to client/server could be as easy as changing the connection string.

For a long time the Firebird Foundation didn’t do themselves any favours with their fusty old website. That’s now, thankfully, had a refresh. I don’t think the project itself was ever near death.

[quote=191862:@Paul Raulerson]Last point, neither Oracle or SQL server run on MacOS. Oracle runs great on Windows or Linux, and SQL Server runs only under Windows. That tends to mean you need to run the database server on a virtual machine dedicated to the task, or on a spare machine if you happen to have one[/quote]Firebird runs on lots of platforms:
OS: Windows, Linux, MacOS, FreeBSD, HP-UX, AIX, Solaris and more…
Hardware: x86, x64, ARM, PowerPC, Sparc and more…

e.g. Want Firebird on your Raspberry Pi (ARM+Linux)?

how do you connect to a firebird database using xojo ?