r/sqlite • • 4d ago

How does a SQLite server work?

Hey guys, I'm new to SQL and all of the tech. I'm planning on using SQLite for a school project, cos I heard it's relatively simple and easy-to-use. However, before I install and mess around with it, I have some questions.

So, this project will act as an application on someone else's computer, and that's the part that confuses me. Because if I install SQLite on my computer, create a database using it and integrate said database into a Java application, will the database I created remain exclusive to my computer, or can I transfer it to another user's computer? (if that makes sense). Like, if I export this Java application onto another computer, what will happen to the database? Do I manually need to copy and config it for every computer my application is running on, or no, not really?

Sorry if this is a stupid question, I'm new to all of this so I really would appreciate someone clarifying this for me.

Thanks in advance!

6 Upvotes

16 comments sorted by

8

u/octobod 4d ago

When it's not running (ie when your Java app isn't running) an SQLite database is just a file on the hard disk, so you can copy it along with all the Java code

1

u/Kiv_Dim_2009 4d ago

Oh, ok, thank you!

1

u/kbilleter 3d ago

If the db is set to use a write-ahead log it can occasionally leave files behind. Just open again and make sure everything is cleaned up before moving with something like “begin immediate; commit;"

6

u/LowCompetitive1888 4d ago

There is no server, your app is opening the SQLite database file directly and processing your apps SQL commands against it.

1

u/Kiv_Dim_2009 4d ago

Thank you for clarifying!

1

u/davidgsb 4d ago

sqlite is not client/server based, it's library you embed in your application which can then read sqlite3 file format which holds your data. You can then run sql query through that library to process data from that file.

https://www.sqlite.org/quirks.html#sqlite_is_embedded_not_client_server

or

https://www.sqlite.org/serverless.html

1

u/rupertavery64 3d ago

It's just a file.

When you write a program that uses it, you will most likely import a library for sqlite. That library has methods to open the database file (if it doesn't exist yet, it will create it). There will be methods for creating the tables and columns if they don't exist yet, or ignore if they do.

Then you can query the database using SQL commands. There are some small differences between regular SQL and SQL for SQLite.

You can use https://sqlitebrowser.org/ to open a SQLite database directly.

Since it's just a .db file, you can just copy it. I assume you know about relative paths and making sure your application can see the database from it's current path.

1

u/FarmboyJustice 3d ago

It is possible to have an application server that uses SQLite as its database, this causes some people to think the database is actually a server, but it's still just a file.

1

u/YetAnotherUser23xjw 3d ago

If you want every user's application to have their own data, you just deploy the sqlite file with your application. If you want a centralized data repository where everyone is using the same data, you need to host it somewhere. Then it's a server and you'd need to provided a connection string to each application (ip address/url + credentials). This would probably be hosting in the cloud somewhere. AWS?

1

u/deadeye_82_1 3d ago

Sqlite3 is a library, not a server. It runs directly inside your application's process.

If you want a sqlite server: https://turso.tech/

But do you need this?

1

u/sereikis 2d ago

one caution on copying: BEGIN IMMEDIATE; COMMIT; isn't a WAL checkpoint and doesn't guarantee the main .db file contains every committed write. use SQLite's backup API (or the CLI's .backup) for a live database; don't copy just the main file while its -wal file is still needed.

0

u/lordpuddingcup 4d ago

SQLite is like an excel file it’s not a server really

0

u/Original-Active-6982 3d ago

SQLite cannot be used in an application that needs multi-user/isolate/protected support. It can only run within a single user process. But it is fantastic for that use case.

1

u/SpiritRaccoon1993 1d ago

I don't know what perspective you look at, but I believe it really can. WAL Mode and with coding strategies it will work smoothly with up to 100 users. It sure is limited to others, but not built to replace them.