Unlocking the Potential of SQLite with libSQL
As the most widely used database in the world, SQLite is a force to be reckoned with. However, its limitations can hold developers back from achieving their full potential. That’s where libSQL comes in – an open-source, open-contribution fork of SQLite that aims to evolve the database to suit modern use cases.
What is libSQL?
libSQL is a collaborative project designed to take SQLite to new heights. With changes happening rapidly in the industry, SQLite is becoming increasingly suitable for use cases it wasn’t initially intended for. libSQL aims to capitalize on this trend and provide a more robust and adaptable database solution.
Getting Started with libSQL
In this article, we’ll focus on sqld, the networked version of libSQL. sqld offers a local development experience that can be easily upgraded to a production-ready, networked database. We’ll cover the installation process, basic usage, and native clients, as well as replication and testing.
Installing Binaries and Basic Usage
To get started with sqld, you can install the binaries using Homebrew or build from source. Once installed, you can create a local instance of sqld and query it using common tools like curl.
brew install sqldor
git clone https://github.com/libSQL/sqld.git
cd sqld
make
make installNative Clients and Replication
libSQL provides client packages for TypeScript/JavaScript, Rust, and Python, making it easy to integrate into your application. Replication in sqld works over gRPC, allowing for seamless data synchronization between primary and replica databases.
Building a Simple Movie List App with libSQL and Express.js
To demonstrate the power of libSQL, we’ll build a simple movie list app using Express.js and Vue.js. We’ll cover the setup process, including creating the Express server, initializing the database, and seeding data.
Setup
const express = require('express');
const app = express();
const sqld = require('sqld');
const db = new sqld.Database('movies.db');
db.exec(`
CREATE TABLE IF NOT EXISTS movies (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
watched BOOLEAN DEFAULT FALSE
);
`);
Adding New Movies and Marking Watched
We’ll implement routes for adding new movies and marking watched movies. This will involve fetching movie data from the OMDb API and updating the database accordingly.
app.post('/movies', async (req, res) => {
const movie = req.body;
const result = await db.run(`
INSERT INTO movies (title) VALUES ($title)
`, { $title: movie.title });
res.json({ id: result.lastInsertRowid });
});
app.patch('/movies/:id', async (req, res) => {
const id = req.params.id;
const watched = req.body.watched;
await db.run(`
UPDATE movies SET watched = $watched WHERE id = $id
`, { $watched: watched, $id: id });
res.json({ message: 'Movie updated' });
});
Viewing and Searching
We’ll also implement routes for viewing and searching movies. This will involve issuing SELECT statements and filtering results based on the watched flag.
app.get('/movies', async (req, res) => {
const results = await db.all(`
SELECT * FROM movies
`);
res.json(results);
});
app.get('/movies/search', async (req, res) => {
const query = req.query.q;
const results = await db.all(`
SELECT * FROM movies WHERE title LIKE '%$query%'
`);
res.json(results);
});
Testing and What’s Next
With our app up and running, we can test its functionality and explore ways to improve and expand libSQL. Future plans include native support for CRDTs, asynchronous APIs, and a revamped SQL shell.
Join the libSQL community today and contribute to the evolution of this powerful database solution!