Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 37 ยท Deno KV and Data

Data Formats, SQLite and Databases in Deno

Read and write JSON, CSV, YAML and TOML with the Deno standard library, store relational data in SQLite with node:sqlite and prepared statements, and connect to Postgres, MySQL and MongoDB.

Real programs move data between formats and stores. A config file is YAML or TOML, an export is CSV, an API speaks JSON, and business data lives in a relational database. Deno gives you JSON natively, the standard library parses the other formats, and you have several routes to SQL databases, from an embedded SQLite file to a remote Postgres server.

This lesson covers each of them, with the safety habits that matter most: validate what you parse, and never build SQL by gluing strings together.

Deno does not run on this site, so Deno.* and jsr:/npm: imports are read-only. The runnable blocks use Node 24 (which includes node:sqlite, a module Deno also supports) and hand-written parsers that show how the standard library behaves.

JSON

JSON is built into every JavaScript runtime. For files, combine it with Deno.readTextFile:

TypeScript
const raw = await Deno.readTextFile("config.json");   // needs --allow-read
const config = JSON.parse(raw);

await Deno.writeTextFile("out.json", JSON.stringify(config, null, 2)); // --allow-write

You can also import JSON as a module, with an attribute that tells the runtime the file is data and not code:

TypeScript
import config from "./config.json" with { type: "json" };
console.log(config.port);

JSON.parse returns any, so check the shape before trusting it. Libraries such as Zod (npm:zod) or Valibot (jsr:@valibot/valibot) validate and give you a real type; without one, write a small guard function.

For large or line-based data, JSON Lines (one JSON value per line) is easier to stream. jsr:@std/json offers JsonParseStream and JsonStringifyStream to process data without loading it all into memory. And deno.jsonc files allow comments, parsed by jsr:@std/jsonc.

CSV

CSV looks trivial and is not: fields can contain commas, quotes and line breaks. Use the standard library:

TypeScript
import { parse, stringify } from "jsr:@std/csv";

const text = `name,city,score
Ada,"London, UK",98
Grace,New York,87`;

const rows = parse(text, { skipFirstRow: true });
// [{ name: "Ada", city: "London, UK", score: "98" }, ...]   (all values are strings)

console.log(stringify(rows, { columns: ["name", "score"] }));

Two points: every value comes back as a string, so convert numbers yourself; and for big files use CsvParseStream with Deno.open(...).readable so only one row is in memory at a time.

To show why the library exists, here is a small CSV parser that handles quoted fields. The real one handles more edge cases, but the rules are the same.

Node.js Why CSV needs a real parser (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

YAML, TOML and friends

Configuration formats have their own packages:

TypeScript
import { parse as parseYaml, stringify as toYaml } from "jsr:@std/yaml";
import { parse as parseToml } from "jsr:@std/toml";

const server = parseYaml(`
host: localhost
ports: [8000, 8001]
tls: false
`) as { host: string; ports: number[]; tls: boolean };

const toml = parseToml(`
title = "My app"
[database]
url = "postgres://localhost/app"
`);

console.log(server.ports[0], toml.database);
console.log(toYaml({ name: "Deno", tags: ["fast", "safe"] }));

A security note: parse YAML with the default (safe) schema and never evaluate code from data. Also jsr:@std/dotenv parses .env text, and jsr:@std/msgpack and jsr:@std/cbor handle compact binary formats.

SQLite: a database in one file

For many apps, SQLite is all the database you need: no server, one file, full SQL, transactions and indexes. Deno supports the node:sqlite module, so the same code works in Node 24 and in Deno:

TypeScript
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("app.db");   // ":memory:" for a throwaway database
db.exec(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

const insert = db.prepare("INSERT INTO notes (title) VALUES (?)");
insert.run("Learn Deno");

const all = db.prepare("SELECT id, title FROM notes").all();

Run with deno run --allow-read --allow-write main.ts. A popular alternative is jsr:@db/sqlite, a fast binding that needs --allow-ffi.

Always use parameters

Compare these two ways to look up a user:

TypeScript
// DANGEROUS: SQL injection if `name` is "x' OR '1'='1"
db.prepare(`SELECT * FROM users WHERE name = '${name}'`).all();

// SAFE: the value travels separately from the SQL text
db.prepare("SELECT * FROM users WHERE name = ?").all(name);

A prepared statement with placeholders (? or :name) sends the SQL and the values separately, so data can never be interpreted as SQL. It is also faster when reused. Use placeholders for every value that comes from outside. Table and column names cannot be parameters, so allow-list them if they vary.

The example uses an in-memory database, a transaction and parameters. It runs for real here.

Node.js SQLite with prepared statements and a transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The CHECK (balance >= 0) constraint rejects the overdraft, and ROLLBACK undoes the debit when the second step fails, so money is never lost halfway. Transactions plus constraints are the main reason to choose a relational database over Deno KV for money-like data with many relations.

Server databases

For multi-user production systems with many writers you will use a database server. Use npm: packages, which work in Deno (see npm and Node Compatibility):

TypeScript
import postgres from "npm:postgres";

const sql = postgres(Deno.env.get("DATABASE_URL")!);

const minAge = 18;
const adults = await sql`SELECT id, name FROM users WHERE age >= ${minAge}`;
// the tagged template turns ${minAge} into a parameter, not string concatenation
TypeScript
import mysql from "npm:mysql2/promise";
const pool = mysql.createPool(Deno.env.get("MYSQL_URL")!);
const [rows] = await pool.execute("SELECT * FROM users WHERE id = ?", [7]);
TypeScript
import { MongoClient } from "npm:mongodb";
const client = new MongoClient(Deno.env.get("MONGO_URL")!);
const users = client.db("app").collection("users");
await users.insertOne({ name: "Ada" });

These need --allow-net for the database host (narrow it: --allow-net=db.internal:5432), --allow-env for the connection string, and usually a connection pool created once at startup. ORMs and query builders such as Drizzle and Prisma also work on Deno.

Choosing a store

Need Choose
Simple records, counters, sessions, queues, zero setup Deno KV
Relational data, joins, reports, transactions SQLite (one server) or Postgres/MySQL (many)
Flexible documents MongoDB or JSON columns
Large files and images Object storage (S3 or R2 style)

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can't you parse CSV reliably with line.split(",")?

    A quoted field such as "London, UK" contains a comma that is not a separator. A real parser tracks quotes.
  2. 2. What is the safest way to put a user's input into an SQL query?

    Placeholders keep data separate from SQL, which prevents SQL injection.
  3. 3. What do the values from @std/csv parse() look like by default?

    CSV has no types. Every field is returned as a string.
  4. 4. What does ROLLBACK do in the transfer example?

    A transaction is all or nothing. ROLLBACK discards the changes made since BEGIN.

Summary

  • JSON is native (JSON.parse, import ... with { type: "json" }); validate the shape of what you parse.
  • @std/csv, @std/yaml, @std/toml and @std/dotenv read configuration and tabular data; CSV values are strings.
  • node:sqlite gives a full SQL database in one file; use prepared statements, constraints and transactions.
  • Never concatenate user input into SQL; use placeholders or tagged-template drivers.
  • Server databases (Postgres, MySQL, MongoDB) come through npm: packages and need narrow --allow-net.

Next: npm and Node Compatibility