From SQLite to MariaDB: When Your Application Needs to Grow

SQLite is fantastic.

Really.

For a developer starting a new project, it is difficult to find something easier: no server to install, no network configuration, no user management, no backup strategy to define on day one.

You create a file, you open it from your application, and you are ready to store data.

That is why SQLite is so popular for prototypes, mobile applications, small web applications, CLI tools, embedded systems, and even many production workloads.

But sometimes the project grows.

And one day, the database that was “just a file” starts to become the center of the application.

More users. More writes. More background jobs. More reporting. More expectations. More risk.

That is usually the moment when a developer asks a very good question:

Should I continue with SQLite, or is it time to move to a client/server database?

In this article, we will see why moving from SQLite to MariaDB Server can make sense, what benefits MariaDB brings, and how to perform the migration.

SQLite is not the problem

Before starting, let’s be clear: migrating away from SQLite does not mean SQLite was a bad choice.

Very often, SQLite was the best choice at the beginning of the project.

It allowed the developer to move fast, to keep the architecture simple, and to avoid spending time managing infrastructure before the application even had users.

But a good technical choice can become less appropriate when the context changes.

SQLite is embedded in the application. MariaDB Server is a database server.

This difference changes everything.

With MariaDB Server, the database becomes a service that can be accessed by multiple application servers, managed independently, secured with users and privileges, monitored, backed up, replicated, and scaled.

When is it time to move?

There is no magic number.

But there are some signs that usually indicate that the project is ready for MariaDB.

Multiple application instances

With SQLite, the database is a file. This is very convenient when the application runs on one machine.

But what happens when you want to run two web servers behind a load balancer?

Or when you want to deploy the same application in containers?

Or when you want background workers to write data while the web application is also writing?

You can, of course, try to work around this, but at some point, you are fighting the architecture.

With MariaDB, all those application instances connect to the same database server.

That is exactly what it is made for.

More concurrent writes

SQLite supports concurrent reads very well, but concurrent writes are more limited. When many processes or threads need to write simultaneously and cannot simply wait their turn, SQLite itself recommends using a client/server database engine.

MariaDB Server, using InnoDB, provides row-level locking and transactions. This allows multiple sessions to work concurrently on different rows without blocking the whole database.

For a growing web application, this is a big difference.

Better access control

With SQLite, access to the database is mostly access to the file.

With MariaDB Server, you can create users and grant privileges at the database, table, and even more granular levels.

For example:

CREATE USER 'myapp'@'%' IDENTIFIED BY 'StrongPasswordHere';

GRANT SELECT, INSERT, UPDATE, DELETE
ON myapp.*
TO 'myapp'@'%';

This is already much better than giving the application full administrative access.

And of course, for migrations or administration, you can have different accounts with different permissions.

Operational needs

When a project grows, the database is no longer only a developer’s concern.

You also need to think about:

• backups
• restore tests
• monitoring
• upgrades
• security
• performance tuning
• replication
• high availability

MariaDB Server fits naturally in that world.

Everything you expect from an RDBMS is part of MariaDB Server.

SQL features

SQLite supports a lot of SQL, but MariaDB Server brings many additional features useful for larger applications:

• stored procedures
• views
• roles
• generated columns
• JSON functions
• window functions
• replication
• different storage engines
• full-text indexes
• event scheduler
• and a VECTOR Search

And with MariaDB Server, you also get the benefit of an open-source database server with a very large ecosystem.

Migration overview

The migration process consists of the following steps:

1. inspect the current SQLite database
2. adapt the schema for MariaDB
3. export the data from SQLite
4. create the MariaDB database and user
5. import the schema
6. import the data
7. adapt the application connection layer
8. test, test, and test again
9. switch production traffic

Let’s have a look.

Inspect the SQLite database

First, we need to know what we have.

On the machine where the SQLite database is available:

sqlite3 app.db

Then inside the SQLite shell:

.tables
.schema

To dump the schema:

sqlite3 app.db ".schema" > schema.sql

To dump everything:

sqlite3 app.db ".dump" > sqlite_dump.sql

The full dump is useful for understanding the database, but I usually prefer to migrate the schema and data separately.

Why?

Because the SQL dialects are not identical.

The SQLite dump will contain SQLite-specific syntax that may need to be adapted before being loaded into MariaDB.

Adapt the schema

This is probably the most important part of the migration.

SQLite is very flexible with types. MariaDB is more strict, and that is a good thing.

A table like this in SQLite:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT,
  created_at TEXT
);

could become this in MariaDB:

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_uq (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Some common changes:

SQLite	MariaDB
INTEGER PRIMARY KEY	BIGINT UNSIGNED AUTO_INCREMENT
TEXT	VARCHAR, TEXT, MEDIUMTEXT
BLOB	BLOB, MEDIUMBLOB, LONGBLOB
REAL	DOUBLE or DECIMAL
NUMERIC	DECIMAL
TEXT date	DATE, DATETIME, TIMESTAMP
no strict foreign keys by default in old apps	real foreign keys with InnoDB

Pay attention to booleans too.

In SQLite, you may have stored them as 0 and 1.

In MariaDB, you can use:

is_active BOOLEAN NOT NULL DEFAULT TRUE

BOOLEAN is a synonym for TINYINT(1), but it makes the schema easier to read.

Create the database in MariaDB

Now let’s create the database and the application user:

CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'myapp'@'%' IDENTIFIED BY 'StrongPasswordHere';

GRANT SELECT, INSERT, UPDATE, DELETE
ON myapp.*
TO 'myapp'@'%';

For the migration itself, you may want to use an administrative account or a temporary migration user with more privileges.

Do not forget to remove anything unnecessary after the migration.

Load the schema

Once the schema has been adapted for MariaDB:

mariadb -u root -p myapp < schema_mariadb.sql

Check the tables:

SHOW TABLES;
SHOW CREATE TABLE users\G

At this stage, the database should be empty but ready.

Export data from SQLite

There are multiple ways to export data.

For small databases, CSV is simple and efficient.

Example for the users table:

sqlite3 app.db <<EOF
.headers on
.mode csv
.output users.csv
SELECT id, name, email, created_at FROM users;
EOF

Do the same for every table.

Of course, you can script this.

You can list the tables with:

sqlite3 app.db ".tables"

And generate one CSV file per table.

Import data into MariaDB Server

On MariaDB Server, we can use LOAD DATA LOCAL INFILE.

Example:

LOAD DATA LOCAL INFILE 'users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

Depending on your client and server configuration, LOCAL INFILE may need to be enabled.

For example:

mariadb --local-infile=1 -u root -p myapp

And on the server side, check:

SHOW VARIABLES LIKE 'local_infile';

If needed:

SET GLOBAL local_infile=1;

Do not enable it permanently without understanding the security implications.

Foreign keys and import order

If your schema has foreign keys, import the parent tables first.

For example:

1. users
2. projects
3. tasks
4. comments

If that is not possible, you can temporarily disable foreign key checks during the import:

SET foreign_key_checks=0;

-- import data

SET foreign_key_checks=1;

But be careful.

Disabling checks does not validate everything automatically afterward. It is better to import in the correct order when possible.

Sequences and AUTO_INCREMENT

This is not mandatory, but it can be good practice during the migration: after importing explicit IDs from SQLite, verify the next auto-increment value.

For example:

SELECT MAX(id) FROM users;

Then:

ALTER TABLE users AUTO_INCREMENT = 12345;

Use a value greater than the current maximum.

This avoids duplicate key errors when the application starts inserting new rows.

There is also the option to use tools to migrate from SQLite to MariaDB, such as this one: https://github.com/techouse/sqlite3-to-mysql.

Application changes

The application now needs to connect to MariaDB rather than open a SQLite file.

Before:

import sqlite3

conn = sqlite3.connect("app.db")

After:

import mariadb

conn = mariadb.connect(
    user="myapp",
    password="StrongPasswordHere",
    host="db.example.com",
    port=3306,
    database="myapp"
)

Of course, the exact connector depends on the programming language.

For PHP, Java, Go, Node.js, Python, Ruby, Rust… there is a MariaDB or MySQL compatible connector available.

You will also need to review SQL statements.

Some common differences:

• placeholders can be different depending on the driver
• AUTOINCREMENT becomes AUTO_INCREMENT
• INSERT OR IGNORE becomes INSERT IGNORE
• INSERT OR REPLACE usually becomes REPLACE or INSERT ... ON DUPLICATE KEY UPDATE
• LIMIT offset, count is supported by MariaDB, but some SQLite queries may use a different style
• date functions are different
• JSON functions are different

This is where tests are very useful.

Test the migration

Before moving production traffic, test the migration on a copy of the database.

Some checks I like to perform:

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM projects;
SELECT COUNT(*) FROM tasks;

Compare those numbers with SQLite:

sqlite3 app.db "SELECT COUNT(*) FROM users;"
sqlite3 app.db "SELECT COUNT(*) FROM projects;"
sqlite3 app.db "SELECT COUNT(*) FROM tasks;"

Also check important constraints:

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

And verify the application behavior:

• user registration
• login
• updates
• deletes
• background jobs
• reports
• migrations
• backups
• restore procedure

A migration is not finished when the data is imported.

It is finished when the application works correctly, and you know how to recover if something goes wrong.

Production cutover

For a small application, the simplest approach is often:

1. stop the application
2. copy the SQLite file
3. migrate the data to MariaDB
4. run validation checks
5. change the application configuration
6. start the application
7. monitor

This creates downtime, but it is simple and safe.

For larger applications, you may need a more advanced strategy:

• maintenance mode
• write freeze
• incremental sync
• dual writes during a short period
• custom migration scripts
• blue/green deployment

But do not make it more complex than necessary.

Simple is good when simple is enough.

Backups

Before the migration:

cp app.db app.db.backup

After the migration, use MariaDB backup tools and test the restore.

A backup that was never restored is only a hope.

For a logical backup:

mariadb-dump -u root -p --databases myapp > myapp.sql

To restore:

mariadb -u root -p < myapp.sql

For larger databases, you may want to consider physical backups with mariadb-backup and replication.

Benefits after the migration

Once the application uses MariaDB, you get several benefits:

• multiple application servers can connect to the same database
• better concurrency for write-heavy workloads
• users and privileges
• easier remote access
• monitoring
• backups and restore procedures
• replication
• high availability possibilities
• richer SQL features
• better operational separation between application and database

And most importantly, the architecture is ready for the next step.

FAQ

Is MariaDB always better than SQLite?

No.

SQLite is excellent. For many applications, it is the right choice forever.

If your application is local, embedded, small, or has simple concurrency needs, SQLite can be perfect.

MariaDB is useful when you need a real database server.

Can I import the SQLite dump directly into MariaDB?

Sometimes for very simple schemas, but usually not without modifications.

SQL dialects differ, and SQLite is more permissive with data types.

I recommend reviewing and manually adapting the schema.

Should I keep the same column types?

Not always.

The migration is a good opportunity to clean the schema.

Use proper DATE, DATETIME, DECIMAL, VARCHAR, indexes, constraints, and foreign keys.

Can I keep the same IDs?

Yes.

Import the IDs explicitly, then adjust the AUTO_INCREMENT value.

Should I migrate to MariaDB before the project grows?

Not necessarily.

Starting with SQLite is often the best decision.

But when the project requires multiple writers, multiple application instances, remote access, better operational tooling, or stronger access control, MariaDB becomes a very good next step.

Conclusion

SQLite is a wonderful database.

It helps developers get started quickly and keep things simple.

But when the application grows, the database requirements also grow.

Moving to MariaDB is not a failure of SQLite. It is just the next step in the application’s life.

The migration is mostly about being careful:

• understand the existing schema
• adapt the data types
• export the data
• import into MariaDB
• update the application
• test everything
• backup before and after

And then your project is ready for more users, more traffic, and more fun.

Enjoy migrating to MariaDB!