Differences between SQLite and SQL

In this Blog I will start pointing out some differences between SQL and SQLite, then I will explain what is my understanding about handling DataBases, which companies have started the handling of databases, what have changed in the past decades, and finally an approach on how to change from SQLite to PostgreSQL so a project can be deployed in a web page.

What has changed in the past few weeks, we had an assignment to build our first project with Ruby on Rails. SQLite is ideal for small projects because it can be run in memory and backed by small files on disk that are easily created and moved around. This was a good experience in how to populate a database, get familiar with the CRUD functions, HTML, CSS and finally be able to show it and interact within the browser, localhost:3000.

But there is a problem, SQLite is not intended as a production-grade database. Let’s start with some differences between SQLite and SQL:

This is really interesting, so SQLite as we already know is file-based and serverless, this tool helps to have an understanding of working with databases, and also means that you just work on your computer, but you cannot directly access an SQLite database remotely. Here is where Commercial Database comes into play.

Some of the oldest and most established commercial databases are from:

Oracle, IBM, Microsoft….

They are decades old, with millions of lines of code in order to interact with them. Because nowadays more people like us are becoming software engineers, demand has grown for a lower cost or free open sources. These are some examples:

If you or your company don’t want to deal with the database there are some companies out there that can provide you on-demand support, “Commercial Database”.

Another factor is the actual size of your database and the number of users who will access to it, many companies offer small, free database for trying their service, but once your usage goes beyond the hobby level, you have to start paying.

The Cloud Data storage in addition to data storage solutions, also support popular open-source databases, like MySql, PostgreSQL, and MariaDB.

So what are you paying for…

Basically for the data storage but these are giant corporations with data centers around the world, with tens of thousands of servers, so they can offer you a little bit more:

If you are building an app that will be a hit worldwide and you need to grow (scalability) you will need the ability to scale horizontally, and these companies will help you to do that, they will be like an umbrella so you can focus in your software.

In this case, choosing Postgres or MySQL would be a good compromise, you can keep the database on your tiny server for now, but rest assured there are compatible versions in the cloud if the need were to arise and migrate your data from a local Postgres or MySql database to one in the cloud is fairly straightforward.

Wrapping up!

SQL, database design, and optimization are skills you will need regardless of the database you select.

Try creating the same database in Mysql, Postgres, MariaDB, SQLite, and others.

In this link, there is a review of some cloud alternatives for a startup.

Finally, I found a really good article explaining how to change from SQLite to PostgreSQL.

If you’ve removed the gem 'sqlite3' line from your Gemfile and are still getting errors while deploying to Heroku it is likely that another gem you are using has sqlite3 as a dependency. To help find the source of this dependency look in your Gemfile.lock for sqlite3. Find the gem that has sqlite3 as a dependency and remove it from your Gemfile. Once you’ve done this run bundle install and ensure that sqlite3 no longer exists in your Gemfile.lock.

To install Postgres on your computer use this link.